Convert SQL query into linq C#

Question

Convert SQL query to LINQ C#

SELECT   ts.TeacherId, count(Distinct ts.SubjectId) as Subjects
from dbo.TeacherSubjects ts
GROUP BY ts.TeacherId
HAVING ts.TeacherId  = 2352

here is my LINQ query

var SubjectsGroup = db.TeacherSubjects.Where(p => p.TeacherId == 2352).Distinct().GroupBy(x => x.TeacherId);

Step 1: That HAVING condition should be in the WHERE clause instead. — jarlh, Jun 11 '19 at 19:01
Please never just post SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. Also, tell what type of LINQ you're targeting (to entities?), and show your own first efforts so we see where *specifically* you need help. The best LINQ query is hardly ever a 1:1 reproduction of a SQL query. — Gert Arnold, Jun 11 '19 at 19:03
I think you should google a tutorial on LINQ and figure this out for yourself (it'll be better for you in the long run) It's a really simple LINQ query, dude. Simple enough for you to learn on your own but too simple for us to do it for you. — jPhizzle, Jun 11 '19 at 19:06
Here is my LINQ query var SubjectsGroup = db.TeacherSubjects.Where(p => p.TeacherId == 2352).Distinct().GroupBy(x => x.TeacherId); — Jon, Jun 11 '19 at 19:09
or try this one too: var subjectGroups = from p in db.TeacherSubjects where p.TeacherId = 2352 group p by p.TeacherId into ts select new { TeacherId = ts.TeacherId, Subjects = ts.SubjectId.Count() } — jPhizzle, Jun 11 '19 at 19:12
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jun 11 '19 at 19:48
First of all Thanks to all after spending couple of hours i finally found the solution Here is the LINQ Query . ` var teacherSubjects = db.TeacherSubjects.Where(x => x.TeacherId == 2352).ToList(); var grouped = teacherSubjects .GroupBy(x => x.TeacherId) .Select(group => new { teacherId = group.Key, Subjects = group.**Distinct(new SubjectIdComparer())** });` The core part is to implement the Comparer to find the distinct elements — Jon, Jun 12 '19 at 15:00
** var teacherSubjects = db.TeacherSubjects.Where(x => x.TeacherId == 2352).ToList(); var grouped = teacherSubjects .GroupBy(x => x.TeacherId) .Select(group => new { teacherId = group.Key, Subjects = group.Distinct(new SubjectIdComparer()) });** — Jon, Jun 12 '19 at 15:03

Dmitry Stepanov · Accepted Answer · 2019-06-11T19:44:00.160

var teacherId = ;  // your teacherId here

var result = new {
    TeacherId = teacherId,
    Subjects = db.TeacherSubjects.Where(ts => ts.TeacherId == teacherId).Select(x => x.SubjectId).Distinct().Count()
};

Or you can group by SubjectId:

var teacherId = ;  // put your teacherId here
var result = 
    (from ts in TeacherSubjects.Where(x => x.TeacherId == teacherId)
    group ts by ts.SubjectId into gr
    select new 
    {       
        TeacherId = teacherId,
        Subjects = gr.Count()
    }).ToList();

Convert SQL query into linq C#

1 Answers1