35

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.

Community
  • 1
  • 1
monty
  • 7,888
  • 16
  • 63
  • 100
  • 3
    Why do you join? Use a navigation property. – Gert Arnold Oct 07 '16 at 19:08
  • Well, it is my first time with linq. I am.open for everything that works :-) – monty Oct 07 '16 at 19:49
  • @GertArnold Thanks for the link. I read it. Since I used the database first approach and scaffolded the model classes I have the navigation properties. But I do not see how that helps me retrieving the missing entries. Mind giving me another hint? – monty Oct 10 '16 at 06:44
  • 1
    So you have a property Person.PersonDetails? – Gert Arnold Oct 10 '16 at 07:20
  • The new error indicates that you probably have a `ToList` (or `AsEnumerable`) somewhere before the `select`. – Gert Arnold Oct 10 '16 at 07:22
  • @GertArnold: no ToList(). It exactly the code from the answers. – monty Oct 10 '16 at 08:04
  • 1
    @GertArnold: *grin* I am currently facepalming my self because I got what you tried to tell me. If you post using person.PersonDetails I will take this as answer *grin*. (So far I haven't defined the foreign key properly. Now i have the details property in the person. Though there is still the question if the other way should work or not. But BIG THANKS for pointing me to my own blindness :-) – monty Oct 10 '16 at 08:14

4 Answers4

43

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

Sampath
  • 63,341
  • 64
  • 307
  • 441
  • 4
    Oh dear, LINQ making development painful again, think I'll use raw SQL. – Paul McCarthy Apr 25 '22 at 13:32
  • @PaulMcCarthy Why painful? It is getting better and better with every new version. So we must use it hence it is so powerful and type-safe way to write powerful queries. – Sampath Apr 25 '22 at 17:27
  • 1
    I think 'left join' should have been in version one. Hopefully a future improvement will include it. – Paul McCarthy Apr 26 '22 at 08:32
  • 1
    @PaulMcCarthy Well, at least LINQ (to Entities) exchanges a well known atrocity for a newer atrocity. – Ray May 01 '22 at 10:37
5

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);
}
Vikas Sardana
  • 1,593
  • 2
  • 18
  • 37
1

I agree to author of this post - it is still look like a bug! If you have empty joined tables you always receive "Object reference not set to an instance of an object.". The only way is to check joined tables to null:

    IEnumerable<Models.Service> clubServices =
        from s in services
        from c in clubs.Where(club => club.ClubId == s.ClubId).DefaultIfEmpty()
        from t in clubs.Where(tenant => tenant.TenantId == c.TenantId).DefaultIfEmpty()
        select new Models.Service
        {
            ServiceId = s.ServiceId.ToString(),
            ClubId = c == null ? (int?)null : c.ClubId,
            ClubName = c == null ? null : c.Name,
            HasTimeTable = s.HasTimeTable,
            MultipleCount = s.MultipleCount,
            Name = s.Name,
            Tags = s.Tags.Split(';', StringSplitOptions.RemoveEmptyEntries),
            TenantId = t == null ? (int?)null : t.TenantId,
            TenantName = t == null ? null : t.Name
        };

I unable to check "detailText = person.PersonDetails.Select(d => d.DetailText).SingleOrDefault()" because my joined tables are in different DB.

juriko
  • 81
  • 3
  • 1
    this does not seem to happen anymore in .NET 6 with EF Core. i don't know when it changed in .NET Core, because i previously used LINQ-to-SQL in NET Framework 4.8. i'm guessing that's the reason the other answers look like bugs. it's more consistent with SQL now – symbiont Apr 04 '22 at 15:55
0

Here is a generic implementation that uses method syntax (for those who prefer it), and doesn't require you to remember the arcane implementation of DefaultIfEmpty() and such. It uses expression tree magic to make an equivalent result.:

public static IQueryable<TOutput> LeftJoin<TLeft, TRight, TKey, TOutput>(
    this IQueryable<TLeft> left,
    IEnumerable<TRight> right,
    Expression<Func<TLeft, TKey>> leftKey,
    Expression<Func<TRight, TKey>> rightKey,
    Expression<Func<TLeft, TRight?, TOutput>> join)
{
    var paramJ = Expression.Parameter(typeof(LeftJoinInternal<TLeft, TRight>));
    var paramR = Expression.Parameter(typeof(TRight));
    var body = Expression.Invoke(join, Expression.Field(paramJ, "L"), paramR);
    var l = Expression.Lambda<Func<LeftJoinInternal<TLeft, TRight>, TRight, TOutput>>(body, paramJ, paramR);

    return left
        .GroupJoin(right, leftKey, rightKey, (l, r) => new LeftJoinInternal<TLeft, TRight> { L = l, R = r })
        .SelectMany(j => j.R.DefaultIfEmpty()!, l);
}

private sealed class LeftJoinInternal<TLeft, TRight>
{
    public TLeft L = default!;
    public IEnumerable<TRight> R = default!;
}

Example usage based on the OP:

var result = _dbContext.Person.LeftJoin(
    _dbContext.PersonDetails,
    p => p.Id, d => d.Id,
    (person, details) => new
    {
        id = person.Id,
        firstname = person.Firstname,
        lastname = person.Lastname,
        detailText = detail?.DetailText
    });
Pieter-Jan Briers
  • 111
  • 1
  • 8