Getting error 'Unable to translate a collection subquery in a projection' when using union and select together in EF Core 7

Question

I wrote a code that EF Core creates an expression for that looks like this:

DbSet<Reception>()
    .Include(x => x.Employee)
    .Include(x => x.ReceptionSignatures)
    .Where(x => x.Employee.FirstName.Contains("mo"))
    .Union(DbSet<Reception>()
        .Include(x => x.Employee)
        .Include(x => x.ReceptionSignatures)
        .Where(x => x.Employee.PersonelId.Contains("mo")))
    .Union(DbSet<Reception>()
        .Include(x => x.Employee)
        .Include(x => x.ReceptionSignatures)
        .Where(x => x.Employee.LastName.Contains("mo")))
    .Union(DbSet<Reception>()
        .Include(x => x.Employee)
        .Include(x => x.ReceptionSignatures)
        .Where(x => x.Employee.NationId.Contains("mo")))
    .OrderBy(x => x.Employee.FirstName.CompareTo("mo") == 0 ? 0 : 1)
    .Select(r => new ReceptionAllDTO
    { 
        ReceptionId = r.Id, 
        NationId = r.Employee.NationId, 
        PersonelId = r.Employee.PersonelId, 
        FirstName = r.Employee.FirstName, 
        LastName = r.Employee.LastName, 
        Birthday = r.Employee.Birthday, 
        RecepDate = r.RecepDate, 
        Height = r.Height, 
        Weight = r.Weight, 
        ReceptionSignatures = r.ReceptionSignatures, 
    })

In Reception entity, I have a relation to Signature like this:

public virtual ICollection<Signature> ReceptionSignatures { get; set; }

but when EF Core wants to create a query for SQL, it throws this exception:

Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.

Answer 1

I got this exception when I was using more than two Unions() in a row like

query1.Union(query2).Union(query3)

When I changed it to nested unions it was working:

query1.Union(query2.Union(query3));

Answer 2

It seems like you are querying for more data which is really not efficient. Its better to project your required columns using the Select() and then write a Union.

When writing the Union the number of columns Selected must be same as shown below from a code base i wrote 2 weeks ago and which works.

var billPaymentVoucherQuery = _context.Set<BillPaymentVoucher>().AsQueryable();
var billsQuery = _context.Set<Bill>().AsQueryable();
    
var anon_billsQuery = billsQuery.Where(w => w.InvoiceDate.Date <= filter.AsAtDate.Date)
                                            .Where(w => w.OperationalStatus == OperationalBillStatus.Approved &&
                                                        (
                                                          w.FinancialStatus == FinancialBillStatus.Pending ||
                                                          w.FinancialStatus == FinancialBillStatus.OnHold ||
                                                          w.FinancialStatus == FinancialBillStatus.PartiallyApproved ||
                                                          w.FinancialStatus == FinancialBillStatus.Approved
                                                        ))
                                            .Select(s => new
                                            {
                                                VendorName = s.VendorInvoice.Vendor!.Name,
                                                Type = "Bill",
                                                Date = s.InvoiceDate,
                                                Number = Convert.ToString(s.InvoiceNumber),
                                                Amount = s.LineItemTotal + s.VATAmount
                                            }).AsQueryable();
    
            var anon_billPaymentVoucherQuery = billPaymentVoucherQuery
                                              .Where(w => (
                                                           w.UpdatedOn.HasValue &&
                                                           w.UpdatedOn.Value.Date <= filter.AsAtDate.Date
                                                          )
                                                          ||
                                                          (
                                                           w.UpdatedOn.HasValue == false &&
                                                           w.CreatedOn.Date <= filter.AsAtDate.Date
                                                          ))
                                              .Where(w => w.BillPaymentVoucherStatus == BillPaymentVoucherStatus.Paid)
                                              .Select(s => new
                                              {
                                                  VendorName = s.PaymentApprovedBill.Bill.VendorInvoice.Vendor!.Name,
                                                  Type = "Payment",
                                                  Date = s.UpdatedOn ?? s.CreatedOn,
                                                  Number = Convert.ToString(s.PaymentApprovedBill.Bill.InvoiceNumber + " | " +
                                                                            s.PaymentVoucherNumber),
                                                  Amount = -s.PayAmount
                                              }).AsQueryable();
    
            var unionedQuery = anon_billsQuery.Union(anon_billPaymentVoucherQuery)
                                              .Where(w => string.IsNullOrWhiteSpace(filter.Type) || w.Type == filter.Type);
    
            int pageSize = 2;
            bool hasMoreRecords = true;
    
            
                var transactionData = await unionedQuery.OrderBy(w => w.VendorName)
                                                    .ThenBy(w => w.Date)
                                                    .Skip((paginator.PageNumber - 1) * pageSize)
                                                    .Take(pageSize)
                                                    .ToListAsync(token);