ASP.NET Core & EntityFramework Core: Left (Outer) Join in Linq

Question

I am trying to get a left join working in Linq using ASP.NET Core and EntityFramework Core.

Simple situation with two tables:

Person (id, firstname, lastname)

PersonDetails (id, PersonId, DetailText)

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.

In SQL it works fine

 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 

results as expected:

 # | id | firstname | lastname | detailtext 1 | 1  | First1    | Last1    | details1 2 | 2  | First2    | Last2    | details2 3 | 3  | First3    | Last3    | NULL 

inside my Web API controller i query:

 [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); } 

The results in swagger are missing Person 3 (those without detail text)

 [   {     "id": 1,     "firstname": "First1",     "lastname": "Last1",     "detailText": "details1"   },   {     "id": 2,     "firstname": "First2",     "lastname": "Last2",     "detailText": "details2"   } ] 

What am I doing wrong in Linq?

---

Update 1:

Thank you for the answers and the links so far.

I copied and pasted the code(s) below using into and .DefaultIfEmpty() and after some further readings I understand that this should work.

Unfortunatly it doesn't.

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:

 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() 

Google gave me that one: "LEFT OUTER JOIN PROBLEMS #4002" as well as "Left outer join @ Stackoverflow"

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.

---

Solution 1: Navigation Properties

As Gert Arnold pointed out in the comments: use the navigation properties

This means the (working) query simply looks like

 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); 

In my PersonExampleDB I hadn't correctly set the foreign key so the property PersonDetails 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).

---

Still happy about updates when the join way works one time.

Answer 1

If you need to do the Left joins then you have to use into and DefaultIfEmpty() as shown below.

var result = from person in _dbContext.Person              join detail in _dbContext.PersonDetails on person.Id equals detail.PersonId into Details              from m in Details.DefaultIfEmpty()                select new                 {                     id = person.Id,                     firstname = person.Firstname,                     lastname = person.Lastname,                     detailText = m.DetailText                 }; 

You can learn more about it : Left Outer Join in LINQ to Entities