Thoughts from Dan Miser RSS 2.0
# Friday, 23 September 2005
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

    class Program
        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"
                        select c;

            Customer cust = query.ToArray()[0];
            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);
Friday, 23 September 2005 19:54:00 (GMT Daylight Time, UTC+01:00)  #    Comments [0] -
Tracked by: [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback] [Pingback]
<2018 May>
About the author/Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2018
Dan Miser
Sign In
Total Posts: 388
This Year: 0
This Month: 0
This Week: 0
Comments: 630
Pick a theme:
All Content © 2018, Dan Miser
DasBlog theme 'Business' created by Christoph De Baene (delarou)