将嵌套总和的sql查询转换为linq2sql

Question

我试图将此SQL转换为Linq2SQL查询，但是，我只是将存储过程拖到dbml中，希望有人可以做得更好。当没有嵌套查询的记录时，它将返回null。

SQL：

SELECT 
    Table1.Field1 - 
    ISNULL(
      (
      SELECT 
       SUM(Table2.Field1) 
      FROM Table2 
       INNER JOIN Table3 ON Table2.ID = Table3.Table2ID 
      WHERE Table3.Table1ID = Table1.ID 
      ) 
      ,0) 
FROM 
    Table1 
WHERE 
    (Table1.ID = @ID) 

LINQ2SQL

 return (from q in db.Table1s 
        where q.ID == id 
        select q.Field1.GetValueOrDefault() - 
          (from o in db.Table2s 
          join r in db.Table3s on o.ID equals r.Table2ID.GetValueOrDefault(0) 
          where r.Table1ID == q.ID 
          select Convert.ToInt32(o.Field1.GetValueOrDefault(0))).Sum() 
        ).SingleOrDefault() 

任何人都可以做的更好。

Answer 1

简明版的使用方法的语法和lambda表达式：

from t1 in Table1s 
select t1.Field1 - Table3s.Where(x=>x.Table1ID == t1.ID).Sum(y=>y.Table2.Field1) 

更可读的版本使用的查询语法使用let声明：

from t1 in Table1s 
let tmp = 
(
    from t3 in Table3s 
    where t3.Table1ID == t1.ID 
    select t3.Table2.Field1 
).Sum() 
select t1.Field1 - tmp 

我强烈建议使用像LinqPad这样的工具来设计查询，因为它会显示生成的SQL。这可以让您调整更昂贵的查询以获得更好的结果。

它对快速建立原型和测试短代码块也很有用。

Answer 2

这可能有所帮助： 我会用另一种方式来制定sql。就像这样：

SELECT 
    Table1.Field1 - ISNULL(tblTemp.SumOfField1,0) 
FROM 
    Table1 
    LEFT JOIN 
     (
      SELECT 
       SUM(Table2.Field1) AS SumOfField1, 
       Table3.Table1ID 
      FROM Table2 
       INNER JOIN Table3 ON Table2.ID = Table3.Table2ID 
      GROUP BY 
       Table3.Table1ID 
     ) AS tblTemp 
     ON tblTemp.Table1ID = Table1.ID 
WHERE 
    Table1.ID = @ID 

然后你的LINQ代码可以是这样的（同样的结果看它只是另一种方式）：

var leftJoin=(
        from Table2 in db.Table2 
        join Table3 in db.Table3 
         on Table2.ID equals Table3.Table2ID 
        group Table3 by Table3.Table1ID into g 
        select new 
        { 
         Table1ID=g.Key, 
         SumOf= g.Sum (x =>x.Field1) 
        } 
       ); 
var output=(
     from Table1 in db.Table1 
     from g in leftJoin 
      .Where (a =>a.Table1ID==Table1.ID).DefaultIfEmpty() 
     select new 
     { 
      Table1.ID, 
      SumOf=(g.SumOf??0)-Table1.Field1 
     } 
    );