linq-to-sql - Linq 到 SQL : Left Join to MAX aggregate

Question

我正在尝试编写一个 Linq to SQL 语句，该语句显示所有客户记录和发票表的匹配 max(InvoiceId)；基本上是客户的最新发票。左联接是必需的，因为客户可能没有任何发票但需要在结果集中。

外键为 Customer.CustomerID = Invoice.CustomerId 的两个基本表

CREATE TABLE [dbo].[Customer](
    [CusomerId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [int] NOT NULL
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
    [CustomerId] ASC
)
) ON [PRIMARY]

CREATE TABLE [dbo].[Invoice](
    [InvoiceId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL,
    [InvoiceTotal] [float] NOT NULL
    CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED 
(
    [InvoiceId] ASC
)
) ON [PRIMARY]

想要的结果集的SQL如下:

SELECT *
FROM Customers c
  LEFT JOIN 
    (Invoice i 
      INNER JOIN (SELECT CustomerId, MAX(InvoiceId) as InvId FROM Invoice GROUP BY CustomerId) as InvList 
      ON i.InvoiceNo = InvList.InvoiceNo) ON c.CustomerId = i.CustomerId

根据我的发现，我认为这不能在一个语句中完成；需要首先创建 MAX(InvoiceId) 产品并在主语句中使用。由于我无法让它工作，也许我也错了。

Answer 1

您可以按如下方式在 LINQ 中编写此特定查询——尽管这将导致相关子查询:

var query = 
    from c in ctx.Customer
    select new
    {
        Customer = c,
        LatestInvoice = ctx.Invoice
            .Where(i => i.CustomerId == c.CustomerId)
            .OrderByDescending(i => i.InvoiceId)
            .FirstOrDefault();
    };

如果您想以另一种方式执行此操作，LINQ 语法的可读性较差，但由于延迟执行，您可以将查询拆分一点:

var latestInvoicesPerCustomerQuery = 
    from inv in ctx.Invoice
    group inv by inv.CustomerId into g
    select new { CustomerId = g.Key, InvoiceId = g.Max(inv => inv.InvoiceId) };

var customersAndLatestInvoicesQuery = 
    from customer in ctx.Customer
    join linv in latestInvoicesPerCustomer 
         on customer.CustomerId equals linv.CustomerId
         into latestInvoiceJoin
    from latestInvoice in latestInvoiceJoin.DefaultIfEmpty() // left join
    join invoice in ctx.Invoice 
         on latestInvoice.InvoiceId equals invoice.InvoiceId
    select new
    {
        Customer = customer,
        LatestInvoice = invoice
    };

第一个查询 ( latestInvoicesPerCustomerQuery ) 在您枚举它或引用第一个查询的第二个查询之前不会执行。就运行时而言，最终查询是一个表达式树——因此您可以认为第一个查询已被吸收到第二个查询中。

如果您真的想在一个查询中完成所有操作，您也可以这样做:

var customersAndLatestInvoicesQuery = 
    from customer in ctx.Customer
    join linv in (
            from inv in ctx.Invoice
            group inv by inv.CustomerId into g
            select new 
            { 
                CustomerId = g.Key, 
                InvoiceId = g.Max(inv => inv.InvoiceId) 
            }
        ) 
        on customer.CustomerId equals linv.CustomerId
        into latestInvoiceJoin
    from latestInvoice in latestInvoiceJoin.DefaultIfEmpty() // left join
    join invoice in ctx.Invoice 
         on latestInvoice.InvoiceId equals invoice.InvoiceId
    select new
    {
        Customer = customer,
        LatestInvoice = invoice
    };

customersAndLatestInvoicesQuery 的任一变体应该大致翻译成您在帖子中列出的 SQL。