1
\$\begingroup\$

I want to be able to list every Transaction associated with a Client in date order.

Transaction has 2 sub-types: PaymentTransaction and ChargeTransaction.

The key difference between them is:

  • PaymentTransaction should be associated directly with a Client.
  • ChargeTransaction is associated with a Proposal, which in turn is associated with a Client.

public abstract class Transaction
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public DateTime Time { get; set; }
}

public class PaymentTransaction : Transaction
{
    public int ClientId { get; set; }
    public Client Client { get; set; }
}

public class ChargeTransaction : Transaction 
{
    public int ProposalId { get; set; }
    public Proposal Proposal { get; set; }
}

public class Client
{
    public int Id { get; set; }
    public ICollection<Proposal> Proposals { get; set; }
    public ICollection<PaymentTransaction> Payments { get; set; }
}

public class Proposal
{
    public int Id { get; set; }
    public int ClientId { get; set; }
    public Client Client { get; set; }
    public ICollection<ChargeTransaction> Charge { get; set; }
}

There will be 1000s of records for each Client. So I need to page the results.

I don't want to have to blend the results from 2 Db tables, so I have used Table per Hierarchy pattern:

public DbSet<Transaction> Transactions { get; set; }
public DbSet<PaymentTransaction> PaymentTransactions { get; set; }
public DbSet<ChargeTransaction> ChargeTransactions { get; set; }

My problem is retrieving the results for a specific ClientId. For db.ChargeTransactions I would use x => x.Proposal.ClientId. And for db.PaymentTransactions I would use x => x.ClientId. But for db.Transactions I cannot use either of these.


My Solution

I have put the Proposal and Client properties directly on the Transaction model and made them nullable.

I'm not convinced this is a good solution, but I don't know a better way. That is why I'm asking this question.

Can you suggest any improvements on this? Or a better approach?

public abstract class Transaction
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public DateTime Time { get; set; }
    public int? ClientId { get; set; }
    public virtual Client Client { get; set; }
    public int? ProposalId { get; set; }
    public virtual Proposal Proposal { get; set; }
}

public class PaymentTransaction : Transaction
{

}

public class ChargeTransaction : Transaction 
{

}

This means I can do:

db.Transactions.Where(x => x.ClientId == clientId || x.Proposal.ClientId == clientId)
\$\endgroup\$
  • 1
    \$\begingroup\$ I am afraid that the database tables and queries you generate might get greedy on locking and lock escalations (major performance hit). I think you need to consider if it is at all possible to have a transaction where a client Id is null. \$\endgroup\$ – Walter Vehoeven Oct 4 '19 at 5:30
  • 1
    \$\begingroup\$ I think you need a M->M table with a "charge nature" you join via that table based on the nature. in code that would be Inheritance in code, docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/… \$\endgroup\$ – Walter Vehoeven Oct 4 '19 at 5:34
  • \$\begingroup\$ Thanks @PPann. I get the locks problem. (I had been wondering if I should just set the clientId manually.) But I'm having trouble understanding how the M->M charge nature table would work in relation to the TPT/TPC/TPH setups that the MS article describes. Any chance you could elaborate a little, maybe as an answer? \$\endgroup\$ – Martin Hansen Lennox Oct 4 '19 at 15:47
  • 1
    \$\begingroup\$ I want to be able to list... Is this only for reading data? \$\endgroup\$ – Gert Arnold Oct 6 '19 at 18:29
  • 1
    \$\begingroup\$ Then I'd use a database view that blends the data from two transaction tables under one UserId column. \$\endgroup\$ – Gert Arnold Oct 7 '19 at 12:15
1
\$\begingroup\$

It's hard to answer your question with a definitive answer as so much information is missing and a lot, depends also on the other UI screens data that you need to fill or that other may have opened. You’re going to get issues when performing aggregates on the same table that you provide row data on as you might end-up in a deadlock, even with yourself. I'm going to give some basic data that goes through my mind, if you already know this, no harm done, just skip over it.

1 Transaction scope: Say you have a transaction summing over the charges and payments to get a balance, you also would like to return the last 10 charges as well as the last 10 payments. We all know that there is no guarantee that charges and payments sum-up you will get into partial payments.

Then users will use a invoice reference number in the payments so you need to book a payment to that reference even if there are older charges that would incur late fees (this may actually be a business case for some companies as they charge more).

You're may not be able to guarantee that the table is partitioned in a way that would facilitate transactional isolation levels to that detail so you will likely need to set your connections to allow for a snapshot isolation level (this will hit your tempdb with snapshot data so size for that) as you have no or little control over the TSQL generated by EF. If you can think of the data that you your using as ViewModel class in MVC that that would help, you do not need take the whole table into memory when you a limited subset. You will have less issues when you’re updating the data that another user might also be updating

public class FooViewConfiguration : EntityTypeConfiguration<FooView>      
{
   public FooViewConfiguration()
   {
    this.HasKey(t => t.Id);
    this.ToTable("dbo.myView");
   }
  …
}

You will find that you’re users are getting optimistic concurrency exceptions (you never get them as you’re the only one changing the data in your database) so you’re data scope needs to deal with that. Have a look at

using (var context = new PaymentTransactionContext())
{
    var payment = context.Payments.Find(1);
    payment.PayedByName = "client Name";
    payment.PaymentDate = UTCNow;


    bool saveFailed;
    do
    {
        saveFailed = false;

        try
        {
            context.SaveChanges();
        }
        catch (DbUpdateConcurrencyException ex)
        {
            saveFailed = true;

            // Update the values of the entity that failed to save from the store
            ex.Entries.Single().Reload();
        }

    } while (saveFailed);
}

And only save that where you have a change.

using (var context = new MyContext())
{
    context.ChangeTracker.TrackGraph(customer, e =>
    {
        e.Entry.State = EntityState.Unchanged;

        if((e.Entry.Entity as Invoice) != null)
        {
            if (e.Entry.IsKeySet)
            {
                e.Entry.State = EntityState.Modified;
            }
            else
            {
                e.Entry.State = EntityState.Added;
            }
        }
    });

    foreach (var entry in context.ChangeTracker.Entries())
    {
        Console.WriteLine("Entity: {0}, State: {1}", entry.Entity.GetType().Name, entry.State.ToString());
    }
    context.SaveChanges();
}

You limit this by using an (indexed) view and control the locking using TSQL, you can update data in a view either direct if only one table is involved or by using "instead of" triggers when you join several tables.

2 Indexing and locks: You might have some and you might find that your indexes are disabled (they get broken on ETL import errors) or those that you need are missing you will usually end-up with table locks as the dbms has alternative as the only option is a table scan or index scan (yes, both are bad).

Adding to many indexes will really slow down the inserts as they need to be maintained as well. Depending on the size and quantity of an index that might get out of hand. (once worked on a project where I had to drop all indexes, then do a backup and re-create the indexes in order to backup and maintain the recovery time objectives).

Also indexes, and covering indexes will widen the scope of the data as this needs to be locked as well.

What one ends-up doing is design a database that has indexes on join fields first. The way you limit the scope and speed up your table is by "removing unrelated data" as early as possible, the smaller the scope the smaller the lock the less disk I.O. SQL server executes the plan after a given time, might not be the best plan, but you do not want to wait 2 seconds for him to figure it out (that's why you can pre-compile plans and attach them). Usually what I end-up doing is make 2 connections/ contexts. I normally have a database connection with a read-only intend. And one with a read-write intend. This way will save yourself quite a few locking issues and note that this is what makes working with large databases so slow, you quickly end up locking too much and read too much. The read-only will do most of the heavy lifting and cause little or no issues as the intend and transaction levels help.

3 Database are for storing data, they are not OO: Sql server does support graph, and you would get some kick-ass performance however EF doesn’t as far as I know. So, if getting a bigger database I advice to not use OO design from the class as a mirror on the SQL tables, it’s not going to perform, go to the 6th nominal form and start duplicating data. Or create a physical table for all 3 using a 1-1 optional relationship (a foreign key that is disabled for query plans) and a trigger for data integrity Also, I think that Proposal is a perhaps a ChargeTrasnaction that has a flag of being accepted… could this be

I think, try having a customer with relationships on "view model"/"unit of work" scoped data using ICollection for all relations and really limit what you load using the above mentioned option.

You can always use EF and SqlCommand and use the best of both worlds when ever you need.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ have a look at docs.microsoft.com/en-us/dotnet/architecture/microservices/… as it deals with some of the concepts mentions above. \$\endgroup\$ – Walter Vehoeven Oct 4 '19 at 20:44
  • \$\begingroup\$ @Martin Hansen Lennox, does this solve your issue? \$\endgroup\$ – Walter Vehoeven Oct 5 '19 at 18:16
  • \$\begingroup\$ it doesn't really. But it is such a detailed, well thought out and comprehensive answer I feel terrible for saying that. On this occasion I just want to list the data, selecting records by the clientId. It reflects poorly on my skills that I hadn't even considered the locking implications. So while it was a very helpful and interesting answer that gave me much food for thought, it didn't actually solve my issue. \$\endgroup\$ – Martin Hansen Lennox Oct 6 '19 at 21:40
  • \$\begingroup\$ @MartinHansenLennox no need to feel bad, there is a lot of Technology in your stack. Try and make a List of dependent data and show that, keep it simple. \$\endgroup\$ – Walter Vehoeven Oct 7 '19 at 8:04
  • \$\begingroup\$ The problem I'm having is getting a single list from db.Transactions based on the clientId. \$\endgroup\$ – Martin Hansen Lennox Oct 7 '19 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.