Search code examples
c#linqlinq-to-sqllinq-to-objectsasenumerable

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

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
Solution

  • 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