title: How can I implement a LEFT OUTER JOIN in LINQ using lambda syntax on Entity Framework Core 2.0?
body: <p>I am trying to implement a LEFT OUTER JOIN in Linq against an Entity Framework Core 2.0 DbContext.  It's important that the query is translated to SQL, rather than evaluated locally.  I've reviewed several StackOverflow solutions including <a href="https://stackoverflow.com/questions/584820/how-do-you-perform-a-left-outer-join-using-linq-extension-methods">this one</a> which is good, but none are using EF Core.</p>
  
  <p>The problem I get is that EF Core returns the following warning/error for the <code>DefaultIfEmpty()</code> method:</p>
  
  <p><code>The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally</code></p>
  
  <p>Without the <code>DefaultIfEmpty()</code> method an INNER JOIN is used.  My LINQ query looks like this:</p>
  
  <pre><code>var join = context.Portfolios
             .Where(p =&gt; p.IsActive)
             .GroupJoin(context.BankAccounts, 
                        prt =&gt; prt.Id, 
                        bnk =&gt; bnk.PortfolioId, 
                        (prt, bnks) =&gt; new {Portfolio=prt,Account=bnks.DefaultIfEmpty()})
             .SelectMany(r =&gt; r.Accounts.DefaultIfEmpty(),
                         (p, b) =&gt; new 
                             {
                                 Id = p.Portfolio.Id,
                                 BankAccount = b.BankAccountNumber,
                                 BankRef = b.BeneficiaryReference,
                                 Code = p.Portfolio.Code,
                                 Description = p.Portfolio.DisplayName
                             });
  </code></pre>
  
  <p>Does anyone know a way around this?  </p>