CODEWITHSUNDEEP
linq-to-sql
warne
warne

Reputation: 71

linq to sql -error when insert on 2 entites parent child relationship

I have order & order details entities with OrderID as foreign key in order details table. OrderID is the identity column for Order and is set to autogenerate.

When I do an insert as follows (hardcoded values for simplicity):

LINQ1DataContext db = new LINQ1DataContext();

Order order = new Order();
order.OrdersCustomerID = 1;
order.OrdersDate = time;
db.Orders.InsertOnSubmit(order);

OrdersDetail oDetail = new OrdersDetail();
oDetail.OrdersDetailID = order.OrdersID;
oDetail.OrdersDetailProdID = 1;
oDetail.OrdersDetailQty = 6;
oDetail.OrdersDetailUnitPrice = 2.58m;
db.OrdersDetail.InsertOnSubmit(oDetail); 

db.SubmitChanges();

This results in a 'conflict with the FOREIGN KEY constraint' error. I found this was because an orderID is not being generated at InsertOnSubmit(order). When the SubmitChanges() is then run, OrdersDetailID doesnt match OrdersID and get the error. OrderID will only get generated following a 'submitchanges()'.

If put in an extra db.SubmitChanges(); after the db.Orders.InsertOnSubmit(order), orderid gets autogenerated as I expect and orderDetails then inserts fine.

My question is, is it normal to have to put in a submitChanges after every insertOnSubmit when inserting into 2 tables (parent-child) like this?

I thought the idea of insertOnSubmit was I could do a couple of inserts on different tables and LINQ to SQL would look after the all the details following a SubmitChanges.

thanks,

Upvotes: 3

Views: 1777

Answers (2)

warne
warne

Reputation: 71

Adding 

order.OrdersDetail.Add(oDetail);

before

db.OrdersDetail.InsertOnSubmit(oDetail);

seemed to work and only needed one db.SubmitChanges(). Maybe that's the way to do this type of thing..

Upvotes: 3

Robin Orheden
Robin Orheden

Reputation: 2764

LINQ1DataContext db = new LINQ1DataContext();

Order order = new Order();
order.OrdersCustomerID = 1;
order.OrdersDate = time;
db.Orders.InsertOnSubmit(order);

> db.SubmitChanges();

OrdersDetail oDetail = new OrdersDetail();
oDetail.OrdersDetailID = order.OrdersID;
oDetail.OrdersDetailProdID = 1;
oDetail.OrdersDetailQty = 6;
oDetail.OrdersDetailUnitPrice = 2.58m;
db.OrdersDetail.InsertOnSubmit(oDetail); 

db.SubmitChanges();

Upvotes: 0

Related Questions