I love play linq queries in lambda, some days ago i answered a question in stackoverflow:  How to transalte this sql query in lambda?

SELECT COUNT(*) AS [value]
FROM [Persons] AS [t0]
INNER JOIN [personlocations] AS [t1] ON [t0].[personId] = [t1].[personid]
INNER JOIN [Locations] AS [t2] ON [t2].[locationid] = [t1].[locationid]
INNER JOIN [PersonRoles] AS [t3] ON [t0].[personId] = [t3].[personid]
INNER JOIN [Roles] AS [t4] ON [t4].[roleid] = [t3].[roleid]
WHERE ([t4].[description] = ‘Student’) AND ([t2].[description] = ‘Flamengo’)

It’s simple:

var persons = Persons
        .Join(Personlocations, p=>p.PersonId, ps=>ps.Personid,
        (p,ps) =>
new {p,ps}).Where(a => a.ps.Location.Description ==“Flamengo”)
        .Join(PersonRoles,pr=> pr.p.PersonId, r=>r.Personid,(pr,r) =>
new {pr.p,r}).Where(a=>a.r.Role.Description==“Student”)
        .Select(p=>
new {p.p});

or:
var persons = Persons.Where(p=>(p.Personlocations.Select(ps=>ps.Location).Where(l=>l.Description == “Flamengo”).Count() > 0)
&& (p.PersonRoles.Select(pr=>pr.Role).Where(r=>r.Description ==
“Student”).Count() > 0));
or:
var persons = Persons
            .Where(p=>(p.Personlocations.Select(ps=>ps.Location)
            .Select(l=>l.Description).Contains(
“Flamengo”)) &&
            (p.PersonRoles.Select(pr=>pr.Role)
            .Select(r=>r.Description).Contains(
“Student”)));

There are more ways to do this same result in linq. in another post i will discuss some.