Combining Left join, .includes() and Group By in Entity Framework-CodePudding

I'm trying to perform multiple selects from different tables and joining the result together. The project is running in a .Net 5 environment utilising EF core 5.0.14. My end goal would be to have a list of objects that have the simple fields Id(int), Name(string), Date(string)(If it exists), Email(string), Image(string), Phone(string) and also the Lists of objects Employees and Parents.

I have split up my queries into the following:

var firstQuery =  _context.Records.GroupBy(r => r.RecordId)
   .Select(g => new 
       {
          Id = g.Key,
          Date = g.Max(x => x.Date)
   });

The first query results in a list of objects with an Id and the corresponding id's max date

var secondQuery =  _context.Users.Include(u => u.Employees)
                .Include(u => u.Parents)

The second query results in a list of objects that holds most of the data in simple fields and as Lists (for Employees and Parents)

And finally I try to left join them so that if there is record in firstQuery that matches the Id in secondQuery they should be merged otherwise the date field should be null:

var result = (from u in secondQuery
                        from p in firstQuery
                        .Where(p => p.Id==u.Id)
                        .DefaultIfEmpty()
                        select new UserRecordsRequest{
                            Id = u.Id,
                            Email = u.Email,
                            Name = u.Name,
                            Date = (string?)p.Date,
                            Phone = u.Phone,
                            Image = u.Image,
                            Employees = u.Employees,
                            Parents = u.Parents,
                        }.toList()

The UserRecordsModel looks as follows:

public class UserRecordsModel
    {
        [Key]
        public int Id { get; set; }
        public string Email { get; set; }
        public string Date { get; set; }
        public string Name { get; set; }
        public string Phone { get; set; }
        public virtual List<EmployeeModel> Employees { get; set; }
        public virtual List<UserModel> Parents { get; set; }
        public UserRecordsModel() { }
        public UserRecordsModel(UserRecordsRequest userRecordsRequest)
        {

            Email = userRecordsRequest.Email;
            Name = userRecordsRequest.Name;
            Date = userRecordsRequest.Date;
            Phone = userRecordsRequest.Phone;
        }
    }

Simplified ER-diagram: But this throws an exception:

System.InvalidOperationException: Unable to translate collection subquery in projection since the parent query doesn't project key columns of all of it's tables which are required to generate results on client side. This can happen when trying to correlate on keyless entity or when using 'Distinct' or 'GroupBy' operations without projecting all of the key columns.

Everything looks good in the separate queries until the last join one. I have spent oh so many hours trying to figure this one out. What have I misunderstood about this issue and how would you change it?

CodePudding user response：

That is because of the query starts being built with a Keyless entity type and also you Include some collection navigation properties on that Keyless entity.

In the first go you can check the generated equivalent query in SQl-Profiler or by Debug watcher to see what query generated.

but by executing that query the first sub-query will executed first so try to enforce firstQuery be run before anything by ToList() like:

var firstQuery =  _context.Records.GroupBy(r => r.RecordId)
   .Select(g => new 
       {
          Id = g.Key,
          Date = g.Max(x => x.Date)
   }).ToList();

if that doesn't work try this:

var veryFirstQuery =  _context.Records.GroupBy(r => r.RecordId)
   .Select(g => new 
       {
          Id = g.Key,
          Date = g.Max(x => x.Date)
   }).SelectMany(x=> x.Select(u=> u));
var firstQuery = veryFirstQuery.Select(x=> x.Id ).ToList();

and then(this is not correct syntax but just copy from your query and change what I think it needs):

var result = (from u in secondQuery 
                         where firstQuery.Contain(u.Id) 
                         select u)
                        .DefaultIfEmpty()
                        select new UserRecordsRequest{
                            Id = u.Id,
                            Email = u.Email,
                            Name = u.Name,
                            Date = (string?)veryFirstQuery.FirstOrDefault(x=> x.Id == u.Id).Date,
                            Phone = u.Phone,
                            Image = u.Image,
                            Employees = u.Employees,
                            Parents = u.Parents,
                        }.ToList()

or you can:

var result = (from u in secondQuery
                where firstQuery.Contain(u.Id)
                select u)
.DefaultIfEmpty().ToList()
.Select(u => new UserRecordsRequest
{
    Id = u.Id,
    Email = u.Email,
    Name = u.Name,
    Date = (string?)veryFirstQuery.FirstOrDefault(sb => sb.Id == u.Id).Date,
    Phone = u.Phone,
    Image = u.Image,
    Employees = u.Employees,
    Parents = u.Parents,
});

I am trying to say you can separate your main query to little queries to prevent that InvalidOperationException or review your models relationship in your Fluent-Api.