Asynchronous Paging with Entity Framework 6.1.3
<p>I'm new to EF so apologies in advanced if something isn't done correctly. I'm struggling to get paging to work asynchronously with EF 6.</p>

<p>I've implemented the paging mechanism as per this article: <a href="https://developingsoftware.com/entity-framework-paging/" rel="nofollow noreferrer">How to Increase the Performance of Entity Framework with Paging</a>, which I thought was clean and to the point (but not perfect either) but I can't get this to work asynchronously which is a problem.</p>

<p>As per article, I've create the interface:</p>

<pre><code>public interface IPageList
{
    int TotalCount { get; }
    int PageCount { get; }
    int Page { get; }
    int PageSize { get; }
}
</code></pre>

<p>I created the class:</p>

<pre><code>public class PageList&lt;T&gt; : List&lt;T&gt;, IPageList
{
    public int TotalCount { get; private set; }
    public int PageCount { get; private set; }
    public int Page { get; private set; }
    public int PageSize { get; private set; }

    public PageList(IQueryable&lt;T&gt; source, int page, int pageSize)
    {
        TotalCount = source.Count();
        PageCount = GetPageCount(pageSize, TotalCount);
        Page = page &lt; 1 ? 0 : page - 1;
        PageSize = pageSize;
        AddRange(source.Skip(Page * PageSize).Take(PageSize).ToList());
    }

    private int GetPageCount(int pageSize, int totalCount)
    {
        if (pageSize == 0)
            return 0;

        var remainder = totalCount % pageSize;
        return (totalCount / pageSize) + (remainder == 0 ? 0 : 1);
    }
}
</code></pre>

<p>and finally the extension:</p>

<pre><code>public static class PageListExtensions
{
    public static PageList&lt;T&gt; ToPageList&lt;T&gt;(this IQueryable&lt;T&gt; source, int pageNumber, 
    int pageSize)
    {
        return new PageList&lt;T&gt;(source, pageNumber, pageSize);
    }
}
</code></pre>

<p>So in my data layer, I've got the following function:</p>

<pre><code>public async Task&lt;List&lt;LogEntity&gt;&gt; GetLogsAsync(int pageNumber, int pageSize)
{
    using (_dbContext = new DatabaseContext())
    {                            
        var results = _dbContext.Logs.Select(l =&gt; new
        {
            LogId = l.LogId,
            Message = l.Message,
        })
        .OrderBy(o =&gt; o.DateTime)
        .ToPageList(pageNumber, pageSize).ToList().Select(x =&gt; new LogEntity()
        {
            LogId = x.LogId,
            Message = x.Message,
        });

        return await results.AsQueryable&lt;LogEntity&gt;().ToListAsync();
    }
}
</code></pre>

<p>When I run the above, I get:</p>

<blockquote>
  <p>Additional information: The source IQueryable doesn't implement
  IDbAsyncEnumerable. Only sources that
  implement IDbAsyncEnumerable can be used for Entity Framework
  asynchronous operations. For more details see
  <a href="http://go.microsoft.com/fwlink/?LinkId=287068" rel="nofollow noreferrer">http://go.microsoft.com/fwlink/?LinkId=287068</a>.</p>
</blockquote>

<p>I've googled the error and while I've read numerous articles, I'm still struggling to get it to work.</p>

<p>Can anyone tell me exactly how to resolve this problem as I have no idea where to start at this stage.</p>

<p>Thanks</p>

<p><strong>UPDATE-1</strong></p>

<p>As Ivan highlighted in his comment, I don't think I need the 2 <code>Select</code>, so here is the simplified version:</p>

<pre><code>var results = _dbContext.Logs.OrderBy(o=&gt;o.DateTime)
    .ToPageList(pageNumber, pageSize).Select(l =&gt; new
{
    LogId = l.LogId,
    Message = l.Message,
});
</code></pre>

<p>Still doesn't sort my async problem. I'm currently looking at this article which will hopefully help:</p>

<p><a href="https://stackoverflow.com/questions/33305495/how-to-return-empty-iqueryable-in-an-async-repository-method">How to return empty IQueryable in an async repository method</a></p>

<p><strong>UPDATE-2</strong></p>

<p>I think I figured it out but it still not as responsive as I'd like it to be, so I'm not 100% sure whether or not it is done correctly. I thought that when swapping to my logs tab in my WPF app, the swapping would have been instantaneous but it's not!</p>

<p>Anyway here's what I've changed:</p>

<pre><code>    public async Task&lt;List&lt;LogEntity&gt;&gt; GetLogsAsync(int pageNumber, int pageSize)
    {
        using (_dbContext = new DatabaseContext())
        {
            var results = _dbContext.Logs.OrderBy(o=&gt;o.DateTime).ToPageList(pageNumber, pageSize).Select(l =&gt; new LogEntity
            {
                LogId = l.LogId,
                Message = l.Message,
            }).AsAsyncQueryable();

            return await results.ToListAsync();
        }
    }
</code></pre>

<p>If anything, the code is definitely simpler than my original one.</p>

<p><strong>Update-3:</strong></p>

<p>When I call this:</p>

<pre><code>return new PageList&lt;LogEntity&gt;(_dbContext.Logs, pageNumber, pageSize);
</code></pre>

<p>It returns the TotalCount = 100,000, PageCount = 200, Page = 0, PageSize 500, but then it throws an error when the AddRange is called i.e.</p>

<blockquote>
  <p>An exception of type 'System.NotSupportedException' occurred in
  EntityFramework.SqlServer.dll but was not handled in user code
  Additional information: The method 'Skip' is only supported for sorted
  input in LINQ to Entities. The method 'OrderBy' must be called before 
  the method 'Skip'.</p>
</blockquote>

<p>So I fixed this by calling:</p>

<pre><code>return new PageList&lt;LogEntity&gt;(_dbContext.Logs.OrderBy(o=&gt;o.DateTime), 
pageNumber, pageSize);
</code></pre>

<p>When I tried to call @krillgar's simplest suggestion i.e.</p>

<pre><code>return _dbContext.Logs
       .Select(l =&gt; new LogEntity // Cast here so your .ToPageList
       { // will start as the object type you want.
         LogId = l.LogId,
         Message = l.Message    
       })
       .OrderBy(l =&gt; l.DateTime)
       .ToPageList(pageNumber, pageSize);
</code></pre>

<p>I get the following error:</p>

<blockquote>
  <p>An exception of type 'System.NotSupportedException' occurred in
  EntityFramework.SqlServer.dll but was not handled in user code
  Additional information: The entity or complex type
  'MyCompany.DataLayerSql.LogEntity' cannot be constructed in a LINQ to
  Entities query.</p>
</blockquote>

<p>on the <code>this.TotalCount = source.Count();</code> in the PageList class.</p>

<p>Any ideas?</p>