Bill Blogs in C#

More LINQ 2 SQL (finally)

It’s been quite a while since I’ve tackled the DLINQ (sorry, LINQ to SQL) samples on this blog.  The combination of CoedeMash, writing for MSDN, and researching a book has meant that I haven’t done much here in the blog.  It’s time to change that.
The next LINQ to SQL sample group is quite simple. They show how LINQ to SQL uses entity references and link tables when resolving relationships between records in different tables.
The first sample shows this query:

    var q =
        from e in db.Employees
        where e.ReportsToEmployee == null
        select e;

The ReportsToEmployee property returns an EntityRef<Employee> in the employee table. EntityRef<T> is a reference type, so if there are no references, the ReportsToEmployee property returns null.  That’s all there is to it.
But, there is another way to handle these types of references.  The Employee class also contains a property that shows the employee ID for the reports to employee. The ReportsTo property contains a Nullable<int> that holds the value of the manager’s employee ID.
You can use ReportsTo to find all employees with no manager:

    var q =
        from e in db.Employees
        where !e.ReportsTo.HasValue
        select e;

Or, report on those entities that do exist, and retrieve the ReportsTo value to display that empolyee’s manager:

    var q =
        from e in db.Employees
        where e.ReportsTo.HasValue
        select new {e.FirstName, e.LastName, ReportsTo = e.ReportsTo.Value};

The next set of queries is the date time and string queries. I’m going to skip them until next time, and jump ahead to the Object Identity queries. The object identity samples show how LINQ to SQL actually does the work of mapping the object model to the relational model.   Throughout LINQ 2 SQL, the LINQ libraries ensure that anytime a query returns the same object, you receive a new reference to the object that has already been received. While these samples don’t show it, you must remember that this property is true even for records that contain links to other records, or are retrieved from other tables. Here’s the first sample that illustrate this very important concept.

 Customer cust1 = db.Customers.First(c => c.CustomerID == "BONAP");
 Customer cust2 = db.Customers.First(c => c.CustomerID == "BONAP");
Console.WriteLine("cust1 and cust2 refer to the same object in memory: {0}",
    Object.ReferenceEquals(cust1, cust2));

If you run this sample, you’ll see that cust1 and cust2 do indeed refer to the same object in memory.
The second sample shows how you do indeed get the same results using two different queries that do retrieve the same record from the database. Cust1 returns the first (and likely only) customer that has the BONAP customer id.  The second query looks through the orders table and finds the first order where the customer ID is BONAP. Then, that order is retrieved.  The comparison using ReferenceEquals to show you that both object variables do point to the same object in memory.

 Customer cust1 = db.Customers.First(c => c.CustomerID == "BONAP");
    Customer cust2 = (
        from o in db.Orders
        where o.Customer.CustomerID == "BONAP"
        select o )
        .First()
        .Customer;
    Console.WriteLine("cust1 and cust2 refer to the same object in memory: {0}",
                      Object.ReferenceEquals(cust1, cust2));

Now, to be fair, I’ve not gone out of my way to try to break this. I’ve also done very little with NHibernate or other ORM toolkits. Therefore, I’m going to be very guarded in my praise for this part of LINQ 2 SQL.  I like the design. I like the fact that it would seem that you don’t need to worry about the object identity. But, as I said, this is not an area where I’m an expert.