CODEWITHSUNDEEP
c#linq
Umair
Umair

Reputation: 31

Combine 2 LINQ queries

I am trying to join 2 LINQ queries. But I am not getting result according to my desire.

List<TeacherSubjectVM> query = (from t in uow.staffs
                                join ts in uow.teachersubjects on t.ID equals ts.teacherID
                                join s in uow.subjects on ts.subjectID equals s.ID
                                select new TeacherSubjectVM
                                {
                                    subjectName = s.Name,
                                    teacherName = t.fname,
                                }).ToList();

List<TeacherSubjectVM> query1 = (from t in uow.subjects
                                 join ts in uow.classsubjects on t.ID equals ts.subjectID
                                 join s in uow.jamats on ts.subjectID equals s.ID
                                 select new TeacherSubjectVM
                                 {
                                     section = s.section,
                                     className = s.name,
                                 }).ToList();

List<TeacherSubjectVM> combine = query.Concat(query1).ToList();

Class name and section should be in the same row of subject name and teacher name:

Upvotes: 3

Views: 96

Answers (2)

Ian Mercer
Ian Mercer

Reputation: 39297

Simply join all the tables you want to join in a single step:

List<TeacherSubjectVM> query = (from t in uow.staffs
                            join ts in uow.teachersubjects on t.ID equals ts.teacherID
                            join s in uow.subjects on ts.subjectID equals s.ID
                            join cs in uow.classsubjects on ts.subjectID equals cs.ID
                            join j in uow.jamats on ts.subjectID equals j.ID
                            select new TeacherSubjectVM
                            {
                                subjectName = s.Name,
                                teacherName = t.fname,
                                section = cs.section,
                                className = j.name,
                             }).ToList();

I think that's what you are trying to do but your inconsistent variable naming and reuse of variable names makes it really hard to read.

Upvotes: 1

Tha&#39;er AlAjlouni ثائر العجلوني
Tha&#39;er AlAjlouni ثائر العجلوني

Reputation: 15457

The approach you're taking will not give the desired because the Concat function will not merge the objects, it will only append all items of the query1 to query in a new list, here combine.

The of reaching your desire is to loop through them and manually merge objects, something like the following:

List<TeacherSubjectVM> query = (from t in uow.staffs
                                join ts in uow.teachersubjects on t.ID equals ts.teacherID
                                join s in uow.subjects on ts.subjectID equals s.ID
                                select new TeacherSubjectVM
                                {
                                    Id = ts.subjectID,
                                    subjectName = s.Name,
                                    teacherName = t.fname,
                                }).ToList();

List<TeacherSubjectVM> query1 = (from t in uow.subjects
                                 join ts in uow.classsubjects on t.ID equals ts.subjectID
                                 join s in uow.jamats on ts.subjectID equals s.ID
                                 select new TeacherSubjectVM
                                 {
                                     Id = ts.subjectID,
                                     section = s.section,
                                     className = s.name,
                                 }).ToList();

query.ForEach(item => {
    var otherItem = query1.Where(itm => itm.Id == item.Id).FirstOrDefault();

    item.subjectName = otherItem?.subjectName;
    item.teacherName = otherItem?.teacherName;
});

Note that I added the Id property to the TeacherSubjectVM class so that the merge can be done regarding it, it should be filled with the subject id since it is the only common thing between the two objects.

PS: It is always a better practice to use descriptive variable names, that will help enhancing the readability and maintainability of your code :)

Upvotes: 0

Related Questions