LINQ to SQL Query Execution with LoadOptions Specified

David Hayden, a Florida .NET developer and C# MVP, posted an update to his LINQ To SQL Debugger Visualizer in Visual Studio 2008 Has Limited Value - LINQ To SQL Tutorials item this morning. In LINQ To SQL Debugger Visualizer Revisited - Same Concerns Better UI :), David says:

In the previous post I mentioned some concerns I have with the LINQ To SQL Debugger Visualizer in that the query it displays is not the whole story. ...

Turns out I wasn't even using the Debugger Visualizer - DOH! Here is the real LINQ To SQL Debugger Visualizer, which is a separate download.

He goes on to say:

So, the debugger visualizer is much easier to work with than I previously mentioned in the earlier post, but it still suggests only 1 query will be executed when in actuality it will execute 40+ queries to the database using the following code in beta 2:

using (NorthwindDataContext = new NorthwindDataContext())
{           
    DataLoadOptions options = new DataLoadOptions();    
    options.LoadWith<Customer>(c => c.Orders);
    options.LoadWith<Order>(o => o.Order_Details);    
    var query = from c in context.Customers select c;
}

I talked more about that in the previous post mentioned above. A little deceptive if you ask me, but still useful in other ways. [Emphasis added.]

The problem is that David omitted this critical instruction prior to executing the LINQ query:

NorthwindDataContext.LoadOptions = options;

to attach the DataLoadOptions to the NorthwindDataContext.

Further, his last instruction specifies context instead of NorthwindDataContext from his using expression. 

Update: 8/20/2007 12:00 PM PDT

David added the missing instruction but didn't correct the DataContext name. (See his comment.)

David's query returns EntitySets of Order and Order_Detail objects for each Customer object. When corrected, the preceding query with an iterator executes this query to obtain the Customer objects:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], 
  [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], 
  [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]

It is the preceding query that appears in the Query Visualizer. It's not reasonable to expect the Query Visualizer to interrogate the DataContext.LoadOptions property value and iterate over the DataLoadOptions collection to see what objects are being eader-loaded. 

The SELECT query is followed by this query for each of the 91 Customer objects to retrieve the EntitySets, as you would expect:

exec sp_executesql N'SELECT [t0].[OrderID], [t0].[CustomerID], 
  [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], 
  [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], 
  [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], 
  [t0].[ShipPostalCode], [t0].[ShipCountry], [t1].[OrderID] AS [OrderID2], 
  [t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], (
    SELECT COUNT(*)
    FROM [dbo].[Order Details] AS [t2]
    WHERE [t2].[OrderID] = [t0].[OrderID]
    ) AS [count]
    FROM [dbo].[Orders] AS [t0]
    LEFT OUTER JOIN [dbo].[Order Details] AS [t1] 
      ON [t1].[OrderID] = [t0].[OrderID]
    WHERE [t0].[CustomerID] = @x1
    ORDER BY [t0].[OrderID], [t1].[ProductID]',N'@x1 nchar(5)',@x1=N'ALFKI'

I find it hard to believe that any developer who deliberately adds code to eager-load 830 Order and 2155 Order_Detail objects would be deceived into believing that a simple SELECT query against the Customers table would retrieve 2,985 rows from two other tables in the database.

I'm also not sure why David mentioned 41+ queries in his post. It's obvious that 91 queries are required to hydrate the EntitySets for 91 Customer objects.

End Update

I tested Beta 2's updated DataContext.LoadOptions feature recently and found that it behaved as advertised with a similar query against the Orders entity set to prevent lazy loading of EntityRef objects:

public partial class NorthwindDataContext {
    partial void OnCreated() {
        DataLoadOptions dlOrder = new DataLoadOptions();
        dlOrder.LoadWith<Order>(o => o.Customer);
        dlOrder.LoadWith<Order>(o => o.Employee);
        dlOrder.LoadWith<Order>(o => o.Shipper);
        this.LoadOptions = dlOrder;
    }
}

(I was testing the LinqDataSource with a bound GridView, so I had to add the LoadOptions collection in the OnCreated event handler of a separate partial class file.)

With GridView.PageSize = 15, loading the first page generates two queries:

SELECT COUNT(*) AS [value]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Customers] AS [t1] 
    ON [t1].[CustomerID] = [t0].[CustomerID]
LEFT OUTER JOIN [dbo].[Employees] AS [t2] 
    ON [t2].[EmployeeID] = [t0].[EmployeeID]
LEFT OUTER JOIN [dbo].[Shippers] AS [t3] ON [t3].[ShipperID] = [t0].[ShipVia]

exec sp_executesql N'SELECT TOP 15 [t7].[OrderID], [t7].[CustomerID], 
  [t7].[EmployeeID], [t7].[OrderDate], [t7].[RequiredDate], 
  [t7].[ShippedDate], [t7].[ShipVia], [t7].[Freight], [t7].[ShipName], 
  [t7].[ShipAddress], [t7].[ShipCity], [t7].[ShipRegion], 
  [t7].[ShipPostalCode], [t7].[ShipCountry], [t7].[test], 
  [t7].[CustomerID2], [t7].[CompanyName], [t7].[ContactName], 
  [t7].[ContactTitle], [t7].[Address], [t7].[City], [t7].[Region], 
  [t7].[PostalCode], [t7].[Country], [t7].[Phone], [t7].[Fax], [t7].[test2],
  [t7].[EmployeeID2], [t7].[LastName], [t7].[FirstName], [t7].[Title], 
  [t7].[TitleOfCourtesy], [t7].[BirthDate], [t7].[HireDate], 
  [t7].[Address2], [t7].[City2], [t7].[Region2], [t7].[PostalCode2], 
  [t7].[Country2], [t7].[HomePhone], [t7].[Extension], [t7].[Notes], 
  [t7].[ReportsTo], [t7].[test3], [t7].[ShipperID], [t7].[CompanyName2], 
  [t7].[Phone2]
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY [t0].[OrderID], [t0].[CustomerID], 
    [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], 
    [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], 
    [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], 
    [t0].[ShipPostalCode], [t0].[ShipCountry], [t2].[test], 
    [t2].[CustomerID], [t2].[CompanyName], [t2].[ContactName], 
    [t2].[ContactTitle], [t2].[Address], [t2].[City], [t2].[Region], 
    [t2].[PostalCode], [t2].[Country], [t2].[Phone], [t2].[Fax], 
    [t4].[test], [t4].[EmployeeID], [t4].[LastName], [t4].[FirstName], 
    [t4].[Title], [t4].[TitleOfCourtesy], [t4].[BirthDate], [t4].[HireDate], 
    [t4].[Address], [t4].[City], [t4].[Region], [t4].[PostalCode], 
    [t4].[Country], [t4].[HomePhone], [t4].[Extension], [t4].[ReportsTo], 
    [t6].[test], [t6].[ShipperID], [t6].[CompanyName], [t6].[Phone]) 
  AS [ROW_NUMBER], 
    [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], 
    [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], 
    [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], 
    [t0].[ShipPostalCode], [t0].[ShipCountry], [t2].[test], 
    [t2].[CustomerID] AS [CustomerID2], [t2].[CompanyName], 
    [t2].[ContactName], [t2].[ContactTitle], [t2].[Address], [t2].[City], 
    [t2].[Region], [t2].[PostalCode], [t2].[Country], [t2].[Phone], 
    [t2].[Fax], [t4].[test] AS [test2], [t4].[EmployeeID] AS [EmployeeID2], 
    [t4].[LastName], [t4].[FirstName], [t4].[Title], [t4].[TitleOfCourtesy], 
    [t4].[BirthDate], [t4].[HireDate], [t4].[Address] AS [Address2], 
    [t4].[City] AS [City2], [t4].[Region] AS [Region2], 
    [t4].[PostalCode] AS [PostalCode2], [t4].[Country] AS [Country2], 
    [t4].[HomePhone], [t4].[Extension], [t4].[Notes], [t4].[ReportsTo], 
    [t6].[test] AS [test3], [t6].[ShipperID], 
    [t6].[CompanyName] AS [CompanyName2], [t6].[Phone] AS [Phone2]
  FROM [dbo].[Orders] AS [t0]
    LEFT OUTER JOIN (
      SELECT 1 AS [test], [t1].[CustomerID], [t1].[CompanyName], 
        [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], 
        [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], 
        [t1].[Phone], [t1].[Fax]
      FROM [dbo].[Customers] AS [t1]
        ) AS [t2] ON [t2].[CustomerID] = [t0].[CustomerID]
    LEFT OUTER JOIN (
      SELECT 1 AS [test], [t3].[EmployeeID], [t3].[LastName], 
        [t3].[FirstName], [t3].[Title], [t3].[TitleOfCourtesy], 
        [t3].[BirthDate], [t3].[HireDate], [t3].[Address], [t3].[City], 
        [t3].[Region], [t3].[PostalCode], [t3].[Country], [t3].[HomePhone], 
        [t3].[Extension], [t3].[Notes], [t3].[ReportsTo]
      FROM [dbo].[Employees] AS [t3]
        ) AS [t4] ON [t4].[EmployeeID] = [t0].[EmployeeID]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t5].[ShipperID], [t5].[CompanyName], 
          [t5].[Phone]
        FROM [dbo].[Shippers] AS [t5]
        ) AS [t6] ON [t6].[ShipperID] = [t0].[ShipVia]
    ) AS [t7]
WHERE [t7].[ROW_NUMBER] > @p0',N'@p0 int',@p0=15

I probably wouldn't have gone through the trouble to post this rejoinder if:

1. David hadn't stated that 40+ queries were required to execute his query.
2. The bogus issue he raised was the result of a missing instruction not expecting the Query Visualizer to evaluate the DataContext.LoadOptions property value for eager-loading instructions. 
3. He hadn't used the term "deceptive" in describing the Query Visualizer's failure to detect them.
4. He had comments enabled on his blog.
5. The ADO.NET team hadn't gone through an extreme effort to build the required T-SQL batch in the query pipeline's expression tree.

Technorati Tags: LINQ to SQL, LINQ, LinqDataSource, Expression Trees, O/R Designer