As I mentioned in my previous post about LINQ and IDENTITY fields
, I was getting an exception when trying to update back to the DB. I have since tracked this down to my guess in that post where it was due to the way that I constructed the customer object via foreach. Using that technique, it seems that it keeps a DataReader open longer than needed, which results in this error. I have a workaround, so I can at least share this sample to show you how related tables with IDENTITY fields work. If you want to see the exception, replace the line where I assign the cust variable with a "foreach (var cust in query)".
Things to note in this sample:
- I am mixing and matching objects retrieved from the DB (cust) and locally created objects (o and od).
- You can assign the Order object to the customer either by setting o.Customer to point to the customer, or using cust.Orders.Add(o). The same holds true for adding OrderDetail to Order.
- IDENTITY fields are not initialized upon creation, but they are assigned the actual value that they got when getting inserted to the DB.
- You can link tables together by either using the object references or the actual data field. For example, when building the OrderDetail object, I use od.ProductID to set the value that will get written to the DB. I could also have constructed/received a Product object from the DB and assigned od.Product to do the linking via object reference.
- Linking between related tables is taken care of automatically, even when linked via IDENTITY fields
static void Main(string args)
Northwind db = new Northwind(
@"Data Source=(local);Initial Catalog=Northwind;Integrated Security=True");
db.Log = Console.Out;
var query = from c in db.Customers
where c.CustomerID == "ALFKI"
Customer cust = query.ToArray();
Order o = new Order();
o.Customer = cust;
o.Freight = 23;
Console.WriteLine("[PRE] Order.OrderID == " + o.OrderID);
OrderDetail od = new OrderDetail();
od.Order = o;
od.Quantity = 1;
od.ProductID = 1;
Console.WriteLine("[PRE] OrderDetail.OrderID == " + od.OrderID);
Console.WriteLine("[POST] Order.OrderID == " + o.OrderID);
Console.WriteLine("[POST] OrderDetail.OrderID == " + od.OrderID);