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