LINQ Query for particular situation

Question

Lets say that I have following tables:

 Student(id(pk), name)
 Class(id(pk), name)
 StudentClass(id(pk), studentId(fk), classId(fk))

Imagine as follows:

Student table contains:

 (1,"John"), (2, "Mike"), (3,"Josh")

Class table contains:

(1,"Geography"), (2, "Math")

StudentClass table contains:

(`1, 1, 1),(2,2,2),(3,3,2)

Lets now assume that I have a StudentClassDTO class which contains

List<string> StudentNames
string ClassName

How can I by using using LINQ query get data into StudentClassDTO? Any help appreciated.

    var data = from sc in context.GetStudentClasses
               join s in context.GetStudents on sc.StudentId equals s.Id
               join c in context.GetClass on sc.ClassId equals c.Id
               select new StudentClassDTO
               {

               }

so it gets name and classname 3 seperate ones but I need if their classes are same it should have to combine them where it will be just one classname and 2 different students. So it should be like {john, Geography} and {[Mike, Josh], Math}

Solution

                   from c in classes
                   join sc in studentClasses on c.Id equals sc.ClassId
                   join s in student on sc.StudentId equals s.StudentId 
                   group s by new {c.Name} into g
                   select new StudentClassDTO
                   {
                       ClassName = g.Key.Name,
                       StudentNames = g.Select(a=>a.Name).ToList()
                   };

Have you tried googling a tutorial? There are many out there which will teach you in great detail how to perform this task. If you suffer problems you can come back and us. — user1666620, Jan 17 '19 at 18:59
What specifically are you struggling with here? Mapping the many-to-many relationship on your model? Finding a Select expression to get the results? Can you share the code you've tried and tell in what way it's failing to do what you're asking? — StriplingWarrior, Jan 17 '19 at 19:03
I actually tried joins but in this case I am only able to get datas to StudentClassDTO like this so it gets name and classname 3 seperate ones but I need if their classes are same it should have to combine them where it will be just one classname and 2 differen students — , Jan 17 '19 at 19:05
@Kakos649 the time you took to write this symbolic content, by this time you could have written the real code and sample data. — TanvirArjel, Jan 17 '19 at 19:07
so it should be lke {john, Geography} and {[Mike, Josh], Math} — , Jan 17 '19 at 19:07
LINQ has a special (non-SQL) operation for this - group join. Use an `into` clause on the Students `join` and then put that in the DTO. — NetMage, Jan 17 '19 at 19:14

score 0 · Answer 1 · answered Jan 17 '19 at 19:13

I use code like this all the time to accomplish what you're trying to do (untested and using the C# 7.3 syntax).

var xs =
    from s in ctx.students 
        join cs in ctx.student_classes on cs.student_id equals s.student_id 
        join c in ctx.classes on c.class_id equals cs.class_id

    select new 
    {
        s, c
    }

var memo = new Dictionary<int, StudentClassDTO>(); //the key is class_id

foreach(var x in xs)
{
    if(!memo.Contains(x.c.class_id, out var @class))
        memo.Add(x.c.class_id, @class = new StudentClassDTO(x.c.class_name));

    @class.Accept(s.student_name);
}


sealed class StudentClassDTO
{
    readonly List<string> student_names;

    public string ClassName { get; }

    public IEnumerable<string> StudentNames => student_names;       

    public(string class_name)
    {
        ClassName = class_name;
    }

    public void Accept(string name) => student_names.Add(name);     
}

Thanks for the answer, but I am trying to accomplish it without extra functions by just LINQ query — , Jan 17 '19 at 19:16
@Kakos649 -- Take a look at [this answer](https://stackoverflow.com/questions/7325278/group-by-in-linq). — Rodrick Chapman, Jan 17 '19 at 19:20

NetMage · Answer 2 · 2019-01-21T19:00:43.720

0

Using the LINQ group join operator, you can get a collection of matching student classes, however your query needs to start from the Class table since you want one StudentClassDTO per class. Unfortunately you have to nest the join from student classes to students (an EF navigation property may do better) so this may generate multiple queries.

var data = from c in context.GetClass
           join sc in context.GetStudentClasses on c.Id equals sc.ClassId into scj
           select new StudentClassDTO {
               ClassName = c.Name,
               StudentNames = (from sc in scj
                               join s in context.GetStudents on sc.StudentId equals s.Id
                               select s.Name).ToList()
           };

edited Jan 21 '19 at 19:00

answered Jan 17 '19 at 20:06

NetMage

26,163
3
34
55

@Kakos649 I fixed the query, but it may generate multiple SQL queries to get the answer, I can't test that. – NetMage Jan 21 '19 at 19:01
Already added solution you can check it out but thanks for the comment – Jan 21 '19 at 19:07
@Kakos649 Depending on SQL engine (and LINQ provider), join may be better than group by. Or worse. – NetMage Jan 21 '19 at 19:42

LINQ Query for particular situation

2 Answers2