title: ASP.NET Core & EntityFramework Core: Left (Outer) Join in Linq
body: <p>I am trying to get a left join working in Linq using ASP.NET Core and EntityFramework Core.</p>
  
  <p>Simple situation with two tables:</p>
  
  <ul>Person (id, firstname, lastname)</ul>
  
  <ul>PersonDetails (id, PersonId, DetailText)</ul>
  
  <p>The data I try to query is Person.id, Person.firstname, Person.lastname and PersonDetails.DetailText.
  Some persons do not have a DetailText so the wanted result is NULL.</p>
  
  <p>In SQL it works fine</p>
  
  <pre><code>
  SELECT p.id, p.Firstname, p.Lastname, d.DetailText FROM Person p 
  LEFT JOIN PersonDetails d on d.id = p.Id 
  ORDER BY p.id ASC
  </code></pre>
  
  <p>results as expected:</p>
  
  <pre><code>
  # | id | firstname | lastname | detailtext
  1 | 1  | First1    | Last1    | details1
  2 | 2  | First2    | Last2    | details2
  3 | 3  | First3    | Last3    | NULL
  </code></pre>
  
  <p>inside my Web API controller i query:</p>
  
  <pre><code>
  [HttpGet]
  public IActionResult Get()
  {
      var result = from person in _dbContext.Person
                      join detail in _dbContext.PersonDetails on person.Id equals detail.PersonId
                      select new
                      {
                          id = person.Id,
                          firstname = person.Firstname,
                          lastname = person.Lastname,
                          detailText = detail.DetailText
                      };
     return Ok(result);
  }
  </code></pre>
  
  <p>The results in swagger are missing Person 3 (those without detail text)</p>
  
  <pre><code>
  [
    {
      "id": 1,
      "firstname": "First1",
      "lastname": "Last1",
      "detailText": "details1"
    },
    {
      "id": 2,
      "firstname": "First2",
      "lastname": "Last2",
      "detailText": "details2"
    }
  ]
  </code></pre>
  
  <p>What am I doing wrong in Linq?</p>
  
  <hr>
  
  <h2>Update 1:</h2>
  
  <p>Thank you for the answers and the links so far.</p>
  
  <p>I copied and pasted the code(s) below using <code>into</code> and <code>.DefaultIfEmpty()</code> and after some further readings I understand that this should work.</p>
  
  <p>Unfortunatly it doesn't.</p>
  
  <p>First there the code starts throwing exceptions but still returns with the first two results (with the NULLs missing). Copy Paste from the output window:</p>
  
  <pre><code>
  System.NullReferenceException: Object reference not set to an instance of an object.
     at lambda_method(Closure , TransparentIdentifier`2 )
     at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
     at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
     at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
     at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)
     at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)
     at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.WriteObject(TextWriter writer, Object value)
     at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.d__9.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__32.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__31.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__29.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__23.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__18.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Builder.RouterMiddleware.d__4.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.ApplicationInsights.AspNetCore.ExceptionTrackingMiddleware.d__4.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.ApplicationInsights.AspNetCore.RequestTrackingMiddleware.d__4.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Server.IISIntegration.IISMiddleware.d__8.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Hosting.Internal.RequestServicesContainerMiddleware.d__3.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Server.Kestrel.Internal.Http.Frame`1.d__2.MoveNext()
  Microsoft.AspNetCore.Server.Kestrel:Error: Connection id "0HKVGPV90QGE0": An unhandled exception was thrown by the application.
  
  System.NullReferenceException: Object reference not set to an instance of an object.
     at lambda_method(Closure , TransparentIdentifier`2 )
     at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
     at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
     at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
     at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)
     at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)
     at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.WriteObject(TextWriter writer, Object value)
     at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.d__9.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__32.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__31.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__29.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__23.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__18.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Builder.RouterMiddleware.d__4.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.ApplicationInsights.AspNetCore.ExceptionTrackingMiddleware.d__4.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.ApplicationInsights.AspNetCore.RequestTrackingMiddleware.d__4.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Server.IISIntegration.IISMiddleware.d__8.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Hosting.Internal.RequestServicesContainerMiddleware.d__3.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.AspNetCore.Server.Kestrel.Internal.Http.Frame`1.d__2.MoveNext()
  </code></pre>
  
  <p>Google gave me that one: <a href="https://github.com/aspnet/EntityFramework/issues/4002" rel="noreferrer">"LEFT OUTER JOIN PROBLEMS #4002"</a>
  as well as <a href="https://stackoverflow.com/questions/34095062/left-outer-join-with-entity-framework-core">"Left outer join @ Stackoverflow"</a></p>
  
  <p>Now I am not sure if that is some bug that either still exists or should have been fixed already. I am using EntityFramework Core RC2.</p>
  
  <hr>
  
  <h2>Solution 1: Navigation Properties</h2>
  
  <p>As Gert Arnold pointed out in the comments: use the navigation properties</p>
  
  <p>This means the (working) query simply looks like</p>
  
  <pre><code>
  var result = from person in _dbContext.Person
               select new
               {
                  id = person.Id,
                  firstname = person.Firstname,
                  lastname = person.Lastname,
                  detailText = person.PersonDetails.Select(d => d.DetailText).SingleOrDefault()
              };
  
  return Ok(result);
  </code></pre>
  
  <p>In my PersonExampleDB I hadn't correctly set the foreign key so the property <code>PersonDetails</code> wasn't in the scaffolded model class. But using this is the simplest solution (and works and even works fast) instead of the join for now (see the bug reports).</p>
  
  <hr>
  
  <p>Still happy about updates when the join way works one time.</p>