LINQ Insert record to main table and child table using id from main table

Question

I have two tables TblOrders and TblOrderItems. TblOrders has a primary key field which is an autonumber field. I need to insert a record into TblOrders and then using the OrderID for that Order insert several records into TblOrderItems.

So TblOrders has the following fields OrderID, Customer, DelAddress, OrderDate while TblOrderItems has the following filed ItemID (PK autonumber), LinkedOrderID (the OrderID from TblOrders that this item belongs to), ItemName, ItemQty, ItemPrice

I have the following Code...

Dim db as New OrdersDataContext
Dim newOrder as New Order With {.Customer = "CustomerA", _
                                .DelAddress = "Delivery Address", _
                                .OrderDate = Now()}

db.Orders.InsertOnSubmit(newOrder)
'Removed as I now have a FK relationship
'Dim ID as Integer = newOrder.OrderID

If lstBox_Items.Count > 0 Then
    For i = 0 to lstBox_Items.Count - 1
        Dim newOrdersItem as New OrderItem With {.LinkedOrderID = newOrder.OrderID, _
                                                 .ItemName = lstBox_Items.Items.Item(i).Text, _
                                                 .ItemPrice = lstBox_Items.Items.Item(i).Value,_
                                                 .ItemQty = lstBox_Items.Items.Item(i).Attributes("Qty")}

        db.OrderItems.InsertOnSubmit(newOrderItem)
    Next
End If

db.SubmitChanges

This is where I'm struggling... Where do I need to place my db.InsertOnSubmit() and db.SubmitChanges so that the whole transaction is completed and should anything fail then no Order is inserted.

UPDATE: I have now updated my code block with my insert and submit commands. The code seems to be working OK except ALL inserts into child table OrderItems have a LinkedOrderID value of 0 instead of the OrderID from the newly inserted record in the ORDER table.

UPDATE 2: I have now added an FK relationship between OrderItems.LinkedOrderID and Order.OrderID but now when I try to insert I get an error... The INSERT statement conflicted with the FOREIGN KEY constraint. "FK_TblOrderItems_TblOrders". The conflict occurred in database "MMD", table "dbo.TblOrders", column 'OrderID'. The statement has been terminated.

SOLVED: Updated the code block with correct solution.

You should add lOrderItems to Order.OrderItems instead of setting the FK value yourself. — Gert Arnold
Sorry Gert, I'm not clear on what you are suggesting... I have updated my code block and I'm getting all inserts except that the LinkedOrderID in the OrderItems table is always 0. — Mych
If LinkedOrderID is a defined as a FK from OrderItem to Order in the database, the linq-to-sql designer should have created this property. You need this property to add OrderItems to an Order if both are new. Linq-to-sql will set the FK values itself. — Gert Arnold

Mych Mych · Accepted Answer · 2013-10-16T10:02:00

0

votes

Issue solved... Thanks to Gert for help provided. Answer shown in main question.

LINQ Insert record to main table and child table using id from main table

1 Answers