ASP.NET核心& EntityFramework核心:左(外)加入Linq
ASP.NET核心& EntityFramework核心:左(外)加入Linq
提问于 2016-10-07 14:06:20
我正在尝试使用ASP.NET Core和EntityFramework Core在Linq中获得一个左连接。
有两个表的简单情况：
PersonDetails (id，PersonId，DetailText)
我试图查询的数据是Person.id、Person.firstname、Person.lastname和PersonDetails.DetailText。有些人没有DetailText，所以想要的结果为NULL。
在SQL中，它工作得很好。
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控制器中，我查询：
[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);
}“傲慢”中的结果是“失踪人口3”(没有详细文本的)。
[
{
"id": 1,
"firstname": "First1",
"lastname": "Last1",
"detailText": "details1"
},
{
"id": 2,
"firstname": "First2",
"lastname": "Last2",
"detailText": "details2"
}
]我在Linq做了什么错事？
更新1：
谢谢你的回答和链接到目前为止。
我使用into和.DefaultIfEmpty()复制并粘贴了下面的代码，经过进一步的阅读，我了解到这应该是可行的。
不幸的是它没有。
首先，代码开始抛出异常，但仍然返回前两个结果(缺少NULL)。从输出窗口复制粘贴：
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()谷歌给了我一个：“左外连接问题#4002”和“左外联接@ Stackoverflow”
现在，我不确定这是否是某个仍然存在或应该已经修复的bug。我使用的是EntityFramework核心RC2。
解决方案1:导航属性
正如Gert在注释中指出的:使用导航属性
这意味着(工作中的)查询看起来很简单
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不在脚手架模型类中。但是使用这是最简单的解决方案(并且工作得很快)，而不是现在的联接(参见bug报告)。
当连接方式一次工作时，仍然对更新感到高兴。
回答 3
Stack Overflow用户
回答已采纳
发布于 2016-10-07 17:29:24
如果需要执行左联接，则必须使用into和DefaultIfEmpty()，如下所示。
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
};您可以了解更多相关信息：在LINQ实体中左外加入
Stack Overflow用户
发布于 2016-10-07 19:36:11
如果不执行左联接，则所使用的linq基本上是创建内部联接。对于linq中的左联接，使用into关键字
[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);
}Stack Overflow用户
发布于 2018-09-30 20:54:09
我同意这篇文章的作者-它看起来仍然像一个错误！如果有空的联接表，则始终收到“对象引用未设置为对象的实例”。唯一的方法是将已连接的表检查为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
};我无法检查"detailText = person.PersonDetails.Select(d => d.DetailText).SingleOrDefault()“，因为我的联接表位于不同的DB中。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39919230
复制相关文章
相似问题
腾讯云开发者
