转换相关和连接SQL查询到LINQ

本文关键字：查询 LINQ SQL 连接 转换 | 更新日期: 2023-09-27 18:01:31

请考虑这个查询:

SELECT DISTINCT(fld_09 + fld_10), fld_Switch, fld_Area
FROM   Mytbl t
WHERE  fld_Year = 2000
   AND fld_Month = 1
   AND fld_02 != '04'
   AND fld_Switch = 2
   AND (
           (fld_09 + fld_10) NOT IN 
                (SELECT DISTINCT(fld_09 + fld_10)
                 FROM  Mytbl q
                 WHERE  fld_Year = 2000
                 AND fld_Month = 1
                 AND fld_02 != '04'
                 AND fld_Switch = 1
                 AND t.fld_Area = q.fld_Area)
       )
GROUP BY fld_Area, fld_09, fld_10, fld_Switch

这个查询在1秒内执行。我想用linq来写这个查询。我写了下面的代码:

var subQuery= (from r in ent.Mytbl 
               where r.fld_Year == Year &&
                     r.fld_Month == Month &&
                     r.fld_Switch == 1 &&
                     r.fld_02 != "04"
               select r.fld_09 + r.fld_10;
var query = (from o in ent.Mytbl 
             let code = o.fld_09 + o.fld_10
             where o.fld_Year == Year &&
                   o.fld_Month == Month &&
                   o.fld_Switch == 2 &&
                   o.fld_F02 != "04" &&
                   subQuery.Contains(code) == false
             group o by new { o.fld_Area, o.fld_09, o.fld_10, o.fld_Switch }
             into gr
             select new
             {
                 a = gr.Key.fld_09 + gr.Key.fld_10,
                 gr.Key.fld_Area,c=gr.Count()
             }).Distinct();

这两个查询是否相等?

第二个查询在执行时花费2分钟。我错在哪里?

转换相关和连接SQL查询到LINQ

如果还没有，请确保使用LinqPad来调整这个查询。我认为let条款是你的业绩打击。

相关文章：