Playing with lambda in LinqToSql

April 2, 2009

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: