Sasan Karimi Sasan Karimi - 2 months ago 11
SQL Question

how to convert left join SQL script to Linq script?

How can I convert

left join
to linq script. I have a T-SQL like this:


SELECT
es.StandardID,
COUNT(DISTINCT esc.StandardCourseID) AS CourseIDCount,
COUNT(DISTINCT esp.StandardPostID) AS PostIDCount
FROM EduStandards AS es
LEFT JOIN EduStandardCourses AS esc
ON es.StandardID = esc.StandardID
LEFT JOIN EduStandardPosts AS esp
ON es.StandardID = esp.StandardID
GROUP BY es.StandardID


That I want to convert this to linq.

Answer

Following is the query with left join that is replica of your query in linq.

var query = (from es in dbContext.EduStandards
                    join esc in dbContext.EduStandardCourses on es.StandardID equals esc.StandardID into ssc
                    from esc in ssc.DefaultIfEmpty()
                    join esp in dbContext.EduStandardPosts on es.StandardID equals esp.StandardID into ssp
                    from esp in ssp.DefaultIfEmpty()
                    select new { StandardId = es.StandardID, CourseCount = ssc.Count(), PostCount = ssp.Count() }).Distinct().ToList();

But I think we need not to apply left join in linq to calculate count. Following optimized linq query will return same result.

var query2 = (from es in dbContext.EduStandards
                     join esc in dbContext.EduStandardCourses on es.StandardID equals esc.StandardID into ssc
                     join esp in dbContext.EduStandardPosts on es.StandardID equals esp.StandardID into ssp
                     select new { StandardId = es.StandardID, CourseCount = ssc.Count(), PostCount = ssp.Count() });
Comments