c# - .NET Core 3.1 LINQ expression from could not be translated for lists within a list

Question

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.

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

The LINQ expression [...] could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

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

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.

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.

Answer 1

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();