CODEWITHSUNDEEP
asp.netsqllinqlinq-to-sql
Chad Moran
Chad Moran

Reputation: 12854

Querying against LINQ to SQL relationships

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?

Upvotes: 3

Views: 811

Answers (3)

ccook
ccook

Reputation: 5959

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 :)

Upvotes: 2

DarkwingDuck
DarkwingDuck

Reputation: 2706

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

Upvotes: -1

John Boker
John Boker

Reputation: 83699

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();

Upvotes: 0

Related Questions