CODEWITHSUNDEEP
c#sqllinq
Steve Staple
Steve Staple

Reputation: 3289

Emulating an SQL Query with Count of linked records using Linq

In SQL, I would do something like:

    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

In a related Linq query, I have this:

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

How would I add a similar count of linked records (equivalent to COUNT(SVSSurvey_Question.ID) AS Questions in above example)?

Upvotes: 1

Views: 77

Answers (2)

Janne Matikainen
Janne Matikainen

Reputation: 5121

Shouldn't this be enough? Assuming QuestionCount is a property in your Level and you have navigation properties set up properly in your model.

QuestionCount = l.SVSurvey_Questions.Count(),

Upvotes: 1

Grant Winney
Grant Winney

Reputation: 66489

You could do a left join on the "Questions" table, then count everything that is not 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()
                  ...
              });

Upvotes: 0

Related Questions