What is the effect of using AsEnumerable() on paging?

Question

I understand that if you have some function in a linq query that does not map to a sql query then you must call .AsEnumerable() first:

var model = _service.GetQuery
                    .Where(data => data.SomeFlag == true)
                    .Select(data => new { Foo = CalculateFoo(data.Bar); });

Cannot be executed by linq to sql, but by adding AsEnumerable() we can make the .Select() clause execute by linq to objects instead:

var model = _service.GetQuery
                    .Where(data => data.SomeFlag == true)
                    .AsEnumerable()
                    .Select(data => new { Foo = CalculateFoo(data.Bar); });

But what if the dataset is very large - what affect does AsEnumerable have on paging? If I say:

var page = model.Skip((page > 0 ? page - 1 : 0) * rows).Take(rows);

Because model is now IEnumerable instead of IQueryable, when we say model.Skip().Take(), does it have to first load the entire dataset from the database before it can skip and take? (This would defeat the purpose of paging)

EDIT: The question was written generically - these are the concrete details:

I do not have control over the paging. I am generating a model and passing it to a grid component (DevExpress in this case, but could be any grid). It is the grid component that issues the paging commands. Any solution that involves using .Skip().Take() before AsEnumerable() is not possible here.

So I need to be able to pass this model to the grid, and also at the same time make sure that the model uses deferred execution:

var model = _service.GetQuery
                .Where(data => data.SomeFlag == true)
                .Select(data => new {
                     data.Id,
                     data.Customer.Code,
                     data.Customer.Name,
                     // etc, select a few other properties 
                     Foo = CalculateFoo(data.Bar);
                });

So now I have a rock vs hard place problem:

If I pass that model to the grid, it will throw an exception when showing the current page because linq to sql cannot execute CalculateFoo()
If I add AsEnumerable() then the grid can show the current page, but it has to load the entire dataset to do so (loading many thousands of rows just to display 200 of them)
If I leave the Foo column out of the model then we have deferred execution back again, but the grid is missing a column

possible duplicate of [Understanding .AsEnumerable() in LINQ to SQL](http://stackoverflow.com/questions/3311244/understanding-asenumerable-in-linq-to-sql) — Amy B, May 12 '12 at 22:18
I read that question. It doesn't mention paging or the effect that AsEnumerable has on paging. — JK., May 12 '12 at 22:39
AsEnumerable has a behavior. It doesn't care what you do with its result. If you understand that behavior, then you understand that behavior in your scenario. — Amy B, May 13 '12 at 13:30
After reading David's post, I think the answer is **Yes**, the skip/take after `AsEnumerable` will take a bad effect on performance if the table is large. — Junle Li, May 20 '15 at 15:58

score 1 · Answer 1 · answered May 12 '12 at 21:58

1

Just call Skip/Take before AsEnumerable():

var model = _service.GetQuery
                    .Where(data => data.SomeFlag == true)
                    .Skip((page > 0 ? page - 1 : 0) * rows).Take(rows)
                    .AsEnumerable()
                    .Select(data => new { Foo = CalculateFoo(data.Bar); });

answered May 12 '12 at 21:58

ie.

5,982
1
29
44

I wish it was that easy :) Question updated to explain why I cannot put .Skip().Take() before .AsEnumerable() – JK. May 12 '12 at 23:08

Raphaël Althaus · Accepted Answer · 2012-05-12T23:20:55.783

If you make paging, you need ...a page, and a total

so

var query= _service.GetQuery
                    .Where(data => data.SomeFlag == true);

ViewBag.Total = query.Count();
var model = query.Skip((page > 0 ? page - 1 : 0) * rows).Take(rows)
                    .AsEnumerable()
                    .Select(data => new { Foo = CalculateFoo(data.Bar); });

Because model is now IEnumerable instead of IQueryable, when we say model.Skip().Take(), does it have to first load the entire dataset from the database before it can skip and take? (This would defeat the purpose of paging)

That's a fact : you shall always have only your "accepted by linq2entites" query before "paging".

EDIT

I do not have control over the paging. I am generating a model and passing it to a grid component (DevExpress in this case, but could be any grid). It is the grid component that issues the paging commands. Any solution that involves using .Skip().Take() before AsEnumerable() is not possible here.

Well, "usually" Grid-systems allow (telerik) (or have only (MvcContrib) custom paging (which means you have to provide the "selected page" results + the total count, as in my answer).

I made a search (you could go further) on "DevExpress custom paging", which give few results. Don't know if they're interesting for you.

Examples of answer

http://www.devexpress.com/Support/Center/p/Q264453.aspx

http://community.devexpress.com/forums/p/98848/338135.aspx

Unfortunately I am not the one in control of the paging. All I can do is send a model. I'll update the question with details — JK., May 12 '12 at 22:55
+1 Thanks for the link - didn't answer the question at hand but it did lead me to realize that devex was not paging the linq correctly anyway - it was in fact loading all rows and then taking one page - the exact behavior I'm trying to avoid here. Looks like they have something called a linq datasource that could help solve the problem. — JK., May 14 '12 at 00:52

What is the effect of using AsEnumerable() on paging?

2 Answers2