Entity framework - how to add complex objects to db

Question

Here is simplified model of my MySQL database (model first): I mapped it via Entity Framework v6.1.3. I have dataContext object to make database queries.

OrderProductsProduct1 is table to make foreign key connections, here is EF auto generated class:

 public partial class OrderProductsProduct1
 {
     public int ID { get; set; }
     public int OrderProductsID { get; set; }
     public int Product1ID { get; set; }
     public decimal Width { get; set; }

     public virtual OrderProducts OrderProducts { get; set; }
     public virtual Product1 Product1 { get; set; }
}

I'm confused how to add new objects to database, which has two one to many foreign keys. For example how to properly add Order object with few Product1 objects connected via OrderProducts and OrderProductsProduct1 tables.

So far I was trying to make a List of OrderProducts, where each OrderProduct has another List with OrderProductsProduct1 and 2. To OrderProductsProduct1 and 2 I was adding Products1 and 2. Finally OrderProducts was bound to Order with orderProducts.Order = newOrder and then dataContext.Add(newOrder). But this causes Entity Framework exceptions.

Do I have to create objects from the lowest level (Client, Product1, Product2), add them to database with dataContext.Add(), to get their ID's and then bound them with higher level objects (Order) and so on until the highest level (OrderProductsProduct1 and 2)? This approach omits Entity Framework and makes hard to revert changes in case of errors.

Wait... i see a lot of vitual things, look at this : http://stackoverflow.com/questions/40729137/why-are-foreign-keys-in-ef-code-first-marked-as-virtual/40729675#40729675 — Antoine Pelletier, Dec 20 '16 at 21:30
Putting the lazy loding off is maybe what you need, what is the entity exeption you get ? — Antoine Pelletier, Dec 20 '16 at 21:30
I know this is OT but your database design is not optimal for an product/order management application. — TheGeekYouNeed, Dec 20 '16 at 23:01

score 3 · Accepted Answer · answered Dec 21 '16 at 14:48

Entity Framework is smart enough to look at the primary Key (ID) of your objects to see if it must add it as a new object, or to use only the foreign key of the object.

Suppose you want to add a new OrderProductProducts1 with an existing OrderProducts and an existing Product1. Code could be like follows:

Product1 existingProduct = dbContext.Product1s...
OrderProducts existingorderProduct = dbContext.OrderProducts...

Method where you use the ID of existing items:

OrderProductProducts1 addedProduct = dbContext.OderProductProducts1.Add(
    new OperProductProducts1()
    {
        // don't fill ID, will be filled during SaveChanges
        OrderProductsId = existingOrderProduct.ID,
        Product1Id = existingProduct.ID,
        ...
    });

Instead of filling the ID of the existing items you can also use the complete existing item:

OrderProductProducts1 addedProduct = dbContext.OderProductProducts1.Add(
    new OperProductProducts1()
    {
        // don't fill ID, will be filled during SaveChanges
        OrderProducts = existingOrderProduct,
        Product1 = existingProduct,
        ...
    });

Entity Framework is smart enough to check the Id of the existing items to see they don't have to be added.

If your OrderProductProducts1 does not use existing items but now ones, just assign them as if they were existing ones:

var nonExistingProduct = new Product1s() {...};
// note: because it does not exist ID == 0
OrderProductProducts1 addedProduct = dbContext.OderProductProducts1.Add(
    new OperProductProducts1()
    {
        // don't fill ID, will be filled during SaveChanges
        OrderProductsId = existingOrderProduct.Id,
        Product1 = nonExistingProduct,
        ...
    });

During SaveChanges Entity framework sees that nonExistingProduct has a zero ID, and thus knows that it has to be added. The Id of the product will be the foreign Product1Id.

If you want you can add the new product to the DbContext yourself before adding the OrderProductProducts1. However, remember as long as you didn't SaveChanges you can't use the ID:

var justAddedProduct = dbContext.Products.Add(new Product1s() {...});
// note: because it does not exist ID == 0
OrderProductProducts1 addedProduct = dbContext.OderProductProducts1.Add(
    new OperProductProducts1()
    {
        // don't fill ID, will be filled during SaveChanges
        OrderProductsId = existingOrderProduct.Id,
        Product1 = justAddedProduct,
        ...
    });

Thanks to your tips I solved my problems. Thank you! – user3550149 Dec 22 '16 at 09:07 — user3550149, Dec 22 '16 at 09:07

Entity framework - how to add complex objects to db

1 Answers1

Linked