Linq with Lambda equivalent of SQL

Question

I am relatively new to entity framework and I've been trying to write a Linq statement with Lambda that includes a simple join. I have three tables: Staff - StaffRole - Role.

Partial Diagram

I want a staff member in a certain role that satisfies a certain condition. Its very simple to write this in regular SQL:

SELECT *
FROM Staff s
INNER JOIN StaffRole sr ON s.StaffId = sr.StaffId
INNER JOIN Role r ON sr.RoleId = r.RoleId
WHERE r.Description = 'SpecialisedHealthManager'
AND s.PrimaryShm = 0

Now, writing it in a Linq statement has not given me much luck. I'm thinking it would be something like this:

var actingShm = db.Staff.Join(db.StaffRole,
                           inner => inner.StaffId,
                           outer => outer.Role,
                           (outer, inner) => new
                           {
                               StaffId = inner.StaffId,
                               FirstName = inner.Staff.FirstName,
                               Surname = inner.Staff.Surname,
                               SamAccountName = inner.Staff.SamAccountName,
                               RoleId = outer.Description
                           });

Needless to say, this is not working..

possible duplicate of [How to perform Join between multiple tables in LINQ lambda](http://stackoverflow.com/questions/9720225/how-to-perform-join-between-multiple-tables-in-linq-lambda) — Micha Wiedenmann, Apr 23 '14 at 06:49
please have a look on [How to join 3 tables with lambda expression][1] [1]: [http://stackoverflow.com/questions/9120088/how-to-join-3-tables-with-lambda-expression] — Babu, Apr 23 '14 at 06:50

score 3 · Accepted Answer · edited Apr 23 '14 at 08:34

Try using it this way:

var list = from s in Staff
join sr in StaffRole on s.StaffId equals sr.StaffId
join r in Role on sr.RoleId equals r.RoleId
where r.Description == 'SpecialisedHealthManager' && s.PrimaryShm == 0
select new
{
   StaffId = s.StaffId,
   FirstName = s.Staff.FirstName,
   Surname = s.Staff.Surname, 
   SamAccountName = s.Staff.SamAccountName,
   RoleId = r.Description
});

score 2 · Answer 2 · edited May 23 '17 at 12:16

2

Look here if you realy want to do this with method syntax LINQ:

SO Multiple tables join with lambdas

Also look here:

http://msdn.microsoft.com/pl-pl/library/bb534675(v=vs.110).aspx

for Join extension method syntax. Usage presented in your code is wrong.

edited May 23 '17 at 12:16

Community

1
1

answered Apr 23 '14 at 06:46

GrzegorzM

842
8
19

score 1 · Answer 3 · answered Apr 23 '14 at 06:51

You should have your associations setup so you can do this...

var actingShm = from s in db.Staff
                from r in s.Roles
                where r.Description == "SpecialisedHealthManager"
                select new
                       {
                           StaffId = s.StaffId,
                           FirstName = s.FirstName,
                           Surname = s.Surname,
                           SamAccountName = s.SamAccountName,
                           RoleId = r.Description
                       });

Are you using Entity Framework or Linq2SQL?

search google for tutorials on Many to Many relationships using Entity Framework. I don't know if you want Database First, or Code First. — Aron, Apr 23 '14 at 07:20

Linq with Lambda equivalent of SQL

3 Answers3