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

asp.net-core entity-framework-core 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.

1
12
5/23/2017 11:45:41 AM

Accepted Answer

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

19
10/8/2016 3:25:19 AM

Popular Answer

You not doing left join, the linq which you are using is basically creating a inner join. For left join in linq use into keyword

[HttpGet]
public IActionResult Get()
{
    var result = from person in _dbContext.Person
                    join detail in _dbContext.PersonDetails on person.Id equals detail.PersonId  
                    into Details
                    from defaultVal in Details.DefaultIfEmpty()
                    select new
                    {
                        id = person.Id,
                        firstname = person.Firstname,
                        lastname = person.Lastname,
                        detailText = defaultVal.DetailText
                    };
   return Ok(result);
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow