Inner join in C# with lambda

Question

I'm new to LINQ and Lambda. I want to make query that will get me for every studyYear (1,2,3) students who are studying that year. I've done it without Lambda but I really want to know how to do it with lambda.

            var res = from s in db.student
                  join u in db.EnrolledYear
                  on s.ID equals u.studentID
                  join g in db.studyYear
                  on u.studyYearID equals g.ID
                  select new
                  {
                      Year = g.year,
                      StudentFName = s.FirstName
                      StudentLName = s.LastName
                  };

I checked out other examples with lambda but I didn't really understand . What I managed is to make inner join between student and enrolled year. Now I don't understand how to finish inner join between enrolled year and study year. I'm stuck here, I need to make one more join:

var res = db.EnrolledYear.Join(db.student,
                  u => u.studentID, s => s.ID,
                  (enroll, student) => new
                  {
                      Godina = enroll.year,
                      FName = student.FirstName
                      LName = student.LastName
                  })
                  .Join(.....?)

Picture in imgur of relations in database:

Just curious: Do you have a working SQL query that gets you the results you want? When trying to compose a complex query in LINQ, I find it useful to work from a proven SQL query. — Casey Crookston
– Casey Crookston, Commented Oct 21, 2019 at 19:39
Yes, I have. But I didn't do LINQ with lambda on multiple tables before, just tried to understand with some examples, now I wanted to learn and use it — private7
– private7, Commented Oct 21, 2019 at 19:41
Unless this is purely scientific interest (to see how 3 tables join works) I would not break your db model to 3 tables. To be precise, StudyYear table is simply meaningless; it has one column "year" that is probably of the same size as the ID for this table. Well, the ID eventually might have even bigger size (can't imagine year going beyond 10000, while ID==count of all-time students); it's meaningless in terms of storage optimization. Correct location for this information would be inside of EnrolledYear table; look at: en.wikipedia.org/wiki/Denormalization — Dmytro
– Dmytro, Commented Oct 21, 2019 at 19:53

Stefan · Accepted Answer · 2019-10-21 20:26:06Z

3

Use Include.

Something like:

db.students.Include(x => x.EnrolledYears).ThenInclude(x=>x.StudyYear).Select(new ...)

edited Oct 21, 2019 at 20:26

Stefan

17.7k11 gold badges65 silver badges86 bronze badges

answered Oct 21, 2019 at 19:44

Ljubomir Bacovic

5942 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeff Mercado · Accepted Answer · 2019-10-21 20:05:47Z

0

Every clause in a query will correspond to a lambda call. Just go down to every clause and convert to an appropriate call.

This particular query could be written like so:

db.student
    .Join(db.EnrolledYear, s => s.ID, u => u.studentID, (s, u) => new { s, u })
    .Join(db.studyYear, x => x.u.studyYearID, g => g.ID, (x, g) => new { x.s, x.u, g })
    .Select(x => new
    {
        Year = x.g.year,
        StudentFName = x.s.FirstName,
        StudentLName = x.s.LastName,
    });

edited Oct 21, 2019 at 20:05

answered Oct 21, 2019 at 19:55

Jeff Mercado

136k34 gold badges268 silver badges282 bronze badges

Collectives™ on Stack Overflow

Inner join in C# with lambda

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related