Linq to SQL - Joins and Skip+Take

Question

I have Linq-to-SQL code that works with a many-to-many relationship, but note that the relationship itself has its own set of attributes (in this case, Products are in Many Categories, and each product-in-category relation has its own SortOrder attribute).

I have a Linq-to-SQL block that returns matching Products with Category membership information. When I execute the code it generates optimised T-SQL code like so:

exec sp_executesql N'SELECT [t0].[ProductId], [t0].[Name], [t1].[ProductId] AS [ProductId2], [t1].[CategoryId], [t1].[SortOrder] AS [SortOrder2], [t2].[CategoryId] AS [CategoryId2], [t2].[Name] AS [Name2] (
SELECT COUNT(*)
FROM [dbo].[ProductsInCategories] AS [t3]
INNER JOIN [dbo].[Categories] AS [t4] ON [t4].[CategoryId] = [t3].[CategoryId]
WHERE [t3].[ProductId] = [t0].[ProductId]
) AS [value]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN ([dbo].[ProductsInCategories] AS [t1]
INNER JOIN [dbo].[Categories] AS [t2] ON [t2].[CategoryId] = [t1].[CategoryId]) ON [t1].[ProductId] = [t0].[ProductId]
WHERE (([t0].[OwnerId]) = @p0) AND ([t0].[Visible] = 1)
ORDER BY [t0].[SortOrder], [t0].[Name], [t0].[ProductId], [t1].[CategoryId]',N'@p0 bigint',@p0=3

However, when I add paging instructions (i.e.".Skip(0).Take(50)") to the Linq expression the generated SQL becomes this:

exec sp_executesql N'SELECT TOP (50) [t0].[ProductId], [t0].[Name]
FROM [dbo].[Products] AS [t0]
WHERE (([t0].[OwnerId]) = @p0) AND ([t0].[Visible] = 1)
ORDER BY [t0].[SortOrder], [t0].[Name]',N'@p0 bigint',@p0=3

Which means the Category membership information isn't loaded anymore, so Linq-to-SQL then executes the manual loading code 50 times over (one for each member in the returned set):

exec sp_executesql N'SELECT [t0].[ProductId], [t0].[CategoryId], [t0].[SortOrder], [t1].[CategoryId] AS [CategoryId2], [t1].[Name]
FROM [dbo].[ProductsInCategories] AS [t0]
INNER JOIN [dbo].[Categories] AS [t1] ON [t1].[CategoryId] = [t0].[CategoryId]
WHERE [t0].[ProductId] = @x1',N'@x1 bigint',@x1=1141

(obviously the "@x1" ID parameter varies for each result from the original query).

So clearly Linq paging breaks the query and causes it to load data separately. Is there a way around this or should I do paging in my own software?

...fortunately the number of products in the database is small enough (<500) to do this, but it just feels dirty because there could be tens of thousands of products, and this just wouldn't be a good query.

EDIT:

Here is my Linq:

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Product>( p => p.ProductsInCategories );
dlo.LoadWith<ProductsInCategory>( pic => pic.Category );
this.LoadOptions = dlo;

query = from p in this.Products
select p;

// The lines below are added conditionally:
query = query.OrderBy( p => p.SortOrder ).ThenBy( p => p.Name );
query = query.Where( p => p.Visible );
query = query.Where( p => p.Name.Contains( filter ) || p.Description.Contains( filter ) );
query = query.Where( p => p.OwnerId == siteId );

The skip/take lines are added optionally, and are the only differences that cause the different T-SQL generation (as far as I know):

IQueryable<Product> query = GetProducts( siteId, category, filter, showHidden, sortBySortOrder );

///////////////////////////////////

total = query.Count();

var pagedProducts = query.Skip( pageIndex * pageSize ).Take( pageSize );
return pagedProducts;

Answer 1

An alternative answer which first pages the products and then selects products and categories in a parent-child structure would be like this:

var filter = "a";
var pageSize = 2;
var pageIndex = 1;

// get the correct products
var query = Products.AsQueryable();

query = query.Where (q => q.Name.Contains(filter));
query = query.OrderBy (q => q.SortOrder).ThenBy(q => q.Name);

// do paging
query = query.Skip(pageSize*pageIndex).Take(pageSize);

// now get products + categories as tree structure
var query2 = query.Select(
    q=>new 
    {
        q.Name, 
        Categories=q.ProductsInCategories.Select (pic => pic.Category)
    });

Which produces a single SQL statement

-- Region Parameters
DECLARE @p0 NVarChar(1000) = '%a%'
DECLARE @p1 Int = 2
DECLARE @p2 Int = 2
-- EndRegion
SELECT [t2].[Name], [t4].[CategoryId], [t4].[Name] AS [Name2], [t4].[Visible], (
    SELECT COUNT(*)
    FROM (
        SELECT [t5].[CategoryId]
        FROM [ProductsInCategories] AS [t5]
        WHERE [t5].[ProductId] = [t2].[ProductId]
        ) AS [t6]
    INNER JOIN [Categories] AS [t7] ON [t7].[CategoryId] = [t6].[CategoryId]
    ) AS [value]
FROM (
    SELECT [t1].[ProductId], [t1].[Name], [t1].[ROW_NUMBER]
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY [t0].[SortOrder], [t0].[Name], [t0].[ProductId]) AS [ROW_NUMBER], [t0].[ProductId], [t0].[Name]
        FROM [Products] AS [t0]
        WHERE [t0].[Name] LIKE @p0
        ) AS [t1]
    WHERE [t1].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
    ) AS [t2]
LEFT OUTER JOIN ([ProductsInCategories] AS [t3]
    INNER JOIN [Categories] AS [t4] ON [t4].[CategoryId] = [t3].[CategoryId]) ON [t3].[ProductId] = [t2].[ProductId]
ORDER BY [t2].[ROW_NUMBER], [t3].[CategoryId], [t3].[ProductId]