Querying against LINQ to SQL relationships

Question

Using LINQ to SQL

db.Products.Where(c => c.ID == 1).Skip(1).Take(1).ToList();

executes

SELECT [t1].[ID], [t1].[CategoryID], [t1].[Name], [t1].[Price], [t1].[Descripti
n], [t1].[IsFeatured], [t1].[IsActive]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID], [t0].[CategoryID], [t0].[Name
, [t0].[Price], [t0].[Description], [t0].[IsFeatured], [t0].[IsActive]) AS [ROW
NUMBER], [t0].[ID], [t0].[CategoryID], [t0].[Name], [t0].[Price], [t0].[Descrip
ion], [t0].[IsFeatured], [t0].[IsActive]
    FROM [dbo].[Products] AS [t0]
    WHERE [t0].[ID] = @p0
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
ORDER BY [t1].[ROW_NUMBER]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [1]

It's using ROW_NUMBER for pagination... good.

Now, I'm trying to use relationships generated by LINQ to SQL to paginate data. Using the query...

var cat = db.Categories.Where(c => c.ID == 1).SingleOrDefault();
cat.Products.Where(c => c.ID == 1).Skip(1).Take(1).ToList();

SELECT [t0].[ID], [t0].[Name]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[ID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

SELECT [t0].[ID], [t0].[CategoryID], [t0].[Name], [t0].[Price], [t0].[Descriptio
n], [t0].[IsFeatured], [t0].[IsActive]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[CategoryID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

Now the use of ROW_NUMBER and pagination is gone... it's getting all Products where CategoryID = 1... why is it getting ALL rows?

Answer 1

have you tried:

var cat = db.Categories.Where(c => c.ID == 1);
var prod = cat.Products.Where(c => c.ID == 1).Skip(1).Take(1).ToList();

Answer 2

You haven't assigned the output of your second LINQ query. So 'cat' is still the first query only.

Answer 3

I think its because the category is in memory. You are asking it, implicitly, to get the products of the category. This implicit request for data is for filled, and then in memory (where the category is at this point) the query is executed.

I'm thinking its equivalent to :

var cat = db.Categories.Where(c => c.ID == 1).SingleOrDefault();
var prods = db.Products.Where(c => c.ID == 1).ToList();
var r = prods.Where(p.CategoryID == cat.ID).Skip(1).Take(1);

Note the significance, what if cat changes in memory? The size of the collection could vary.

NOTE: Thanks for the headache :)