asp.net-mvc - Viewmodel 和 LINQ 查询表示 Asp.Net MVC 中的 4 个链接表

Question

在解决将我的雇主内部系统从 ASP 迁移到 ASP.NET MVC 之前，我使用典型的发票系统作为开发 MVC 和 ViewModel 知识的测试。

我知道 ViewModel 是在 View 中显示信息的推荐方式 - 因此我希望获得一些帮助“扁平化” View 模型，以实现以下目的:

表格:发票、发票项目、付款、付款发票

Invoice 和 InvoiceItem 已链接，Payment(记录总付款)和 PaymentInvoice(列出 Payment 涵盖的发票)也链接。

我想要一个 ViewModel 来向我展示:

发票编号顾客姓名发票总计(数量 X 单价加增值税)已分配金额(来自 PaymentInvoice 表)未偿(发票总计 - 分配金额)

所以我认为我的 ViewModel 应该是:

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

我的域模型是:

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

我的问题是如何将 Payment 和 PaymentInvoice 表链接到 Invoice 和 InvoiceItem 表，因此我可以在 Controller 中使用 LINQ 查询来使用“扁平化数据”填充 View 模型。

我也迷失了 LINQ 查询 - 在 LinqPad 中我得到了:

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

...但不知道之后该去哪里。

编辑 - 我得到的最接近的是执行此操作的 Sql 是:

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

谢谢，

标记

Answer 1

我认为您的 Linq 查询几乎是完美的，您只需要选择新的 ViewModel:

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