实体框架6.1.3的异步分页 [英] Asynchronous Paging with Entity Framework 6.1.3
问题描述
我是EF的新手，所以如果操作不正确，请多加道歉.我正在努力使分页与EF 6异步工作.
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; }
}
我创建了课程:
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);
}
}
最后是扩展名:
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:
其他信息:IQueryable源未实现IDbAsyncEnumerable.仅来源实现IDbAsyncEnumerable可以用于实体框架异步操作.有关更多详细信息，请参见 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.
谢谢
UPDATE-1
正如Ivan在他的评论中强调的那样，我认为我不需要2 Select ，所以这是简化版本:
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:
UPDATE-2
我认为我已经弄清楚了，但是它仍然没有我希望的那样响应，因此我不确定100％是否正确完成了操作.我以为当交换到WPF应用程序中的日志"选项卡时，交换本来是瞬时的，但不是！
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.
更新3:
当我这样称呼时:
return new PageList<LogEntity>(_dbContext.Logs, pageNumber, pageSize);
它返回TotalCount = 100,000，PageCount = 200，Page = 0，PageSize 500，但是当调用AddRange时即抛出错误.
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.
发生"System.NotSupportedException"类型的异常EntityFramework.SqlServer.dll，但未在用户代码中处理附加信息:仅对排序使用跳过"方法在LINQ中输入Entities.必须先调用方法"OrderBy"方法跳过".
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);
当我尝试致电@krillgar最简单的建议时，即
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);
我收到以下错误:
发生"System.NotSupportedException"类型的异常EntityFramework.SqlServer.dll，但未在用户代码中处理附加信息:实体或复杂类型无法在LINQ中构造"MyCompany.DataLayerSql.LogEntity"以实体查询.
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.
在PageList类中的 this.TotalCount = source.Count(); 上.
on the this.TotalCount = source.Count(); in the PageList class.
有什么想法吗?
推荐答案
您在此处错误地使用了 async .除非您要进行I/O或非常长的操作，否则通常只会在创建，管理和合并线程时增加额外的开销.
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.
从数据库中查询是一个I/O操作，但是您还没有了解Entity Framework的行为方式，因此缺少了使该操作异步化的好处.
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.
实体框架(通常为LINQ)使用称为延迟执行.在这种情况下，这意味着在您希望对数据进行操作之前，不会将任何内容发送到您的数据库.您可以有条件地向您的内容中添加 .Where()， .Skip()等，EF会坐在那里准备构建SQL查询.
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.
要将该SQL语句发送到数据库，您需要对其执行 act ，这需要在 PageList 构造函数中执行两次.第一个是:
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();
这将SQL与您的所有 WHERE 语句等配合使用，并添加 SELECT COUNT(*)，然后获取结果.
That takes the SQL with all of your WHERE statements, etc, prepends a SELECT COUNT (*), and fetches the result.
第二次在这里:
AddRange(source.Skip(Page * PageSize).Take(PageSize).ToList());
在上一行的末尾， .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.
您的替代方法是放弃在构造函数中设置所有内容，而改用一种可以轻松实现 async 的方法.
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)
此后，您还进行了更新，将 OrderBy()和 .ToPageList()放在 .Select()之前.但是，您仍在以匿名对象的形式查询它，因此您又需要在需要时继续进行投射.
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.
回到问题的根源，我们需要查看您的return语句:
Going back to the root of your problem, we need to look at your return statement:
return await results.AsQueryable<LogEntity>().ToListAsync();
除了在此处进行人工异步调用外，无需执行此操作，这将不会为您节省任何费用(请参见上文).您对 .AsQueryable< T>()的强制转换只会增加处理过程，而不会给您带来任何好处.
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.
使用现有资源的最简单方法是重新排列并消除冗余代码.您的 .ToPageList()已经将对象强制转换为 List< T> ，因此，如果以正确的顺序执行操作，您将省去很多麻烦:
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.
如果您习惯使用 async ，则应该通过添加默认构造函数和以下方法来重做您的类:
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;
这篇关于实体框架6.1.3的异步分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
