LINQ to SQL Query Request

Question

Using LINQ to SQL

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

performed

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 uses ROW_NUMBER to paginate ... well.

Now I am trying to use the relationships generated by LINQ to SQL to break down data. Using request ...

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 using ROW_NUMBER and pagination has disappeared ... it gets all products where CategoryID = 1 ... why does it get ALL rows?

Answer 1

I think this is because the category is in memory. You ask him, implicitly, to get products in this category. This implicit query for data is populated, and then the query is executed in memory (where the category is at this point).

, :

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

, , ? .

: :)

Answer 2

:

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

Answer 3

LINQ. "cat" - .