switch linq syntax

Question

 var folders = from r in this.bdd.Rights
               join f in this.bdd.Folders on r.RightFolderId equals f.FolderId
               join rs in this.bdd.RightSpecs on r.RightSpecId equals rs.SpecIdRight
               where r.RightUserId == userId
               where rs.SpecRead == true
               where rs.SpecWrite == true
               select f;

How transform this linq query in the other syntax?

 var folders = this.bdd.Rights.Where(r => r.....

Answer 1

Honestly, when it comes to using Joins I stick with Query syntax.

Lambda Syntax for that query is going to be next to unreadable.

Unfortunately I don't have the time to type out the conversion at the moment, so I'm going to suggest you download LINQPad. It will allow you to take your Query syntax and see the Lambda translation along with the SQL that gets generated.

Download LINQPad

So...I decided to take a stab at the code for practice. I think it would look something like:

var folders = this.bdd.Rights
                  .Join(this.bdd.Folders,
                        r => r.RightFolderId,
                        f => f.FolderId,
                        (r,f) => new { Outer = r, Inner = f })
                  .Join(this.bdd.RightSpecs,
                        r => r.Outer.RightSpecId,
                        rs => rs.SpecIdRight,
                        (r,rs) => new { Outer = r, Inner = rs })
                  .Where(r => r.Outer.Outer.RightUserId == userId)
                  .Where(r => r.Inner.SpecRead == true)
                  .Where(r => r.Inner.SpecWrite == true)
                  .Select(r => r.Outer.Inner);

Like I said, it was for practice so I'm not 100% sure if it's correct BUT it should definitely give you an idea why you might want to consider keeping the Query syntax.

Answer 2

If you add the associations of these items in the LinqToSql designer, you'll get navigation properties. These properties make it easier to write the query without worrying about the joining concept.

I would rewrite the original query to remove duplication from joins. You want a filtered folder query, so write a query that filters folders.

var folderQuery =
  from f in this.bdd.Folders
  where f.Rights.Any(r => r.RightUserId = userId &&
    r.RightSpecs.Any(rs => rs.SpecRead && rs.SpecWrite))
  select f;

And then translate it to this:

var folderQuery = this.bdd.Folders
  .Where(f => f.Rights.Any(r => r.RightUserId = userId &&
    r.RightSpecs.Any(rs => rs.SpecRead && rs.SpecWrite));

Answer 3

Read section 7.16.2 in the C# 4 specification. All the rules are there.

Let's go through it. You have:

from r in this.bdd.Rights 
join f in this.bdd.Folders on r.RightFolderId equals f.FolderId 
join rs in this.bdd.RightSpecs on r.RightSpecId equals rs.SpecIdRight 
where r.RightUserId == userId 
where rs.SpecRead == true 
where rs.SpecWrite == true 
select f; 

The spec says

A query expression with a join clause without an into followed by something other than a select clause

from x1 in e1
join x2 in e2 on k1 equals k2 
...

is translated into

from * in ( e1 ) 
. Join(e2 , x1 => k1 , x2 => k2 , ( x1 , x2 ) => new { x1 , x2 })
...

OK, so we start by translating your query into

from * in 
(this.bdd.Rights)
.Join(
    this.bdd.Folders, 
    r=>r.RightFolderId, 
    f=>f.FolderId, 
    (*, f)=> new {r, f})
join rs in this.bdd.RightSpecs on r.RightSpecId equals rs.SpecIdRight 
where r.RightUserId == userId 
where rs.SpecRead == true 
where rs.SpecWrite == true 
select f; 

Now the same rules applies again; we have a query expression with a join clause without an into followed by something other than a select. So that becomes:

from ** in 
((this.bdd.Rights)
.Join(
    this.bdd.Folders, 
    r=>r.RightFolderId, 
    f=>f.FolderId, 
    (r, f)=> new {r, f}))
.Join(
    this.bdd.RightSpecs,
    *=>r.RightSpecId,
    rs=>rs.SpecIdRight,
    (*, rs)=> new {*, rs})
where r.RightUserId == userId 
where rs.SpecRead == true 
where rs.SpecWrite == true 
select f; 

What's the next rule that applies? Consult the spec:

A query expression with a where clause

from x in e
where f
...

is translated into

from x in ( e ) . Where ( x => f )
...

OK, so we apply that transformation three times and get

from ** in 
(((((this.bdd.Rights)
.Join(
    this.bdd.Folders, 
    r=>r.RightFolderId, 
    f=>f.FolderId, 
    (r, f)=> new {r, f}))
.Join(
    this.bdd.RightSpecs,
    *=>r.RightSpecId,
    rs=>rs.SpecIdRight,
    (*, rs)=> new {*, rs}))
.Where(**=>r.RightUserId == userId ))
.Where(**=>rs.SpecRead == true))
.Where(**=>rs.SpecWrite == true)
select f; 

Now what? Consult the specification:

from x in e select v

is translated into

( e ) . Select ( x => v )

So the code above is translated into

((((((this.bdd.Rights)
.Join(
    this.bdd.Folders, 
    r=>r.RightFolderId, 
    f=>f.FolderId, 
    (r, f)=> new {r, f}))
.Join(
    this.bdd.RightSpecs,
    *=>r.RightSpecId,
    rs=>rs.SpecIdRight,
    (*, rs)=> new {*, rs}))
.Where(**=>r.RightUserId == userId ))
.Where(**=>rs.SpecRead == true))
.Where(**=>rs.SpecWrite == true))
.Select(**=>f);

where * and ** are transparent identifiers. So this is then further transformed into

((((((this.bdd.Rights)
.Join(
    this.bdd.Folders, 
    r=>r.RightFolderId, 
    f=>f.FolderId, 
    (r, f)=> new {r, f}))
.Join(
    this.bdd.RightSpecs,
    t1=>t1.r.RightSpecId,
    rs=>rs.SpecIdRight,
    (t1, rs)=> new {t1, rs}))
.Where(t2=>t2.t1.r.RightUserId == userId ))
.Where(t2=>t2.rs.SpecRead == true))
.Where(t2=>t2.rs.SpecWrite == true))
.Select(t2=>t2.t1.f);

We introduced a whole lot of unnecessary parenthesis in there. We could take them out and say that this is equivalent to

this.bdd.Rights
.Join(
    this.bdd.Folders, 
    r=>r.RightFolderId, 
    f=>f.FolderId, 
    (r, f)=> new {r, f})
.Join(
    this.bdd.RightSpecs,
    t1=>t1.r.RightSpecId,
    rs=>rs.SpecIdRight,
    (t1, rs)=> new {t1, rs})
.Where(t2=>t2.t1.r.RightUserId == userId )
.Where(t2=>t2.rs.SpecRead == true)
.Where(t2=>t2.rs.SpecWrite == true)
.Select(t2=>t2.t1.f);

Easy peasy. It's just a straightforward syntactic rewrite, except for a bit of analysis of the transparent identifiers.