Entity Framework 4.1 Code First - auto increment f

Question

My model contains an Order (parent object) and Shipments (child object). The database table for these already have a surrogate key as an auto-increment primary key.

I have the business rule is that for each shipment in the order, we need to have an auto generated "counter" field -- e.g. Shipment 1, Shipment 2, Shipment 3, etc. Shipment model has properties: "ShipmentId", "OrderId", "ShipmentNumber". My attempted implemention is to have ShipmentNumber an int and in code(as opposed to database), query the Shipment collection and do max() + 1.

Here's a code snipet of what I'm doing.

        Shipment newShipmentObj = // blah;

        int? currentMaxId = myOrderObj.Shipments
                            .Select(x => (int?) x.ShipmentNumber)
                            .Max();
            if (currentMaxId.HasValue)
                newShipmentObj.ShipmentNumber = currentMaxId.Value + 1;
            else
                newShipmentObj.ShipmentNumber = 1; // 1st one

        myOrderObj.Shipments.Add(newShipmentObj);

        // etc.. rest of EF4 code

Is there a better way?

I don't really like this as I have the following problems because of potential transaction/concurrency issues.

My Order object also has a autoincrement "counter" -- e.g. Order 1, Order 2, Order 3, ... My Order model has properties: "OrderId", "CustomerId", "OrderNumber".

My design is that I have an OrderRepository but not a ShipmentRepository. The ShipmentRepository could query off the Order.Shipment collection... but with Orders, I have to query directly off the dbcontext, e.g.

        int? currentMaxId = (_myDbContext)).Orders
                        .Where(x => x.CustomerId == 123456)
                        .Select(x => (int?)x.OrderNumber)
                        .Max();

However, the above part doesn't work well if I attempt to add multiple objects to the DbContext without committing/saving changes to the database. (i.e. the .Where() returns null... and only works if I use DbContext ".Local", which is not what I want.)

Help! Not sure what the best solution would be. Thanks!

Answer 1

you seem to already have shipmentid that is incremental. you can use it for you shipment number and maybe combined with current date as described here: How to implement gapless, user-friendly IDs in NHibernate? what you are trying to do with Max() is evil. Stay away from it as it can cause problems with getting the same shipment numbers for multiple shipments when the load is high