如何将此SQL查询转换为LINQ查询？

Question

我有以下SQL语句

SELECT D.*, COALESCE(T_DIZ.adet, 0) AS DIZ, COALESCE(T_OMU.adet, 0) as OMUZ
FROM

(SELECT A.DOK,COUNT(DISTINCT GNL) AS toplam FROM CKS A GROUP BY A.DOK ) AS D

LEFT OUTER JOIN (
    SELECT DOK, ATUR,count(DISTINCT HST)as Adet, COUNT(DISTINCT GNL) AS adet FROM CKS
    WHERE ATUR LIKE '%DIZ%'
    GROUP BY DOK, ATUR  
) T_DIZ ON(T_DIZ.DOK = D.DOK)

LEFT OUTER JOIN (
    SELECT DOK, ATUR,count(DISTINCT HST)as Adet, COUNT(DISTINCT GNL) AS adet FROM CKS
    WHERE ATUR LIKE '%OMU%'
    GROUP BY DOK, ATUR  
) T_OMU ON(T_OMU.DOK = D.DOK)

此查询的结果相同

SELECT DISTINCT(DOK), (COUNT(DISTINCT GNL) ) AS TOP,
 (CASE WHEN ATUR LIKE '%DIZ%' THEN COUNT(DISTINCT GNL) ELSE 0 END) AS DIZ,
 (CASE WHEN ATUR LIKE '%OMU%' THEN COUNT(DISTINCT GNL) ELSE 0 END) AS OMU
FROM S_GC_UST
WHERE GC = 'C'
GROUP BY DOK, ATUR, TRH

有人能帮我把这句话转换成LINQ吗？

谢谢。

Answer 1

简单的答案是你不需要，至少在你使用实体框架或类似的ORM的情况下是这样的。

This post描述了如何在LINQ子查询。据我所知，在“语义”LINQ中没有可能做到这一点。这篇文章中提出的方法的问题是，子查询没有与主查询一起执行，性能是一场噩梦。

我总是通过用T-SQL编写一个存储过程并通过EF映射它来处理这种情况。

Answer 2

你的SQL查询真的很难，我不能给你一个完整的解决方案。我必须给您一些想法，将这个复杂的SQL查询拆分成易于处理的小查询。

你有三个部分，我把它们简化为D，T_DIZ，T_OMU。

然后我必须分别对它们进行简化。在您有了查询的方案图片之后，您应该更好地考虑Groupby、合并、左外部连接。

我们分别有三个查询，分别是query1、query2、query3。

query1 = from a in CKS Group a by Key DOK into Group
         select DOK = Group.DOK, 
                TOPLAM = Group.Select(x => x.GNL).Distinct().Count()

query2 = from b in CKS 
         where SqlMethods.Like(b.ATUR, "%DIZ%")
         Group b by new Key {DOK, ATUR } into Group 
         select DOK  = Group.DOK, 
                ATUR = Group.ATUR,
                Adet = Group.Select(x => x.HST).Distinct().Count(),
                adet = Group.Select(x => x.GNL).Distinct().Count()

//the same way of query2, you can get query3. 

//Then you can use a query23 to unit all of query2 and query3 
query23 = (from x in query2 select new {DIZ= x.A, OMUZ = 0})
          .Concat( from y in query3  select new {DIZ = 0, DIZ = y.B} );

//At last, you can put query1 and query23 together.
query123 = from e in query1  
           join f in query23 
           on e.DOK equals f.DOK  
           into query
           from g in query.DefaultIfEmpty()
           select new { toplam = g.toplam , 
                        DIZ= g==null ? 0:g.DIZ ,
                        OMUZ = g==null?0:g.OMUZ   };

//and then 
var result = query123.ToList().

由于时间的原因，该解决方案仍然存在一些小缺点。希望这能对你有所帮助。