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.

Answer 1

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

http://stackoverflow.com/questions/857807/linq-to-sql-take-w-o-skip-causes-multiple-sql-statements/869340#869340

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.