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


Translate

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 && 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.


All Answers
  • Translate

    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
    

  • Translate

    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
    

  • Translate

    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 => period.id,
            fk => fk.periodid,
            (period, fact) => fact.Where(f => f.otherid == 17)
                                  .Select(fact.Value)
                                  .DefaultIfEmpty()
        )
        .Where(period.companyid==100)
        .SelectMany(fact=>fact).ToList();
    

  • Translate

    Another valid option is to spread the joins across multiple LINQ clauses, as follows:

    public static IEnumerable<Announcementboard> GetSiteContent(string pageName, DateTime date)
    {
        IEnumerable<Announcementboard> content = null;
        IEnumerable<Announcementboard> addMoreContent = null;
            try
            {
                content = from c in DB.Announcementboards
                  //Can be displayed beginning on this date
                  where c.Displayondate > date.AddDays(-1)
                  //Doesn't Expire or Expires at future date
                  && (c.Displaythrudate == null || c.Displaythrudate > date)
                  //Content is NOT draft, and IS published
                  && c.Isdraft == "N" && 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;
                }
    
                //CROSS-JOIN this content
                content = content.Union(addMoreContent);
    
                //Exclude dupes - effectively OUTER JOIN
                content = content.Distinct();
    
                return content;
            }
        catch (MyLovelyException ex)
        {
            throw ex;
        }
    }
    

  • Translate

    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.