David Hayden [MVP C#]

I mentioned this on my personal blog and 1 person in particular has a problem with what I am saying, so I thought I would blog about it here to see what others think. This seems like a legitimate concern to me.

We have the wonderful LINQ To SQL Debugger Visualizer that Scott Guthrie mentioned to help one visualize and execute LINQ To SQL Queries. If we look at some code I posted before that does a bit of prefetching against the Nortwind Database:

NorthwindDataContext context = new NorthwindDataContext();               
    
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Customer>(c => c.Orders);
options.LoadWith<Order>(o => o.Order_Details);

context.LoadOptions = options;

var query = from c in context.Customers
            select c;

IEnumerable<Customer> customers = query.ToList<Customer>();

When you run this code and look at SQL Server Profiler we get around 90+ queries. I am always screwing with and modifying Northwind so the point isn't really the actual number of queries, but the fact that it is more than one as we will see in a moment.

When I look at the variable query using the LINQ To SQL Debugger Visualizer I get this:

which suggests that a single query will be run, essentially select all records from customers. And when you click the execute button, that is the only query it runs.

Well, that is not really the case here in the application when you take into account the LoadOptions. We will get one query that selects all the customers and then a number of queries that get the orders and orderdetails for each customer. Again, this is firing off a number of queries that can be seen in SQL Server Profiler.

This could be a bit deceptive to someone who thinks that the LINQ To SQL Debugger Visualizer is displaying the only query that will be run when this query executes. There is nothing in the LINQ To SQL Debugger Visualizer that suggests anything different.

Now I realize LINQ To SQL does not know how many customers are in the Customer Table, which in this case will ultimately decide the number of queries. I also still love the LINQ To SQL Debugger Visualizer. My only point is that unless I am missing something, one must proceed with caution and realize that the LINQ To SQL Debugger Visualizer may not tell the whole story depending on your DataLoadOptions.

Am I missing something or doesn't this concern seem legitimate?