LINQ to SQL - Left Outer Join with multiple join conditions

Question

I have the following SQL  which I am trying to translate to LINQ   SELECT f value FROM period as p  LEFT OUTER JOIN facts AS f ON p id   f periodid AND f otherid   17 WHERE p companyid   100   I have seen the typical implementation of the left outer join  ie  into x from y in x DefaultIfEmpty   etc   but am unsure how to introduce the other join condition  AND f otherid   17   EDIT  Why is the AND f otherid   17 condition part of the JOIN instead of in the WHERE clause  Because f may not exist for some rows and I still want these rows to be included  If the condition is applied in the WHERE clause  after the JOIN - then I don t get the behaviour I want   Unfortunately this   from p in context Periods join f in context Facts on p id equals f periodid into fg from fgi in fg DefaultIfEmpty   where p companyid    100  amp  amp  fgi otherid    17 select f value   seems to be equivalent to this   SELECT f value FROM period as p  LEFT OUTER JOIN facts AS f ON p id   f periodid  WHERE p companyid   100 AND f otherid   17   which is not quite what I m after

User · Accepted Answer

You need to introduce your join condition before calling DefaultIfEmpty(). I would just use extension method syntax:

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty()
where p.companyid == 100
select f.value

Or you could use a subquery:

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in (from f in fg
             where f.otherid == 17
             select f).DefaultIfEmpty()
where p.companyid == 100
select f.value

User · Answer

this works too     if you have multiple column joins  from p in context Periods join f in context Facts  on new       id   p periodid      p otherid   equals new       f id      f otherid   into fg from fgi in fg DefaultIfEmpty   where p companyid    100 select f value

User · Answer

Can be written using composite join key  Also if there is need to select properties from both left and right sides the LINQ can be written as  var result   context Periods      Where p   gt  p companyid    100       GroupJoin          context Facts          p   gt  new  p id  otherid   17           f   gt  new  id   f periodid  f otherid            p  f    gt  new  p  f        SelectMany          pf   gt  pf f DefaultIfEmpty             pf  f    gt  new MyJoinEntity                       Id   pf p id              Value   f value                 and so on

User · Answer

It seems to me there is value in considering some rewrites to your SQL code before attempting to translate it   Personally  I d write such a query as a union  although I d avoid nulls entirely     SELECT f value   FROM period as p JOIN facts AS f ON p id   f periodid WHERE p companyid   100       AND f otherid   17 UNION SELECT NULL AS value   FROM period as p WHERE p companyid   100       AND NOT EXISTS                          SELECT                            FROM facts AS f                        WHERE p id   f periodid                              AND f otherid   17                           So I guess I agree with the spirit of  MAbraham1 s answer  though their code seems to be unrelated to the question    However  it seems the query is expressly designed to produce a single column result comprising duplicate rows -- indeed duplicate nulls  It s hard not to come to the conclusion that this approach is flawed

User · Answer

Another valid option is to spread the joins across multiple LINQ clauses  as follows   public static IEnumerable lt Announcementboard gt  GetSiteContent string pageName  DateTime date        IEnumerable lt Announcementboard gt  content   null      IEnumerable lt Announcementboard gt  addMoreContent   null          try                       content   from c in DB Announcementboards                  Can be displayed beginning on this date               where c Displayondate  gt  date AddDays -1                   Doesn t Expire or Expires at future date                amp  amp   c Displaythrudate    null    c Displaythrudate  gt  date                   Content is NOT draft  and IS published                amp  amp  c Isdraft     N   amp  amp  c Publishedon    null               orderby c Sortorder ascending  c Heading ascending               select c                  Get the content specific to page names             if   string IsNullOrEmpty pageName                               addMoreContent   from c in content                   join p in DB Announceonpages on c Announcementid equals p Announcementid                   join s in DB Apppagenames on p Apppagenameid equals s Apppagenameid                   where s Apppageref ToLower      pageName ToLower                     select c                                Add the specified content using UNION             content   content Union addMoreContent                   Exclude the duplicates using DISTINCT             content   content Distinct                 return content                catch  MyLovelyException ex                   Add your exception handling here         throw ex

User · Answer

I know it s  a bit late  but just in case if anybody needs to do this in LINQ Method syntax  which is why I found this post initially   this would be how to do that   var results   context Periods      GroupJoin          context Facts          period   gt  period id          fk   gt  fk periodid           period  fact    gt  fact Where f   gt  f otherid    17                                 Select fact Value                                 DefaultIfEmpty              Where period companyid  100       SelectMany fact  gt fact  ToList

[c#] LINQ to SQL - Left Outer Join with multiple join conditions

Examples related to c#

Examples related to sql

Examples related to linq

Examples related to linq-to-sql

Examples related to outer-join