Viewmodel and LINQ query to represent 4 related tables in Asp.Net MVC

Question

I use a typical account system as a test to develop knowledge of MVC and ViewModel before solving the problem of migrating my internal employer systems from asp to asp.net MVC.

I know that ViewModels is the recommended way to display information in a view - so I was hoping for some help to “smooth out” the view model for the following:

tables: invoice, invoice, payment, payment-transfer

Accounts and InvoiceItem are linked, and payment (which records the total payment) and PaymentInvoice (which lists the accounts to which payment applies) are also linked.

I would like the ViewModel to show me:

InvoiceID Customer Name Total Account (amount X UnitPrice plus VAT) AmountAllocated (from the PaymentInvoice table) Outstanding (TotalofInvoice - AmountAllocated)

So, I think my ViewModel should be:

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; } } 

My domain models:

 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<InvoiceItem> 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<PaymentInvoice> 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; } } 

My problem is how to link the Payment and PaymentInvoice table with the Invoice and InvoiceItem table, so I can use the LINQ query in my controller to populate the viewmodel with "flattened data".

I also lost the LINQ query - in LinqPad I have:

 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????...} 

... but not sure where to go after that.

EDIT . The closest I got is Sql to do this:

 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 

Thanks,

Mark

Answer 1

I think your Linq query is almost perfect, you just need to select the new ViewModels:

 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 InvoiceViewModel { InvoiceId = c.InvoiceId, CustomerName = c.CustomerName, TotalofInvoice = c.InvoiceItems.Sum(invoiceitem => invoiceitem.Total(), AmountAllocated = ... Outstanding = ... };