Is clean (or should I say performant) SQL achievable in Linq to Sql?
I wanted the Linq to Sql produce this code:
SELECT C.CustomerID, COUNT(O.CustomerID) AS N
FROM Customers C
LEFT JOIN Orders O ON O.CustomerID = C.CustomerID
GROUP BY C.CustomerID
And I follow this code: LINQ - Left Join, Group By, and Count
So here's my version of code:
var q = from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into sr
from x in sr.DefaultIfEmpty()
group x by c.CustomerID into y
select new { y.Key, N = y.Count(t => t.CustomerID != null) };
But it generate this...
SELECT [t2].[CustomerID] AS [Key], (
SELECT COUNT(*)
FROM [Customers] AS [t3]
LEFT OUTER JOIN [Orders] AS [t4] ON [t3].[CustomerID] = [t4].[CustomerID]
WHERE ([t4].[CustomerID] IS NOT NULL) AND ((([t2].[CustomerID] IS NULL) AND ([t3].[CustomerID] IS NULL)) OR (([t2].[CustomerID] IS NOT NULL) AND ([t3].[CustomerID] IS NOT NULL) AND ([t2].[CustomerID] = [t3].[CustomerID])))
) AS [N]
FROM (
SELECT [t0].[CustomerID]
FROM [Customers] AS [t0]
LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
GROUP BY [t0].[CustomerID]
) AS [t2]
...Which I find unacceptable.
Then I try this...
var q = from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into sr
from x in sr.DefaultIfEmpty()
group x by c.CustomerID into y
select new { y.Key, N = y.Sum(t => t.CustomerID != null ? 1 : 0 )};
...and here is the resulting query:
SELECT SUM(
(CASE
WHEN [t1].[CustomerID] IS NOT NULL THEN @p0
ELSE @p1
END)) AS [N], [t0].[CustomerID] AS [Key]
FROM [Customers] AS [t0]
LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
GROUP BY [t0].[CustomerID]
Though a little cleaner and look performant, but still not as succint and performant compared to simpler statement: COUNT(O.CustomerID)
Is what I'm trying to do, possible in Linq to SQL?
How about other ORM? notably NHibernate, can it translate the HQL statement to its real SQL?
