c# - Retrieve outstanding invoices with currency conversion complexity in LINQ -

in entity framework application, have entity called invoice.cs, has various properties, here ones we're concerned question:

public class invoice : ientity {   public int id { get; set; }   public decimal amount { get; set; }   public datetime date { get; set; }   public int orderid { get; set; }   public virtual icollection<payment> payments { get; set; } } 

i attempting query database list of outstanding invoices. outstanding invoice following:

if total of payments made against invoice less invoice amount, invoice outstanding.

i'm stuck on working out if invoice outstanding or not in linq query, looks this:

var outstandinginvoices = inv in _context.invoices     !inv.isdeleted && inv.date >= startdate && inv.date <= enddate     select inv; 

startdate , enddate parameters passed in filter result.

another complexity how payments made. payments can made in rmb (chinese currency) or gbp (uk currency), , in report i'm generating, want displayed gbp.

so must somehow add logic too:

// loop through payments each invoice, , add payment // amount local variable called total if (payment.currency == currency.gbp) {   total += payment.amount; } else {   total += payment.amount / (decimal)payment.conversionrate; } 

the payment.cs entity has these 2 properties of concern:

public class paymentviewmodel {   public int id { get; set; }   [required]   public decimal amount { get; set; }   [required]   [display(name = "payment currency")]   public currency currency { get; set; }   [display(name = "conversion rate")]   public float conversionrate { get; set; } } 

you going have issue because of float/decimal differences. depending on database provider, might allow force cast decimal float (or might not). of course, you'll have issues of payments close, not quite same. if amount / coversionrate 0.999999999999999999999999999 gbp when amount 1 gbp? technically it's not paid.

ultimately, conversion rate should decimal, not float, determining precision depends on source. accurate 5 decimal places or 7?

var outstanding = _context.invoices   .where(x=>x.date >= startdate)   .where(x=>x.date <= enddate)   .where(x=>!x.isdeleted)   .where(x=>x.payments.sum(p=>(float)p.amount / p.conversionrate) < x.amount); 

alternatively if total paid within 1 gbp:

var outstanding = _context.invoices   .where(x=>x.date >= startdate)   .where(x=>x.date <= enddate)   .where(x=>!x.isdeleted)   .where(x=>x.payments.sum(p=>(float)p.amount / p.conversionrate) - x.amount < 1);