如何将左连接SQL脚本转换为Linq脚本？

问题描述：

如何将left join转换为linq脚本。我有一个T-SQL这样的：
如何将左连接SQL脚本转换为Linq脚本？

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

，我想这个转换为LINQ。

答

以下是左连接的查询，它是您在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();

但我认为，我们需要不适用左连接在LINQ to计算计数。优化后的linq查询将返回相同的结果。

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() });

答

这是我想出来的

var query = from es in db.EduStandards 
      join esc1 in db.EduStandardCourses 
      on es.StandardId equals esc1.StandardId into esc 
      from c in esc.DefaultIfEmpty() 
      join esp1 in db.EduStandardPosts 
      on es.StandardId equals esp1.StandardId into esp 
      from p in esp.DefaultIfEmpty() 
      group new { es.StandardId, Course = c, Post = p } by es.StandardId into g 
      select new 
      { 
       StandardId = g.Key, 
       CourseIdCount = g.Where(x => x.Course != null).Count(), 
       PostIdCount = g.Where(x => x.Post != null).Count(), 
      };

不过，我不能完全肯定是否会为EF工作。

你总是可以做这样的事情：

var query = from es in db.EduStandards 
      select new 
      { 
       es.StandardId, 
       CourseIdCount = db.EduStandardCourses.Where(esc => esc.StandardId == es.StandardId).Distinct().Count(), 
       PostIdCount = db.EduStandardPosts.Where(esp => esp.StandardId == es.StandardId).Distinct().Count() 
      };

另外，我无法证实这些查询的任何一个表现，由于缺乏对数据库的了解的。

如何将左连接SQL脚本转换为Linq脚本？

相关推荐