Asynchronous Paging with Entity Framework 6.1.3

Question

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.

I've implemented the paging mechanism as per this article: How to Increase the Performance of Entity Framework with Paging, 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.

As per article, I've create the interface:

public interface IPageList
{
    int TotalCount { get; }
    int PageCount { get; }
    int Page { get; }
    int PageSize { get; }
}

I created the class:

public class PageList<T> : List<T>, 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<T> source, int page, int pageSize)
    {
        TotalCount = source.Count();
        PageCount = GetPageCount(pageSize, TotalCount);
        Page = page < 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);
    }
}

and finally the extension:

public static class PageListExtensions
{
    public static PageList<T> ToPageList<T>(this IQueryable<T> source, int pageNumber, 
    int pageSize)
    {
        return new PageList<T>(source, pageNumber, pageSize);
    }
}

So in my data layer, I've got the following function:

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

        return await results.AsQueryable<LogEntity>().ToListAsync();
    }
}

When I run the above, I get:

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 http://go.microsoft.com/fwlink/?LinkId=287068.

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

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

Thanks

UPDATE-1

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

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

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

How to return empty IQueryable in an async repository method

UPDATE-2

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!

Anyway here's what I've changed:

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

            return await results.ToListAsync();
        }
    }

If anything, the code is definitely simpler than my original one.

Update-3:

When I call this:

return new PageList<LogEntity>(_dbContext.Logs, pageNumber, pageSize);

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.

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'.

So I fixed this by calling:

return new PageList<LogEntity>(_dbContext.Logs.OrderBy(o=>o.DateTime), 
pageNumber, pageSize);

When I tried to call @krillgar's simplest suggestion i.e.

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

I get the following error:

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.

on the this.TotalCount = source.Count(); in the PageList class.

Any ideas?

Answer 1

You're using async incorrectly here. Unless you're doing I/O, or very long operations you're typically only going to create extra overhead as the threads are created, managed, and merged.

Querying from the database is an I/O operation, however you haven't learned how Entity Framework behaves, so you're missing the benefit of making this operation asynchronous.

Entity Framework (and LINQ in general) uses a technique called Deferred Execution. What that means in this case is that nothing is sent to your database until you want to act on the data. You're able to conditionally add .Where(), .Skip(), etc to your heart's content, and EF will just sit there preparing to build the SQL query.

To send that SQL statement to the database, you need to act on it, which you do in your PageList constructor twice. The first is:

TotalCount = source.Count();

That takes the SQL with all of your WHERE statements, etc, prepends a SELECT COUNT (*), and fetches the result.

The second time is here:

AddRange(source.Skip(Page * PageSize).Take(PageSize).ToList());

At the end of the above line, the .ToList() will send another query to your database, retrieve all the columns and rows you ask for, and populate all of your entities. THIS is where you want your async, but you can't make an async constructor.

Your alternative would be to forego setting everything within the constructor and use a method instead, which can easily be made async.

In your original question, you started with this:

_dbContext.Logs.Select(l => new
    {
        LogId = l.LogId,
        Message = l.Message,
    })
    .OrderBy(o => o.DateTime)

You have also since updated to put the OrderBy() and .ToPageList() before your .Select(). However, you're still querying it as an anonymous object, so you once more need to continue casting after you should need to.

Going back to the root of your problem, we need to look at your return statement:

return await results.AsQueryable<LogEntity>().ToListAsync();

There's no need to do that, other than to put an artificial async call in there, which won't save you anything (see above). Your cast to .AsQueryable<T>() only adds to the processing, and doesn't give you anything.

The easiest way for you to use what you have is a little rearranging and elimination of redundant code. Your .ToPageList() already casts the object as a List<T>, so if you do things in the correct order, you'll save yourself a lot of grief:

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

That's really all that you need.

If you are dead-set on using async, then you should rework your class by adding a default constructor, and the following method:

public async Task CreateAsync(IQueryable<T> source, int page, int pageSize)
{
    TotalCount = await source.CountAsync(); // async here would help
    PageCount = GetPageCount(pageSize, TotalCount);
    Page = page < 1 ? 0 : page - 1;
    PageSize = pageSize;
    AddRange(await source.Skip(Page * PageSize)
                         .Take(PageSize)
                         .ToListAsync()); // async here too!
}

That can be cleaned up with refactoring, but that's the gist. Then call it like this:

// Get your query set up, but don't execute anything on it yet.
var results = _dbContext.Logs.Select(l => new LogEntity
                                    {
                                        LogId = l.LogId,
                                        l.Message
                                    })
                             .OrderBy(l => l.DateTime);

var pageList = new PageList<LogEntity>();
await pageList.Create(results, pageNumber, pageSize);

return pageList;