无法将.NET Core 3.1 LINQ表达式从中转换为列表中的列表 [英] .NET Core 3.1 LINQ expression from could not be translated for lists within a list

问题描述

我有一个列表，其中包含博客上帖子的ID.该列表是通过一个单独的系统生成的，该系统允许用户从多个参数中过滤帖子.我已经对其进行了测试，并且可以正常工作.我的目标是获取此列表并执行LINQ查询，以获取发布该帖子的用户，并根据发布这些帖子的用户对这些帖子进行排序.查询如下:

I have an List containing ids for posts on a blog. The list is generated through a separate system that allows the user to filter posts from a number of parameters. I have tested it, and it works. My objective is to take this List and perform a LINQ Query that grabs the users who have made the posts, and sort the posts according to who posted them. Query as follows:

 List<UserPostDTO> UserPosts =
                await (from x in _context.UserPosting //UserPosting links User and Post tables in the db. It has both user id and post id
                       where PostedByList.Contains(x.Posting.PostingId) //PostedByList is a List<int>
                       && x.Approved == "A"       //only show posts that have been approved
                       && x.User.Active == true   //only show posts from active users
                       group x by new { x.User.FirstName, x.User.LastName, x.User.UserId } into xGroup
                       select new UserPostDTO  //Object containing First/Last name, email, and List<PostPOCO>
                       {
                           FirstName = xGroup.Key.FirstName,  //string
                           LastName = xGroup.Key.LastName,    //string
                           UserEmail = xGroup.Key.UserId,     //int
                           //PostsByUser = (from y in xGroup    //List of PostPOCO objects
                           //               select new PostPOCO
                           //               {
                           //                   PostTitle = y.Posting.Title, //string
                           //                   PostId = y.Posting.PostingId //int
                           //               }).ToList()
                        }).ToListAsync(); //I have read this should solve the issue. It does not.

当我取消注释PostsByUser的查询部分时，在单击查询时会出现以下错误:

When I uncomment out the part of the query for PostsByUser, it gives the following error upon hitting the query:

LINQ表达式[...]无法翻译.以一种可以翻译的形式重写查询，或者通过插入对AsEnumerable()，AsAsyncEnumerable()，ToList()或ToListAsync()的调用来显式切换到客户端评估.请参阅 https://go.microsoft.com/fwlink/?linkid=2101038有关更多信息.

如果对代码的那部分进行注释，那么它将运行而没有任何错误.

If that part of the code is left commented, it runs without any errors.

我找不到解决办法.我尝试用 _context.UserPosting.AsEnumerable()替换第二行，但这与.ToListAsync()冲突，后者应该是客户端/服务器评估变通办法的一部分.所以那里没有运气.

I can't figure a way around this. I have tried replacing the second line with _context.UserPosting.AsEnumerable() , but this conflicts with the .ToListAsync(), which was supposed to be part of the client/server Evaluation workaround anyway. So no luck there.

这是个问题，因为子列表取决于主查询.

This is a bit of an issue since the sub list depends on the main query.

反正有解决此问题的方法吗?我已经在LinqPad中尝试过了，并且可以正常工作.它可以在Visual Studio中编译而不会出现错误.实际尝试运行时它只是崩溃了.

Is there anyway to fix this? I have tried it in LinqPad and it works. It compiles in Visual Studio without error. It just crashed when actually trying to run.

推荐答案

我知道了.事实证明，微软对EF Core进行的新更改所施加的限制改变了Group子句中的工作方式:

I figured it out. Turns out the limitations imposed by Microsoft's new changes in EF Core altered what works in the Group clause: https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

要使其正常工作，它需要对数据库进行全新查询.可能不理想，但是有:

To get this to work, it needs a fresh query on the database. Possibly not ideal, but there it is:

List<UserPostDTO> UserPosts =
   await (from x in _context.UserPosting 
          where PostedByList.Contains(x.Posting.PostingId)
           && x.Approved == "A"      
           && x.User.Active == true   
           group x by new { x.User.FirstName, x.User.LastName, x.User.UserId } into xGroup
           select new UserPostDTO 
                       {
                           FirstName = xGroup.Key.FirstName,  
                           LastName = xGroup.Key.LastName,    
                           UserEmail = xGroup.Key.UserId,     
                           PostsByUser = (from y in _context.UserPosting
                                          where xGroup.Key.UserId == y.UserId    
                                          select new PostPOCO
                                          {
                                              PostTitle = y.Posting.Title, 
                                              PostId = y.Posting.PostingId 
                                          }).ToList()
                        }).ToListAsync(); 

这篇关于无法将.NET Core 3.1 LINQ表达式从中转换为列表中的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！