Linq Left outer join Query

Question

How to convert this sql query into Linq query?

select * 
from setupUOMs as su 
LEFT OUTER JOIN scmSKUUoMs as ssu
   on su.UoMID != ssu.UoMID
where ssu.SKUID = 446 and su.UMTypeID = 5

Following is the linq query.

from c in setupUOMs
        join o in scmSKUUoMs
           on c.UOMID equals o.UoMID into sr
        from x in sr.DefaultIfEmpty()
        where x.SKUID == 446 
        select x

In above query I've so far only done to extract the join number but what I want is to select the non equal records of left table but I'm able to show joined record. and it returns the records of only left table while my result is based on both columns. In where clause I can access x.SKUID which is from left table but can't access x.UMTypesID which is form right table (Means no column from right table is returned on which I can make condition).

Since you are checking `ssu.SKUID = 446` in the `where` you could use an inner join instead. — Magnus, Nov 20 '12 at 09:23
I've took left outer join of table setupUOMs on scmSKUUoMs and return the non matched record of setupUoMs.. Hope you understand Arnold.. — Zeb-ur-Rehman, Nov 20 '12 at 09:24
magnus i'm not taking inner join i'm trying to take Left Outer Join... — Zeb-ur-Rehman, Nov 20 '12 at 09:25
@Zeb-ur-Rehman but since you are checking `ssu.SKUID = 446` in the `where` `ssu` can never be `null` (which a left join might give you) right? — Magnus, Nov 20 '12 at 09:34
@Zeb-ur-Rehman I see that, but what did you try in _linq_? You're really supposed to show some research effort when asking a question. — Gert Arnold, Nov 20 '12 at 09:45
@Arnold Check the post again i'm giving my finding over there.. — Zeb-ur-Rehman, Nov 20 '12 at 09:54
Possible duplicate of [How to implement left join in JOIN Extension method](http://stackoverflow.com/questions/3792888/how-to-implement-left-join-in-join-extension-method) — JoshuaTheMiller, Jan 09 '17 at 17:22

score 0 · Answer 1 · answered Nov 20 '12 at 12:16

0

Take a look at this LEFT JOIN LINQTOSQL Example.

http://www.devcurry.com/2011/01/linq-left-join-example-in-c.html

Maybe it will help.

answered Nov 20 '12 at 12:16

Michael Samteladze

1,310
15
38

score 0 · Answer 2 · answered Nov 20 '12 at 18:37

A join with a non-equals condition is not a join but a Cartesian product excluding the inner joined records. There may be valid reasons to make a query as you did, but there's always a risk of blowing up the result set in terms of number of records.

So let's assume the conditions ensure a result that makes sense, then you can do:

from su in setupUOMs
from ssu in scmSKUUoMs // from .. from is a Cartesian product
where su.UOMID != ssu.UoMID
                && ssu.SKUID == 446 
                && su.UMTypeID == 5
select new { su.Prop1, ssu.Prop2, ... }

Linq Left outer join Query

2 Answers2