Trouble getting total count using .Take() and .Skip()

Question

I'm having some trouble implementing some paging using Linq and I've read the various questions (this and this for example) on here but I'm still getting the error;

System.InvalidOperationException: The result of a query cannot be enumerated more than once.

My (slightly obfuscated) code is;

public List<Thing> GetThings(ObjectParameter[] params, int count, int pageIndex)
{
    var things = from t in Context.ExecuteFunction<Something>("function", params)
                 select new Thing
                 {
                     ID = t.ID
                 });

    var pagedThings = things;

    if (pageIndex == 0)
        pagedThings = things.Take(count);
    else if (pageIndex > 0)
        pagedThings = things.Skip(count * pageIndex).Take(count);

    var countOfThings = things.Count();

    return pagedThings.ToList();
}

As soon as the final .ToList() is called, the error is thrown but I can't see why - are the calls to things.Count() and pagedThings.ToList() enumerating the same thing?

Edit: I'm using Entity Framework if that makes any difference

Answer 1

ExecuteFunction actually returns an ObjectResult if I'm not mistaken, which is... more complicated. You may get different results if you make the function composable (which would execute a separate query when you Count()), but it's been a while since I worked with low level EF so I'm not 100% sure that would work.

Since you can't get out of executing what are effectively two queries, the safest bet is to make a completely separate one for counting - and by completely separate I mean a separate function or stored procedure, which just does the count, otherwise you may end up (depending on your function) returning rows to EF and counting them in memory. Or rewrite the function as a view if at all possible, which may make it more straightforward.

Answer 2

You are setting pagedThings = things. So you are working on the same object. You would need to copy things to a new collection if you want to do what you're trying above, but I would recommend refactoring this code in general.

You can check out this SO post to get some ideas on how to get the count without enumerating the list: How to COUNT rows within EntityFramework without loading contents?

Answer 3

In general, Linq is able to do that. In LinqPad, I wrote the following code and successfully executed it:

void Main()
{
    var sampleList = new List<int>();
    for (int i = 0; i < 100; i++){
        sampleList.Add(i);
    }

    var furtherQuery = sampleList.Take(3).Skip(4);
    var count = furtherQuery.Count();
    var cache = furtherQuery.ToList();

}

Note, as your error mentions, this will execute the query twice. Once for Count() and once for ToList().

It must be that the Linq provider that you are representing as Context.ExecuteFunction<Something>("function", params) is protecting you from making multiple expensive calls. You should look for a way to iterate over the results only once. As written, for example, you could .Count() on the List that you had already generated.

Answer 4

Normally, we call them pageIndex and pageSize.

Please check pageIndex whether 0 as start index or 1 as start index depending on your requirement.

public List<Thing> GetThings(ObjectParameter[] params, int pageIndex, int pageSize)
{
    if (pageSize <= 0)
        pageSize = 1;

    if (pageIndex < 0)
        pageIndex = 0;

    var source = Context.ExecuteFunction<Something>("function", params);

    var total = source.Count();

    var things = (from t in source select new Thing { ID = t.ID })
                .Skip(pageIndex * pageSize).Take(pageSize).ToList();

    return things.ToList();
}

Answer 5

Here is my implementation of your code. a few things to notice. 1. You can handle Skip in one statement. 2. The main method shows how to pass multiple pages into the method.

using System;
using System.Collections.Generic;
using System.Linq;


public class Program
{
    public static void Main()
    {
        List<Thing> thingList = new List<Thing>();
        for (int i = 0; i < 99; i++)
        {
            thingList.Add(new Thing(i));
        }
       int count = 20;
        int pageIndex = 0;
        int numberPages = (int)Math.Ceiling(thingList.Count * 1.0/ (count ));
        for( ; pageIndex < numberPages; pageIndex ++)
        {
           var myPagedThings = GetThings(thingList, count, pageIndex);
           foreach( var item in myPagedThings)
           {
            Console.WriteLine(item.ID  );
           }
        }
    }

    public static IEnumerable<Thing> GetThings(List<Thing> myList, int count, int pageIndex)
    {
        var things = (
            from t in myList
            select new Thing{ID = t.ID}).ToList();

        return things.Skip(count * pageIndex).Take(count);
    }
}

public class Thing
{
    public int ID
    { get; set; }

    public Thing (){}
    public Thing(int id)
    {   this.ID = id;   }
}

Answer 6

As it happens, ExecuteFunction causes the enumeration to occur immediately, ultimately meaning the code could be re-ordered and the copying of the list was not required - it now looks like the below

public ThingObjects GetThings(ObjectParameter[] params, int count, int pageIndex)
{
    var things = from t in Context.ExecuteFunction<Something>("function", params)
             select new Thing
             {
                 ID = t.ID
             }).ToList();

    var countOfThings = things.Count;

    if (pageIndex >= 0)
        things = things.Skip(count * pageIndex).Take(count);

    return new ThingObjects(things, countOfThings);
}