使用Linq模拟带有链接记录计数的SQL查询

本文关键字：SQL 查询 记录 链接 Linq 模拟 使用 | 更新日期: 2023-09-27 17:49:35

在SQL中，我会这样做:

    SELECT SVSSurvey_Level.ID, SVSSurvey_Level.SurveyID,
           SVSSurvey_Level.UserCode, SVSSurvey_Level.ExternalRef,
           SVSSurvey_Level.Description, SVSSurvey_Level.ParentLevelID,
           SVSSurvey_Level.LevelSequence, SVSSurvey_Level.Active, 
           COUNT(SVSSurvey_Question.ID) AS Questions
    FROM SVSSurvey_Level LEFT OUTER JOIN
         SVSSurvey_Question ON SVSSurvey_Level.ID = SVSSurvey_Question.LevelID
    GROUP BY SVSSurvey_Level.ID, SVSSurvey_Level.SurveyID,
             SVSSurvey_Level.UserCode, SVSSurvey_Level.ExternalRef,
             SVSSurvey_Level.Description, SVSSurvey_Level.ParentLevelID,
             SVSSurvey_Level.LevelSequence, SVSSurvey_Level.Active

在一个相关的Linq查询中，我这样写:

var levels = (from l in dataContext.SVSSurvey_Levels 
              where l.SurveyID == intSurveyId
              orderby l.LevelSequence 
              select new Level
              {
                  Id = l.ID,
                  SurveyId = l.SurveyID,
                  UserCode = l.UserCode ,
                  ExternalRef = l.ExternalRef ,
                  Description = l.Description ,
                  ParentLevelId = (l.ParentLevelID),
                  LevelSequence = ( l.LevelSequence ),
                  Active = Convert .ToBoolean( l.Active )
              });

如何添加类似的链接记录计数(相当于上面示例中的COUNT(SVSSurvey_Question.ID) AS Questions)?

使用Linq模拟带有链接记录计数的SQL查询

难道这还不够吗?假设QuestionCount是关卡中的一个属性，并且你已经在模型中正确设置了导航属性。

QuestionCount = l.SVSurvey_Questions.Count(),

您可以在"Questions"表上执行左连接，然后计算所有非null的内容:

var levels = (from l in dataContext.SVSSurvey_Levels
              join q in dataContext.SVSSurvey_Questions on l.ID equals q.LevelID into grp
              from qq in grp.DefaultIfEmpty()
              where l.SurveyID == intSurveyId
              orderby l.LevelSequence 
              select new Level
              {
                  ...
                  ...
                  QuestionCount = (from qq where qq != null select qq).Count()
                  ...
              });
相关文章：