Different Linq to Sql results based on whether or not methods are chained together
I'm learning Linq-to-Sql, and I'm encountering the scenario where I'm trying to build a dynamic query with sorting, filtering, and paging.
If I run a query like this I am able to get "paged" results:
IQueryable<WorkOrder> query = (_dataContext.WorkOrders).Skip((search.page - 1) * search.rows).Take(search.rows);
var retval = query.ToList();
in this case the generated query looks like this:
SELECT <columns>
FROM (
SELECT ROW_NUMBER() OVER (<columns>) AS [ROW_NUMBER], <columns>
FROM [dbo].[WorkOrders] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
However if I run the query like this, it is no longer paged:
IQueryable<WorkOrder> query = (_dataContext.WorkOrders);
query.Skip((search.page - 1) * search.rows).Take(search.rows);
var retval = query.ToList();
In this case, the generated query looks like this:
SELECT <cut for brevity>
FROM [dbo].[WorkOrders] AS [t0]
I can't really grok what is going here... exac开发者_StackOverflowtly when does Linq to Sql generate the query, and is it possible to add extra criteria like Skip, Take, OrderBy to an existing IQueryable? Where I'd like to end up would be something like this:
IQueryable<WorkOrder> query = (_dataContext.WorkOrders);
if (User.IsNotAuthorizedToSeeSomething) {
query.Where(...);
}
if (search.sortField.Equals("Name")) {
query.OrderBy(...);
}
query.Skip((search.page - 1) * search.rows).Take(search.rows);
var retval = query.ToList();
Instead of
IQueryable<WorkOrder> query = (_dataContext.WorkOrders);
query.Skip((search.page - 1) * search.rows).Take(search.rows);
var retval = query.ToList();
try
IQueryable<WorkOrder> query = (_dataContext.WorkOrders);
query = query.Skip((search.page - 1) * search.rows).Take(search.rows);
var retval = query.ToList();
each query.Xxx() is not applying Xxx on query object but returns new object.
It appears that in the second case it is going ahead and executing the query against the database when the first line executes. The Skip and Take are then executed as LINQ to Objects rather than LINQ to SQL.
I'm not really sure why it would do that though...
Do the values of retval look the same in both cases. (It should if what I think is happening really is happening.)
精彩评论