sql - 在 Linq to SQL 上是否可以实现干净的 SQL？

Question

在 Linq to Sql 中是否可以实现干净的(或者我应该说是高性能的)SQL？

我想让 Linq to Sql 生成这段代码:

SELECT C.CustomerID, COUNT(O.CustomerID) AS N
FROM Customers C
LEFT JOIN Orders O ON O.CustomerID = C.CustomerID
GROUP BY C.CustomerID

我遵循以下代码:LINQ - Left Join, Group By, and Count

所以这是我的代码版本:

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) };

但是它生成了这个...

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]

...我觉得这是 Not Acceptable 。

然后我试试这个...

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 )};

...这是结果查询:

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]

虽然更简洁并且看起来更高效，但与更简单的语句相比仍然不够简洁和高效:COUNT(O.CustomerID)

我正在尝试做的事情在 Linq to SQL 中是否可行？

其他ORM怎么样？尤其是 NHibernate，它能将 HQL 语句转换为真正的 SQL 吗？

Answer 1

我认为您通常必须忍受 LINQ to SQL 生成的内容，但希望 LINQ to SQL 允许您完全忽略 SQL(大部分时间)- 我发现这是一个有值(value)的权衡。

对于复杂的报表，我通常会放弃 LINQ to SQL 并编写纯 SQL，尤其是当查询涉及大量特定于数据库的 UDF's 时。之类的。