Best practice / path for basic parts / multi-page insert table in Entity Framework

Question

My table structure is

Orders ------ Id int identity OrderDate smalldatetime OrderStatusid tinyint Products -------- Id int identity Name varchar(50) OrderDetails ------------ Id int identity OrderId int (fkey) ProductId int (fkey) Amount decimal Rate decimal 

I am trying to perform an insert operation using Entity Framework using the code below
Is this the best way to make an insert?
I am not happy with the way I get the complete product element from the context object, instead of just assigning a simple value to productId

 using (MyContextEntities ctx = new MyContextEntities()) { Orders newOrder = new Orders() { Name = "Gayle Wynand", OrderDate = DateTime.Now, IsComplete = true, Comments = "test", OrderStatusId = 2, IsActive = true }; OrderDetails ode = new OrderDetails(); ode.Products = ctx.Products.First(p => p.Id == 2); // any other way? ode.Quantity = 2; ode.Rate = 5.2; newOrder.OrderDetails.Add(ode); OrderDetails ode2 = new OrderDetails(); ode2.Products = ctx.Products.First(p => p.Id == 3); // any other way? ode2.Quantity = 3; ode2.Rate =6.5; newOrder.OrderDetails.Add(ode2); ctx.AddToOrders(newOrder); ctx.SaveChanges(); } 

Is this the right way to insert the main part or is there a better / different way.

Answer 1

What you are doing now will work fine.

If you want to avoid querying the database when assigning ode.Products, you can use the following alternative:

 // substitute your actual qualified entity set name ode.ProductsReference.EntityKey = new EntityKey("MyEntities.ProductsEntitySetName", "Id", 2); 

It is faster, but less readable. In addition, the Products property will be null until you load it. But for insertion, this is often normal.

Answer 2

Another approach would be to use Stub objects rather than EntityKeys ie

 var product = new Product {ID = 2}; ctx.AttachTo("Products", product); ode.Product = product; 

etc .. As an added bonus, this code will work with POCO objects in the future.

For more on this technique, see this blog post .