linq with recursive table

Question

I have a recursive table with 3 levels of skills that looks like this:

Skill
ParentSkill
Mark

of course firs level is the one with parent level set to null.
I need to produce a structure that would look like:

Level1_Skill  |    Level2_Skill    |  Level3_Skill   |   Mark

for example:

Language   |    .NET    |    LINQ     |    Good

in sql, I would write something like:

SELECT  T1.Skill  AS Level1_Skill, T2.Skill As Level2_Skill,  T3.Skill As Level3_Skill,  T3.Mark
FROM Table T1
WHERE T1.ParentSkill IS NULL
LEFT OUTER JOIN Table T2
ON T1.Skill= T2.ParentSkill
LEFT OUTER JOIN Table T3
ON T2.Skill= T3.ParentSkill

How can it be done in LINQ? Any help would be greatly appreciated..

Answer 1

SELECT  T1.Skill  AS Level1_Skill, T2.Skill As Level2_Skill,  T3.Skill As Level3_Skill,  T3.Mark
FROM Table T1
WHERE T1.ParentSkill IS NULL
LEFT OUTER JOIN Table T2
ON T1.Skill= T2.ParentSkill
LEFT OUTER JOIN Table T3
ON T2.Skill= T3.ParentSkill

...translates to:

 

from l1s in dc.Table

join l2s in dc.Table on l1s.Skill equals l2s.ParentSkill

join l3s in dc.Table on l2s.Skill equals l3s.ParentSkill

where l1s.ParentSkill == null

select new

{

  Level1Skill = l1s.Skill,

  Level2Skill = l2s.Skill,

  Level3Skull = l3s.Skill,

  Mark = l3s.Mark

}

Answer 2

Thanks Kristofer, you're the man . The thing I needed was join statement. .

Answer 3

Sorry, I've just discovered that join in LINQ would give me an inner join, but I need left join, and the examples for left join in LINQ that I see on forums are not working when I need to join a table with itself twice....  

Answer 4

OK, here it is:
                    from l1s in db.Table

                    join l2s in db.Table on l1s.Skill equals l2s.ParentSkill  into JOIN1
                    from skills_l2 in JOIN1.DefaultIfEmpty()
                    join l3s in db.Table on skills_l2.Skill equals l3s.ParentSkill into JOIN2
                    from skills_l3 in JOIN2.DefaultIfEmpty()
                    where l1s.ParentSkill == null
                    select new
                    {
                        Level1Skill = l1s.Skill,
                        Level2Skill = skills_l2.Skill,
                        Level3Skill = skills_l3.Skill,
                       Mark= skills_l3.Mark
                    };


not very readable.... but it works..