ASP.NET核心和EntityFramework核心:左(外部)加入Linq [英] ASP.NET Core & EntityFramework Core: Left (Outer) Join in Linq

问题描述

我正在尝试使用ASP.NET Core和EntityFramework Core在Linq中进行左联接工作.

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

有两个表的简单情况:

人员(身份证，名字，姓氏)

Person (id, firstname, lastname)

PersonDetails(ID，PersonId，DetailText)

PersonDetails (id, PersonId, DetailText)

我尝试查询的数据是Person.id，Person.firstname，Person.lastname和PersonDetails.DetailText. 某些人没有DetailText，因此所需结果为NULL.

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.

在SQL中可以正常工作

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

预期结果:

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

我查询的Web API控制器内部:

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

大摇大摆的结果缺少了Person 3(没有详细文字的人)

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"
  }
]

我在Linq做什么错了?

What am I doing wrong in Linq?

感谢您提供的答案和到目前为止的链接.

Thank you for the answers and the links so far.

我使用into和.DefaultIfEmpty()复制并粘贴了以下代码，经过进一步阅读后，我知道这应该可行.

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

不幸的是，不是.

首先，代码开始引发异常，但仍返回前两个结果(缺少NULL).从输出窗口复制粘贴:

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给了我一个:外部联接问题#4002" 以及左外部联接@ Stackoverflow"

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

现在我不确定这是否是仍然存在或应该已经修复的错误.我正在使用EntityFramework Core RC2.

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.

正如Gert Arnold在评论中指出的:使用导航属性

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

在我的PersonExampleDB中，我没有正确设置外键，因此属性PersonDetails不在脚手架模型类中.但是使用它是最简单的解决方案(并且甚至可以快速运行)，而不是目前的联接(请参见错误报告).

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.

推荐答案

如果需要进行左联接，则必须使用into和DefaultIfEmpty()，如下所示.

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

这篇关于ASP.NET核心和EntityFramework核心:左(外部)加入Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！