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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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);
}
Vikas Sardana

Related Questions

Separation of concerns and n-tiered architecture in ASP.NET 5/ASP.NET Core 1

I'm struggling to find a graceful way to keep my DAL layer separate to my MVC/UI layer in ASP.NET 5 thanks to the new built in Dependency Injection which I want to use....For example I have an ASP.NET 5 project, a Business Layer project and a Data Access Project where I have various Entity Framework code such as entities and contexts. in ASP.NET 5 ...
asp.net asp.net-core asp.net-core-mvc dependency-injection entity-framework-core
asked by BenM

Left Outer Join with Entity Framework Core

I'm trying to perform a left outer join request with EF7 (7.0.0-rc1-final), vNext RC1 (rc1-final) and SQL Server 2014...Database :...Pet: Id, Name...User: Id, Name, #PetId...This one works:...var queryWorks = from u in _context.Users join p in _context.Pets on u.PetId equals p.Id into pp from p in pp.DefaultIfEmpty...
c# entity-framework-core
asked by Frrrt

EF Core Left Join and then Inner Join

I have 3 related Tables in MS SQL Server. ...Database Schema...Table_Bid and Table_Stock are related to Table_Product with ProductId. ...I want to fetch the Data like this query in Entity Framework Core...Select * from Table_Bid left join Table_Product on Table_Bid.ProductId = Table_Product.Id inner join Table_Stock on Table_Stock.ProductId = Tabl...
c# entity-framework-core sql-server
asked by Mohammed Altaf

EntityFramework (.NET Core) left outer join by multiple conditions

I need to match ...User... and ...UserAction... by several criteria: ...UserId..., ...ActionType..., ...Approved..., and still want to keep the query as ...left outer join..., because the action could be missing. In regular .net entity framework I would do the following:...var q = from u in db.User join ua in db.UserActions on { u.Id, "Appr...
.net-core c# entity-framework entity-framework-core sql
asked by Alexey Strakh

EFCore Linq Left Outer Join

I've looked through hundreds of examples and still can't quite find what I'm looking for....public class CA { public int Id {get;set;} public List<SignOff> SignOffs {get;set} } public class SignOff { public int Id {get;set;} public int CAId {get;set;} public bool IsCurrentQtr {get;set;} } ...I currently have a search function t...
c# entity-framework-core linq sql-server
asked by Ben

INNER Join and LEFT OUTER JOIN in EF-Core with Linq

I am trying to convert below SQL to Linq query in c# (.net core 2.1 and EF core 2.2)...SELECT TD.*, RD.Match FROM TransactionDetail TD INNER JOIN dbo.Measure M ON M.InternalID = TD.MetricCode LEFT OUTER JOIN ( SELECT tmp.ID, tmp.ReportingDate, 1 AS Match FROM tmp ...
.net-core c# entity-framework-core linq sql
asked by PUBG

Null value in group by after a left outer join in EF Core

i have a problem with a linq query where i try to get certain information out of it and simply can't find where the problem is. I have Tools, Tasks and a M-N ToolTask entity in my ef core db. This query includes 2 left outer joins and a group by at the end....Even though i am checking if the grouped values are not null i am still getting an error m...
c# ef-core-2.2 entity-framework entity-framework-core
asked by Peter

EF Core Left Outer Join failing with nullable object must have a value

I am currently struggling with a EntityFramework Core query. Basically I am applying a LEFT OUTER JOIN (besides of other INNER JOINS)....The first table is ...Solutions... and is basically the foundation, a separate table 'SolutionViews' tracks each single ...view... of a ...Solution..., so let's say the solution with Id 1 has 2 views, you will fin...
c# entity-framework entity-framework-core
asked by Avinor

EF Core Navigation Property Include uses Left Join instead of Inner Join

In my API project, I have ...public class Team { [Key] public long Id { set; get; } [Required] public TeamSettings TeamSettings { get; set; } } public class TeamSettings { [Key] [Required] [Column("TeamSettingsId")] public long Id { set; get; } [Required] [ForeignKey("TeamId")] public long TeamId { get; ...
.net-core asp.net-core c# entity-framework-core sql-server
asked by Zion Hai

Outer join with linq query in EF Core 3 not working

I am developing a football-themed ASP.Net Core MVC web application. I'm trying to get my head around EF Core as everything I've done previously has been straight ADO.Net. ...In my database, I have a table for Players, a table for MatchEvents, and a join table to create a many-to-many relationship, which is called PlayerMatchEvents....I am trying to...
entity-framework-core linq
asked by DivZero

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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