EF Core error: "Comparison on entity type is not supported because it is a keyless entity" when doing outer join

Viewed 879

I have two tables. One for invoices and one for payments. The invoice table has the schema like this.

public class Invoice
{
  public Guid ID {get;set;}
  public string InvoiceNum {get;set;}
  public decimal Amount {get;set;}  
}

The payment table has the schema like this.

public class Payment
{
  public Guid ID {get;set;}
  public InvoiceID {get;set;}
  public decimal PaidAmount {get;set;}
  public DateTimeOffset? VoidDate {get;set;}
  public virtual Invoice Invoice { get; set; }
}

I want to get all invoices which have not been fully paid. Firstly I declared an entity called PaymentStatus.

public class PaymentStatus
{
  public Guid InvoiceID {get;set;}
  public decimal TotalPaidAmount {get;set;}
}

Then in my DbContext class, I have

public virtual DbSet<Invoice> Invoices { get; set; }

public virtual DbSet<PaymentStatus> PaymentStatuses { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
            modelBuilder.Entity<PaymentStatus>().ToQuery(() =>
                Payment.Where(r => r.VoidDate == null)
                    .GroupBy(r => r.InvoiceID)
                    .Select(r => new PaymentStatus{ InvoiceID = r.Key, TotalPaidAmount = r.Sum(p => p.PaidAmount) }))
                .HasNoKey()
                .HasOne(r=>r.Invoice)
                .WithOne().HasForeignKey<PaymentStatus>(r=>r.InvoiceID);
}

Then I tried to get the list of unpaid invoices by outer joining Invoices and PaymentStatuses.

public Task<List<Receivables>> GetReceivables()
{
  var qry = from inv in MyDbContext.Invoices
    from pay in MyDbContext.PaymentStatuses.Where(p => p.InvoiceID == inv.ID).DefaultIfEmpty()
    select new Receivables
    {
      InvoiceID = inv.ID,
      InvoiceAmount = inv.Amount,
      PaidAmount = pay==null ? 0 : pay.TotalPaidAmount, --here causes the error
    };
  return qry.Where(r=>r.InvoiceAmount > r.PaidAmount ).ToListAsync();
}

When the above codes execute, I gets the error System.InvalidOperationException: Comparison on entity type 'PaymentStatus' is not supported because it is a keyless entity.

The error is caused by the line

PaidAmount = pay==null ? 0 : pay.TotalPaidAmount 

If it is changed to

PaidAmount = pay.TotalPaidAmount 

Then it works in UnitTest (where InMemory Database is used) but failed in SQL Server because the translated SQL is filtering records using

WHERE (inv.Amount > pay.PaidAmount)

When an invoice is not paid, then PaymentStatuses has no corresponding record for the invoice and so pay.PaidAmount is Null (not zero). inv.Amount > Null is not satisfied.

I'm using EntityFrameworkCore 3.1.5

What I do not understand is why

pay==null ? 0 : pay.TotalPaidAmount

throwing error and how can it be solved

1 Answers

I solved the problem by making TotalPaidAmount nullable in PaymentStatus

public class PaymentStatus
{
  public Guid InvoiceID {get;set;}
  public decimal? TotalPaidAmount {get;set;}
}

Then change the query in getting Receivables

public Task<List<Receivables>> GetReceivables()
{
  var qry = from inv in MyDbContext.Invoices
    from pay in MyDbContext.PaymentStatuses.Where(p => p.InvoiceID == inv.ID).DefaultIfEmpty()
    select new Receivables
    {
      InvoiceID = inv.ID,
      InvoiceAmount = inv.Amount,
      PaidAmount = pay.TotalPaidAmount ?? 0,
    };
  return qry.Where(r=>r.InvoiceAmount > r.PaidAmount ).ToListAsync();
}
Related