How to convert this SQL Query to LINQ Query?

Question

I have the following SQL statement

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)

This query is same result

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

Can anyone please help me to convert this statement to LINQ?

Thank you.

Answer 1

The simple answer is you do not, at least in a performant way assuming your using Entity Framework or a similar ORM.

This post describes how to handle subqueries in LINQ. As far as I am aware there is no possible way to do this in "semantic" LINQ. The problem with the method suggested in that post is that subqueries are not executed together with the main query and the performance is a nightmare.

I've always handled this situation by writing a stored procedure in T-SQL and mapping that through EF.

Answer 2

Your SQL query is really very difficult that I cannot give you a whole solution. I had to give you some idea to split this complicated SQL query into small one that can be handled easily.

You have three parts, I simplified them as D, T_DIZ , T_OMU .

And then I have to simplified some them separately. And you should better consider the Groupby, the Coalesce, LEFT OUTER JOIN after you have a scheme picture of the query.

Respectively, we have three queries, as 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().

There is still some little drawback in the solution for time`s reason. Hope this will be helpful to you.