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

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.

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..