Entity Framework record is adding a new record instead of using existing record

Question

I have an issue where I'm inserting a new record of order and inside of order i have order details. There can be multiple details each with a product which already exists in the database. My problem is when i try to update the order detail product with the product from the database it wants to put a new record into the database with an empty guid instead of using the one that is already there.

var order = context.Orders.FirstOrDefault(o => o.OrderId == request.Order.OrderId);
if (order == null)
{
    ...

    // This is where a new order is mapped in from a class model 
    // The class model contains order information and order details.

    ...

    // Order Detail
    foreach (var detail in order.OrderDetails)
    {
        var product = context.Products.FirstOrDefault(p => p.ProductId == detail.Product.ProductId);
        detail.Product = product;
        detail.ProductId = product.Id;   
    }

    context.SaveChanges();
}

I have tried to set the detail.Product state to unchanged but that does not work either.

Here is what the records look like after one order has been inserted. You can see the same ProductId but different Guid's. The one that has a Guid is the original that it found but after doing SaveChanges then the empty Guid record was inserted.

Id                                      ProductId   CostPrice   DateCreated
00000000-0000-0000-0000-000000000000    11671170639 14.75       2017-07-08 04:16:21.927
931F65C2-919B-4876-8D6A-77387FF22001    11671170639 14.75       2017-07-07 11:11:29.327

We map the class model to the data model i just did not show that. I put code in there to clarify that. I'm just checking the order does not exists in the database and if not (null) then I'm adding it. — NormTheThird
– NormTheThird, Commented Jul 8, 2017 at 2:16
Are you able to debug and check whether there is in fact a product existing which has a product id equal to detail.Product.ProductId ? — BenKoshy
– BenKoshy, Commented Jul 8, 2017 at 2:39
Yes i know the product exist and for some reason it wants to enter it in to the database with an empty Guid so after running the code have two products with the same ProductId but different Guid's one is the original and one is the new that is empty. — NormTheThird
– NormTheThird, Commented Jul 8, 2017 at 4:01
by using a global DbContext, you load a bunch of objects into the context you don't actually need. Previous loaded entries may be an issue. I assume dirty DbContext entries and object tree relationships are causing this behavior. — DevilSuichiro
– DevilSuichiro, Commented Jul 8, 2017 at 6:43

Steve Py · Accepted Answer · 2017-07-08 07:45:45Z

2

First up, consider changing your field naming convention to avoid confusion. Where you have Product.ProductId, consider naming this Product.ProductNumber. I'd also consider using the same field name for the PK and FK (ProductId vs. Id). When your OrderDetail has a ProductId this is easily confused with your "ProductId" column on your Product entity.

As for the issue you are seeing: What is the source of the OrderDetail instance being operated against? Is it a New OrderDetail or one that was retrieved from EF? What is the source of the existing "Product" item referenced by this OrderDetail? (before your code here to find the Product in EF) Was it New'ed or pulled from EF?

My guess is that you've pulled the OrderDetail from EF where it did not include a Product, and had a virtual Product Product{get;set;} declared. The Product that was added was New'd up based on details of a product. It had a ProductId but no "Id" specified. Before saving your OrderDetail you want to use that ProductId to find the "real" Product from EF and bind it.

After you save the OrderDetail, what was the ProductId on the order detail record? the 000... or "real" id? If it was pointing a the real ID but left that 000.. record orphaned then I suspect that by adding a Product to your OrderDetail EF entity via the proxy, EF has already recorded that a new Product is being added. Your mappings don't have the ID set to identify it as a Generated column, so EF records the Guid.Default for the new entity. EF has no notion of deleting orphaned entities.

If it's pointing at the 0000 then my guess would be that you have a disconnect between the Product and ProductId reference on OrderDetail and/or you are dealing with multiple dbContext instances. Both are generally bad. When using reference properties (OrderDetail.Product) then you should avoid adding properties for the FKs. (OrderDetail.ProductId) This leads to disconnects where the Product's ID does not necessarily match the ProductId and EF has to "pick one". In your case we would need to see your mapping declarations and whether this is Code first or Schema first. Also check whether OrderDetail.Product is declared as virtual or not.

If this was the result of a round trip from the domain through to the view and back, my advice would be to avoid passing Entities around, and instead use ViewModels to talk to your views. These are plain POCO elements where when they get back to the domain, EF can load or create the appropriate entities and match the references up. Passing entities around outside the scope of their DB Context generally leads to pain like this and worse. Multiple instances of a DBContext is another source of issues like this. (I.e. the "context" used by this code is not the same reference as "contexts" used to pull other entities)

answered Jul 8, 2017 at 7:45

Steve Py

36.4k4 gold badges34 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

NormTheThird Over a year ago

You are correct about what is going on and I am using Database First. I'm getting an order json object from an eccomerce site that includes a product_id. I then map that json to my own class model structure. I then map that same structure to a database object and at that point i need to find the product from the table to replace the empty class model in my OrderDetails. OrderDetail.Product is not virtual. Should it be? I also agree that the ProductId should be changed to ProductNumber!

Steve Py Over a year ago

ok, so it sounds like you are creating a new OrderDetails when you get this JSON object, and it is likely creating a new, blank Product as part of that creation? If the ProductId in OrderItem is non-nullable then the core of the solution is to ensure that the code to set up the new order item and associate the product is happening with the same dbContext. I suspect the problem is that you're creating and saving an order item somewhere prior to this. You want to create the order item, then hand off to load the product all prior to a single dbContext.SaveChanges().

NormTheThird Over a year ago

Ok, so I have it working now the way you suggested where I only new the product up in the context. I understand what I changed to fix it but I am also unclear why what i was doing was not working so I guess its time to pick up a book and learn more a entity framework. Thanks for the help.

Steve Py Over a year ago

if you create an orderitem in one context and that order item has a Guid (not Guid?) FK to a product and you go to save it, you essentially need to provide a Product to EF for referential integrity. At that point you've saved the {0000...} Product. Have a look at the Unit of Work pattern which can help solve problems like this. It's designed to serve the context over a complete operation so that you can still do granular operations with the domain, but ensure that the required changes are committed to the context together.

Iman Salehi · Accepted Answer · 2017-07-08 06:28:00Z

0

i think you don't need the line which i say it below:

 // Order Detail
    foreach (var detail in order.OrderDetails)
    {
        var product = context.Products.FirstOrDefault(p => p.ProductId == detail.Product.ProductId);
        detail.Product = product;
        detail.ProductId = product.Id;   // why you did this??? it has to bind at upper line 
    }

and next...i dont understand your database... you are selecting orders product at this line:

 var product = context.Products.FirstOrDefault(p => p.ProductId == detail.Product.ProductId);

and then you bind detail.Product again? why you wan to do this ? if your product has a product by the same id so it has...you don't need to bind it again. i think its better to have a review on your code logic!.

edited Jul 8, 2017 at 6:28

answered Jul 8, 2017 at 5:08

Iman Salehi

9562 gold badges15 silver badges37 bronze badges

Collectives™ on Stack Overflow

Entity Framework record is adding a new record instead of using existing record

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related