LINQ aggregate left join on SQL CE
- by P Daddy
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?
