title: Retrieve outstanding invoices with currency conversion complexity in LINQ
body: <p>In my Entity Framework application, I have an Entity called <code>Invoice.cs</code>, it has various properties, but here are the ones we're concerned about for the question:</p>
  
  <pre><code>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&lt;Payment&gt; Payments { get; set; }
  }
  </code></pre>
  
  <p>I am attempting to query the database to get a list of all outstanding invoices. An outstanding invoice is the following:</p>
  
  <blockquote>
    <p>If the <strong>total of the payments</strong> made against the Invoice are <strong>less than the Invoice Amount</strong>, then the invoice is <em>Outstanding</em>.</p>
  </blockquote>
  
  <p>I'm stuck on working out if an invoice is outstanding or not in my LINQ query, which currently looks like this:</p>
  
  <pre><code>var outstandingInvoices = from inv in _context.Invoices
      where !inv.IsDeleted &amp;&amp; inv.Date &gt;= startDate &amp;&amp; inv.Date &lt;= endDate
      select inv;
  </code></pre>
  
  <p><code>startDate</code> and <code>endDate</code> are parameters passed in to filter the result.</p>
  
  <p>Another complexity with this is to do with how payments are made. The payments can be made in RMB (Chinese currency) or GBP (UK currency), and in the report I'm generating, I want all to be displayed as GBP.</p>
  
  <p>So I must somehow add this logic too:</p>
  
  <pre><code>// loop through payments for each invoice, and add payment
  // amount to a local variable called total
  if (payment.Currency == Currency.Gbp)
  {
    total += payment.Amount;
  } else
  {
    total += payment.Amount / (decimal)payment.ConversionRate;
  }
  </code></pre>
  
  <p>The <code>Payment.cs</code> entity has these two properties that are of concern:</p>
  
  <pre><code>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; }
  }
  </code></pre>