Inserting an [Order] and [OrderItems] in LINQ

Question

I'm quite new to LINQ and was wondering what was the best design for inserting an [Order], subsequently getting the [Order].[ID] and using this to save some [OrderItems].

The ID column of the [Order] table is an Identity column.

I want to try and prevent calling db.SubmitChanges(), getting the newly inserted Order ID and then having to call it (db.SubmitChanges()) again when inserting the related Order Items - is this the only way to do this though?

Thanks in advance.

Answer 1

If you have your LINQ to SQL Classes set up correctly, then Order should have a collection of OrderItems.

You should be able to create an Order and then add new OrderItems to the collection and then call db.SubmitChanges() once and allow LINQ to SQL handle the Id issues.

Answer 2

You don't have to call it in two separate steps. LINQ takes care of that for you.

If you have your database modeled correctly using a foreign key relationship between your Order and OrderItems tables, then the generated objects will also have a relationship. They Order will be composed with OrderItems. Specifically in the case of a one-to-many relationship, your Order object will have a property OrderItems that will be a collection of OrderItem objects.

LINQ is smart enough to see the composition of your objects, and because it has knowledge of the type of constraint between your tables, if will insert all the records in the correct order with the appropriate IDs, to create the same entity modeled in your OOP code, in the relational world.

So, you only have to call SubmitChanges once.

db.Orders.InsertOnSubmit(myOrder);
db.SubmitChanges();

Answer 3

i am having the same problem. but the difference is that i want to get Order with related orderitems, but when it returns order, Order.OrderItems collection is empty. what can i do ? thank you.