LINQ to SQL: Generating one query when calling First() or Take()

Question

I have a database with the following tables:

create table Categories (
  Id int primary key,
  Name nvarchar(256) not null)

create table Products (
  Id int primary key,
  Name nvarchar(256) not null,
  CategoryId int not null,
  foreign key (CategoryId) references Categories (Id))

Using DataLoadOptions, I can write this:

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Category>(c => c.Products);

and get all of the category and product information in one query.

However, db.Categories.First(), db.Categories.FirstOrDefault(), and db.Categories.Take(10) will execute a query that only grabs a subset of entries from the Categories table, and nothing else. Essentially, this:

SELECT TOP (1) Id, Name FROM Categories
-- and
SELECT TOP (10) Id, Name FROM Categories

Accessing the Products property of a Category instance will result in another query execution.

I've discovered a few ways to get around this.

// For First():
var category = db.Categories.Single(c => c == db.Categories.First());

// For FirstOrDefault():
var category = db.Categories.SingleOrDefault(c => c == db.Categories.First());

// For Take(10):
var categories = db.Categories.Where(c => db.Categories.Take(10).Contains(c));

All of the LINQ statements above will return all of the category and product information for a subset of Categories entries in one query each.

Does anyone know if there is a better or more efficient way of achieving this? Thanks.

I have added a bounty as my informative non-answer isn't helpful enough for me. — Amy B, Nov 02 '10 at 14:56

score 1 · Accepted Answer · edited May 23 '17 at 12:11

1

You should read about Skip, Take and loading related collections here:

LINQ to SQL Take w/o Skip Causes Multiple SQL Statements

The bad news is that the solution won't work for you. CompiledQuery is allergic to LoadOptions - you'll get a runtime error.

Accessing the Products property of a Category instance will result in another query execution.

Nitty-pick. This isn't quite true. By the time your call to First returns, the Products will be fetched. The contract of LoadsWith is that your data gets fetched. The problem is that it doesn't guarantee the most efficient queries are used to do the fetching (a single query with ROWNUMBER in this case).

Here's a method implementation that confirms:

        DataClasses1DataContext myDC = new DataClasses1DataContext();
        var myOptions = new System.Data.Linq.DataLoadOptions();
        myOptions.LoadWith<Customer>(c => c.Orders);
        myDC.LoadOptions = myOptions;

        myDC.Log = Console.Out;


        myDC.Customers.Take(10).ToList();

1 query is issued for the Customers, followed by 10 queries, 1 for each customer's orders. These 10 queries occur even though no Customer instances' Orders properties were accessed.

This article by KWatkins demonstrates how to use LoadOptions with CompiledQuery. This opens up CompiledQuery as a way to lock in the Skip(x) even when x may be 0.

edited May 23 '17 at 12:11

Community

1
1

answered Oct 27 '10 at 17:05

Amy B

108,202
21
135
185

@David. Thanks for the extra read, even though it won't work for me. I'm still confused as to why you say my statement is false. Does it not result in more that one query execution? – jordanbtucker Oct 27 '10 at 18:40
More than one query results, but it is immediate and does not wait until you access the Products property. – Amy B Oct 27 '10 at 21:19
@David. I have not seen this in my testing. Is there documentation on this? – jordanbtucker Oct 28 '10 at 01:40
This fact: "LoadOptions can cause iterative querying" is undocumented. I have seen iterative queries generate from more cases than just the ones mentioned in the question. As such, only use LoadsWith if you're willing to examine the generated output (by DataContext.Log or Sql profiler). I would not use LoadsWith for dynamic querying scenarios. – Amy B Oct 28 '10 at 14:32
@David. In my tests of the above example, both DataContext.Log and SQL Server Profiler both show multiple query executions, but only once the Products property is accessed. Can you give me a reproducible scenario where the results are fetched before the property is accessed? Thanks. – jordanbtucker Oct 28 '10 at 22:34
See my edit from yesterday. Also - make sure you are setting the data load option. – Amy B Oct 29 '10 at 15:08
@David. I see it now. Thanks. It appears that the SqlProvider checks to see if the Expression contains a call to ToList(), or a List constructor, or ToArray(), or similar situations, and fetches the related records. – jordanbtucker Oct 30 '10 at 00:09

LINQ to SQL: Generating one query when calling First() or Take()

1 Answers1