CODEWITHSUNDEEP
c#linq
gayu
gayu

Reputation: 58

LINQ query in C# using group and join

I am trying to write the following SQL query in LINQ C# but am not able to get hold of the columns after the group clause.

SQL query

/*get the number of questions by subject*/

select b.SubjectID, b.SubjectName, count(*) AS count 
from QuestionsTable a, SubjectTable b
where a.SubjectID is not null AND a.SubjectID = b.SubjectID
GROUP BY a.SubjectID

LINQ Query

var result =(from a in db1.QuestionsTables
             join b in db1.SubjectTables 
             on a.SubjectID equals b.SubjectID
             where a.SubjectID != null
             group a by a.SubjectID  into g
             select new { a.QuestionID, a.SubjectID, b.SubjectName
             }).ToList();

Upvotes: 2

Views: 151

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205849

Although it could be made the way you asked, the same result can be obtained in much simpler natural way, not involving grouping at all, like this

// get the number of questions by subject
var result =
    (from s in db1.SubjectTables
     select new
     { 
         s.SubjectID,
         s.SubjectName,
         Count = db1.QuestionsTables.Count(q => q.SubjectID == s.SubjectID)
     }).ToList();

Update: Regardless of downvotes, I strongly believe this is the right way to solve this particular problem - simple and natural. Why group something that is already grouped (by primary key).

Upvotes: 0

Mark Shevchenko
Mark Shevchenko

Reputation: 8207

Your SQL wouldn't be executed correctly cause you query groups by a.Subject.ID, but selects b.SubjectID, and b.SubjectName. Usually you should also include single selected fields to GROUP BY list.

(As far as I know, some SQL servers can process functional dependent fields, so they could process your query. But in general it's wrong).

So your working query should be:

SELECT b.SubjectID, b.SubjectName, COUNT(*) AS Count 
FROM QuestionsTable a, SubjectTable b
WHERE a.SubjectID is not null AND a.SubjectID = b.SubjectID
GROUP BY b.SubjectID, b.SubjectName

And your LINQ should be

from a in db.QuestionsTable
join b in db.SubjectTable
  on a.SubjectId equals b.SubjectId
where a.SubjectId != null
group b by new { b.SubjectId, b.SubjectName } into g
select new { g.Key.SubjectId, g.Key.SubjectName, g.Count() }

Upvotes: 4

Related Questions