How to convert leftouterjoin query into LINQ

Question

I have a sql query which has innerjoin and leftouter join's which is very tricky to mr to convert to LINQ.

SELECT project.ID, project.No, project.Name,  
       APPLN.Id,APPLN.Name,SAR.Name  
FROM Phasefact phase WITH (NOLOCK)  
INNER JOIN AProject project WITH (NOLOCK) on phase.Id = project.ID
INNER JOIN Application APPLN WITH (NOLOCK) ON project.AppId = APPLN.Id
LEFT OUTER JOIN Master master WITH (NOLOCK) ON phase.amId = master.Id
INNER JOIN Ref SAR WITH (NOLOCK) ON SAR.ID = master.Ref_Id
WHERE phase.ID = 123

It is bit confusing as it contains "left outer join". Someone please help.

Arion · Answer 1 · 2012-05-10T06:57:10.160

Maybe something like this:

var result=(
        from phase in db.Phasefact
        join project in db.AProject
            on phase.Id equals project.ID
        join APPLN in db.Application 
            on project.AppId equals APPLN.Id
        //Left join
        from master in db.Master
            .Where(a=>a.Id==phase.amId).DefaultIfEmpty()
        join SAR in db.Ref
            on SAR.ID equals master.Ref_Id
        where phase.ID == 123 
        select new
        {
            project.ID,
            project.No,
            project.Name,  
            APPLN.Id,
            APPLN.Name,
            SAR.Name
        }
    );

EDIT

But i don't get the left join. Why are you using a left join? To me it looks like you first use a left join:

LEFT OUTER JOIN Master master WITH (NOLOCK) ON phase.amId = master.Id

And the after that you have a join that limits the result like this:

INNER JOIN Ref SAR WITH (NOLOCK) ON SAR.ID = master.Ref_Id

This will be the same as doing this:

INNER JOIN Master master WITH (NOLOCK) ON phase.amId = master.Id
INNER JOIN Ref SAR WITH (NOLOCK) ON SAR.ID = master.Ref_Id

score 0 · Answer 2 · answered May 10 '12 at 06:48

I am giving you an basic idea of left outer join in LINQ

var lines = from p in db.Phasefact
join m in db.Master on m.Id equals p.amId into p_m where p.ID == 123 from m in p_m.DefaultIfEmpty() select new { p.ID };

Now you can convert this much more according to your logic... or Query.......

How to convert leftouterjoin query into LINQ

2 Answers2