Linq. Join

[shapper]

Hello,

I am having a few problems translating a SQL query I use to Linq.
I know I could use a Stored Procedure but I would like to translate
this query.

select
root.name as root_name, root.id as root_value,
down1.name as down1_name, down1.id as down1_id,
down2.name as down2_name, down2.id as down2_id,
down3.name as down3_name, down3.id as down3_id
from categories as root
left outer join categories as down1 on down1.parentid = root.id
left outer join categories as down2 on down2.parentid = down1.id
left outer join categories as down3 on down3.parentid = down2.id
where root.parentid is null
order by root_name, down1_name, down2_name, down3_name

Could someone, please, help me out with this?

Thanks,
Miguel

 

[Gaurav Vaish \(a.k.a. MasterGaurav\)]

Hi Miguel,

select
root.name as root_name, root.id as root_value,
down1.name as down1_name, down1.id as down1_id,
down2.name as down2_name, down2.id as down2_id,
down3.name as down3_name, down3.id as down3_id
from categories as root
left outer join categories as down1 on down1.parentid = root.id
left outer join categories as down2 on down2.parentid = down1.id
left outer join categories as down3 on down3.parentid = down2.id
where root.parentid is null
order by root_name, down1_name, down2_name, down3_name

IMHO, the problem you're facing is with "Outer Join", right?

Look at this post:
http://bhaidar.net/cs/archive/2007/08/01/left-outer-join-in-linq-to-sql.aspx



--
Happy Hacking,
Gaurav Vaish
http://blogs.mastergaurav.com
http://eduzine.edujini-labs.com
---------------------------

 

[shapper]

Hi Miguel,


IMHO, the problem you're facing is with "Outer Join", right?

Look at this post:
   http://bhaidar.net/cs/archive/2007/08/01/left-outer-join-in-linq-to-s...

That helped me out.

I came up with this:

var categories = from root in database.Categories
where root.parentid = null
orderby root.name, down1.name, down2.name,
down3.name
join down1 in database.Categories on
down1.parentid equals root.id
join down2 in database.Categories on
down2.parentid equals down1.id
join down3 in database.Categories on
down3.parentid equals down2.id
select new {
root_id = root.id,
root_name = root.name,
down1_id = down1.id,
down1_name = down1.name,
down2_id = down2.id,
down2_name = down2.name,
down3_id = down3.id,
down3_name = down3.name
};

Will this produce the same SQL?

How can I check what SQL a Linq query ouputs?
Do I need to run my application? My application is not ready to run
yet.
I am using Visual Studio 2008.

Thanks,
Miguel