title: Linq to SQL: Left Join to MAX aggregate
body: <p>I am trying to write a Linq to SQL statement which displays all customer records and only the matching max(InvoiceId) of the invoice table; basically the newest invoice for the customer.  The left join is required because a customer may not have any invoices but need to be in result set.</p>
  
  <p>Two basic tables with a foreign key of Customer.CustomerID = Invoice.CustomerId</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>The SQL of the desired result set is as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>From what I have discovered, I don't think this can be done in a single statement; that the MAX(InvoiceId) product needs to be created first and used in the main statement.  Since I can't get it to work, perhaps I am wrong about that too.</p>