LINQ aggregate left join on SQL CE

Question

What I need is such a simple, easy query, it blows me away how much work I've done just trying to do it in LINQ. In T-SQL, it would be:

SELECT I.InvoiceID, I.CustomerID, I.Amount AS AmountInvoiced,
       I.Date AS InvoiceDate, ISNULL(SUM(P.Amount), 0) AS AmountPaid,
       I.Amount - ISNULL(SUM(P.Amount), 0) AS AmountDue
FROM Invoices I
LEFT JOIN Payments P ON I.InvoiceID = P.InvoiceID
WHERE I.Date between @start and @end
GROUP BY I.InvoiceID, I.CustomerID, I.Amount, I.Date
ORDER BY AmountDue DESC

The best equivalent LINQ expression I've come up with, took me much longer to do:

var invoices = (
    from I in Invoices
    where I.Date >= start &&
          I.Date <= end
    join P in Payments on I.InvoiceID equals P.InvoiceID into payments
    select new{
        I.InvoiceID, I.CustomerID, AmountInvoiced = I.Amount, InvoiceDate = I.Date,
        AmountPaid = ((decimal?)payments.Select(P=>P.Amount).Sum()).GetValueOrDefault(),
        AmountDue = I.Amount - ((decimal?)payments.Select(P=>P.Amount).Sum()).GetValueOrDefault()
    }
).OrderByDescending(row=>row.AmountDue);

This gets an equivalent result set when run against SQL Server. Using a SQL CE database, however, changes things. The T-SQL stays almost the same. I only have to change ISNULL to COALESCE. Using the same LINQ expression, however, results in an error:

There was an error parsing the query. [ Token line number = 4,
Token line offset = 9,Token in error = SELECT ]

So we look at the generated SQL code:

SELECT [t3].[InvoiceID], [t3].[CustomerID], [t3].[Amount] AS [AmountInvoiced], [t3].[Date] AS [InvoiceDate], [t3].[value] AS [AmountPaid], [t3].[value2] AS [AmountDue]
FROM (
    SELECT [t0].[InvoiceID], [t0].[CustomerID], [t0].[Amount], [t0].[Date], COALESCE((
        SELECT SUM([t1].[Amount])
        FROM [Payments] AS [t1]
        WHERE [t0].[InvoiceID] = [t1].[InvoiceID]
        ),0) AS [value], [t0].[Amount] - (COALESCE((
        SELECT SUM([t2].[Amount])
        FROM [Payments] AS [t2]
        WHERE [t0].[InvoiceID] = [t2].[InvoiceID]
        ),0)) AS [value2]
    FROM [Invoices] AS [t0]
    ) AS [t3]
WHERE ([t3].[Date] >= @p0) AND ([t3].[Date] <= @p1)
ORDER BY [t3].[value2] DESC

Ugh! Okay, so it's ugly and inefficient when run against SQL Server, but we're not supposed to care, since it's supposed to be quicker to write, and the performance difference shouldn't be that large. But it just doesn't work against SQL CE, which apparently doesn't support subqueries within the SELECT list.

In fact, I've tried several different left join queries in LINQ, and they all seem to have the same problem. Even:

from I in Invoices
join P in Payments on I.InvoiceID equals P.InvoiceID into payments
select new{I, payments}

generates:

SELECT [t0].[InvoiceID], [t0].[CustomerID], [t0].[Amount], [t0].[Date], [t1].[InvoiceID] AS [InvoiceID2], [t1].[Amount] AS [Amount2], [t1].[Date] AS [Date2], (
    SELECT COUNT(*)
    FROM [Payments] AS [t2]
    WHERE [t0].[InvoiceID] = [t2].[InvoiceID]
    ) AS [value]
FROM [Invoices] AS [t0]
LEFT OUTER JOIN [Payments] AS [t1] ON [t0].[InvoiceID] = [t1].[InvoiceID]
ORDER BY [t0].[InvoiceID]

which also results in the error:

There was an error parsing the query. [ Token line number = 2,
Token line offset = 5,Token in error = SELECT ]

So how can I do a simple left join on a SQL CE database using LINQ? Am I wasting my time?

Answer 1

Have you tried a query expression with a group by, closer to your T-SQL version?

var invoices =
    from I in Invoices
    where I.Date >= start && I.Date <= end
    join P in Payments on I.InvoiceID equals P.InvoiceID into J
    group J.Sum(p => p.Amount) by new { I.InvoiceID, I.CustomerID, I.Amount, I.Date } into G
    let AmountPaid = G.Sum()
    let AmountDue = G.Key.Amount - AmountPaid
    orderby AmountDue descending
    select new
    {
        G.Key.InvoiceID,
        G.Key.CustomerID,
        AmountInvoiced = G.Key.Amount,
        InvoiceDate = G.Key.Date,
        AmountPaid,
        AmountDue
    };

The result looks right against in-memory collections:

var Invoices = new[] {
    new { InvoiceID = 1, CustomerID = 2, Amount = 2.5m, Date = DateTime.Today },
    new { InvoiceID = 2, CustomerID = 3, Amount = 5.5m, Date = DateTime.Today }
}.AsQueryable();
var Payments = new[] {
    new { InvoiceID = 1, Amount = 1m }
}.AsQueryable();

Yields:

{ InvoiceID = 2, CustomerID = 3, AmountInvoiced = 5.5, InvoiceDate = 8/15/2009,
  AmountPaid = 0, AmountDue = 5.5 }
{ InvoiceID = 1, CustomerID = 2, AmountInvoiced = 2.5, InvoiceDate = 8/15/2009, 
  AmountPaid = 1, AmountDue = 1.5 }

If that doesn't work, a LINQ left join typically uses DefaultIfEmpty() on the join result. You might have to do something like this instead:

var invoices =
    from I in Invoices
    where I.Date >= start && I.Date <= end
    join P in Payments on I.InvoiceID equals P.InvoiceID into J
    from PJ in J.DefaultIfEmpty() // Left Join
    group PJ by new { I.InvoiceID, I.CustomerID, I.Amount, I.Date } into G
    let AmountPaid = G.Sum(p => p == null ? 0 : p.Amount)
    // etc...