How to design the persistence layer in a .NET and EF application with concurrency control?

Question

I have read this post and the theory I think that is clear. I have a DAL that only has the methods to add, get, update and delete information in a database.

So I guess that I have an application in which I have clients, orders and type of client. Type of client has a percent that set the discount to make to a type of client.

The business layer request to DAL the type of client to know the discount.
The business layer create the order with the price and apply the discount according to the type of client.
The business layer send to the DAL the command to add the new order, sending the new order.

In code I can have this:

DAL:

public async getClientType(long paramIDClientType)
{
    using(Entities myDbContext = new Entities())
    {
        return await myDbContext.ClientTypes.Where(x=> x.IDType == paramIDClientType).SingleOrDefault();
    }
}

public async addOrder(Orders paramNewOrder)
{
    using(Entities myDbContext = new Entities())
    {
        myDbContext.Orders.Local.Add(paramNewOrder);
        await myDbContext.SaveChangesAsync();
    }
}

Business layer:

public void addOrderToClient(CLients paramClient)
{
    ClientTypes myType = myDAL.getClientType(paramClient.IDClient);

    ORder myNewOrder = myNewOder();
    myNewOrder.IDClient = paramClientIdCLient;
    myNewOder.Amount = 300;
    myNewOrder.Discount = myType.Discount;
    myNewOder.Total = nyNewOrder.Total - myNewOder.Amount * myNewOder.Discount;

    myDAL.AddOrder(nyNewOrder);
}

But I have a problem with the concurrency in this case, because I want to ensure that I use the correct discount, so I want to avoid the discount of a type of client is changed by another user un the middle of the process of add the new order.

If I use optimistic concurrency, I have to have a timestamp column in my ClientTypes table, but this not solve my problem, because in the addOrder method in my DAL layer, I only pass as parameter the new order, so the method don't have the timestamp value that has the business layer to check if the type of the client has changed to ensure that the discount used is the correct.

SO I am thinking in this solution:

public async addOrder(Orders paramNewOrder)
{
    using(Entities myDbContext = new Entities())
    {
        string sql = "select ct.* from ClientTypes as ct, CLients as c"
            + " where ct.IdType = c.IdType and c.IdType = " + paramNewOrder.IdCLient;

        ClientTypes myClientType = await myDbContext.CLientTypeSqlQuery<CLientTypes>(sql).SingleOrDefaultAsync();

        if(paramNewOrder.Discount != myCLientType)
        {
            throw new Exception("Discount incorrect.");
        }

        paramNewOrder.Total = paramNewOrder.Amount - paramNewOrder.Amount * myClientType.Discount;

        myDbContext.Orders.Local.Add(paramNewOrder);
        await myDbContext.SaveChangesAsync();
    }
}

This is my business layer, but use EF to get the data, so I think that this solution merge DAL abd business layer. Is this true? If this is true, I guess that is a not good solution. But then, how could I control concurrency?

Thanks.

score 1 · Accepted Answer · answered May 16 '16 at 22:46

1

Yes, optimistic concurrency control doesn't help you to prevent inserting a faulty new order, because you don't commit the ClientType. Only updating a ClientType would raise an exception if the discount was changed in the mean time.

But carefully consider the requirements. Is it really of paramount importance that the correct discount is used milliseconds after it's modified? If so, you have to look for a locking mechanism. Otherwise, just fetch the current discount at the very last moment, do the calculation and commit the order.

You could implement a locking/calculation/insert mechanism in a stored procedure that is mapped to the insert action of an Order. EF can map CUD actions to stored procedures..

answered May 16 '16 at 22:46

Gert Arnold

105,341
31
202
291

Well really this is a simple example, perhaps very simple. But imagine that one user is updating the order from open to paid, and I want to avoid a second user add a new item to the order. It would be possible to have a paid order with a item with a price that is not paid. I hope this example is more clear. – Álvaro García May 17 '16 at 08:15
1

It's not more clear (you already were very clear) but it's better :) Maybe in that case you should always also update the order if you add an item to it, so optimistic concurrency kicks in if the order was modified. Otherwise, also here, you need some form of locking. – Gert Arnold May 17 '16 at 11:15
But this is a "fake" update in the order, because is not needed, so it is an option, but I am still wondering if it is better this fake update or pesimistic concurrency. – Álvaro García May 24 '16 at 15:11
1

My opinion? Pessimistic concurrency is harder to implement (requires stored procedures) and harder to make scalable, because locking takes longer so deadlocks always loom. Optimistic concurrency is much easier with EF. But the fake update is not self-explanatory, so it should be well-documented in code. – Gert Arnold May 24 '16 at 15:22

How to design the persistence layer in a .NET and EF application with concurrency control?

1 Answers1