title: Viewmodel and LINQ query to represent 4 linked tables in Asp.Net MVC
body: <p>I'm using a typical invoice system as a test for developing MVC and ViewModel knowledge, before tackling migrating my employers inhouse systems from asp to asp.net MVC.</p>
  
  <p>I know ViewModels are the recommended way to display info in the view - so I was hoping for some help "flattening" the viewmodel for the following:</p>
  
  <p>tables: Invoice, InvoiceItem, Payment, PaymentInvoice</p>
  
  <p>Invoice and InvoiceItem are linked, and Payment (which records an overall payment), and PaymentInvoice (which lists which invoices the Payment covers) are also linked.</p>
  
  <p>I would like a ViewModel to show me:</p>
  
  <p>InvoiceId
  CustomerName
  Total of Invoice (quantity X UnitPrice plus VAT)
  AmountAllocated (from the PaymentInvoice table)
  Outstanding (TotalofInvoice - AmountAllocated)</p>
  
  <p>So I think my ViewModel should be:</p>
  
  <pre><code>public class InvoiceViewModel
  {
      public Int InvoiceId { get; set; }
      public string CustomerName { get; set; }
      public decimal TotalofInvoice { get; set; }
      public decimal AmountAllocated { get; set; }
      public decimal Outstanding { get; set; }
  }
  </code></pre>
  
  <p>My domain models are:</p>
  
  <pre><code>public class Invoice
  {
  
      public int InvoiceId { get; set; }
      public int CustomerId { get; set; }
      public string CustomerName { get; set; }
      public string Email { get; set; }
      public DateTime InvDate { get; set; }
      public IList&lt;InvoiceItem&gt; InvoiceItems { get; set; }
  }
  
  public class InvoiceItem
  {
      public int InvoiceItemId { get; set; }
      public int InvoiceId { get; set; }
      public string Item { get; set; }
      public string Description { get; set; }
      public int Quantity { get; set; }
      public decimal UnitPrice { get; set; }
      public decimal VAT { get; set; }
      public virtual Invoice Invoice { get; set; }
  
      // calculated fields
      public decimal Total
      {
          get { return Quantity * UnitPrice; }
      }
      public decimal VATAmount
      {
          get { return TotalPlusVAT - Total; }
      }
      public decimal TotalPlusVAT
      {
          get { return Total * (1 + VAT / 100); }
      }
  }
  
  public class Payment
  {
      public int PaymentId { get; set; }
      public int CustomerId { get; set; }
      public DateTime DateReceived { get; set; }
      public decimal TotalReceived { get; set; }
      public IList&lt;PaymentInvoice&gt; PaymentInvoices { get; set; }
  }
  
  public class PaymentInvoice
  {
      public int PaymentInvoiceId { get; set; }
      public int PaymentId { get; set; }
      public decimal AmountAllocated { get; set; }
      public int InvoiceId { get; set; }
      public virtual Payment Payment { get; set; }
  }
  </code></pre>
  
  <p>My problem is in how to link the Payment and PaymentInvoice table to the Invoice and InvoiceItem table, so I can use a LINQ query in my controller to populate the viewmodel with the "flattened data".</p>
  
  <p>I'm lost with the LINQ query too - in LinqPad I've got:</p>
  
  <pre><code>from c in Invoices
  join i in InvoiceItems on c.InvoiceId equals i.InvoiceId
  join pi in PaymentInvoices on c.InvoiceId equals pi.InvoiceId
  select new {...into ViewModel????...}
  </code></pre>
  
  <p>...but not sure where to go after that.</p>
  
  <p><strong>EDIT</strong> - The closest I've got is the Sql to do this is:</p>
  
  <pre><code>SELECT     Invoices.InvoiceId, 
             Invoices.CustomerName, 
             (SUM(InvoiceItems.Quantity * InvoiceItems.UnitPrice)) AS TotalOfInvoice, 
             (SELECT     SUM(AmountAllocated) AS Expr1
                            FROM         PaymentInvoices
                            WHERE     (InvoiceId = Invoices.InvoiceId)) AS AmountAllocated, 
             SUM(InvoiceItems.Quantity * InvoiceItems.UnitPrice) 
             - (SELECT     SUM(AmountAllocated) AS Expr1
                            FROM         PaymentInvoices
                            WHERE     (InvoiceId = Invoices.InvoiceId)) AS Outstanding
  FROM         Invoices LEFT OUTER JOIN
                    InvoiceItems ON Invoices.InvoiceId = InvoiceItems.InvoiceId
  GROUP BY Invoices.InvoiceId, Invoices.CustomerName
  </code></pre>
  
  <p>Thank you,</p>
  
  <p>Mark</p>