ASP.NET Core&EntityFramework核心:左(外)加入Linq
我正在尝试使用ASP.NET Core和EntityFramework Core在Linq中使用左连接.
两个表的简单情况: 人(id,名字,姓氏) PersonDetails(id,PersonId,DetailText) 我尝试查询的数据是Person.id,Person.firstname,Person.lastname和PersonDetails.DetailText. 在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 API控制器中我查询: [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,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,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() 谷歌给了我一个:“LEFT OUTER JOIN PROBLEMS #4002” 现在我不确定这是否仍然存在或应该已经修复的错误.我正在使用EntityFramework Core RC2. 解决方案1:导航属性 正如Gert Arnold在评论中指出的那样:使用导航属性 这意味着(工作)查询看起来像 var result = from person in _dbContext.Person select new { id = person.Id,detailText = person.PersonDetails.Select(d => d.DetailText).SingleOrDefault() }; return Ok(result); 在我的PersonExampleDB中,我没有正确设置外键,因此属性PersonDetails不在scaffolded模型类中.但是使用它是最简单的解决方案(并且工作甚至可以快速工作)而不是现在的连接(参见错误报告). 当加入方式有效时,仍然对更新感到满意. 解决方法
如果你需要执行Left连接,那么你必须使用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,detailText = m.DetailText }; 您可以了解更多信息:Left Outer Join in LINQ to Entities (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
- asp.net – “SELECT * FROM …”VS“SELECT ID FROM …”性
- asp.net-core – 在以下可用的中间件中定义变量
- WiX(Votive)项目参考收获(HEAT)ASP.Net Web应用程序
- asp.net mvc使用html5mode和路由托管角度应用程序
- asp.net – 自定义日期colmn telerik网格的过滤器选项
- razor – 如何将值绑定到ASP.NET Core MVC中枚举中每个项的
- Asp.net调用C#层调用Managed C调用Native C
- asp.net-mvc – 仍然支持MetaWeblog API吗?
- asp.net – 将Dictionary加入querystring的最快方法是什么?
- asp.net – 在主页面加载中丢失了ViewState对象
- asp.net-mvc-2 – 在asp.net MVC中的表中插入多行
- asp.net-mvc – 在ASP.NET MVC中禁用布局?
- asp.net – IIS返回找不到.mp4文件的404
- asp.net-mvc – asp.net mvc错误处理的最佳做法
- asp.net – 无法识别配置文件Web.config
- asp.net-mvc – 显式指定ASP.NET MVC中控制器返回
- asp.net – 使用Using语句的SQLConnection,从里面
- asp.net-mvc-3 – 从剃刀视图引用资源文件
- asp.net – 在DataBind上默认为空值
- asp.net – .NET:如何从Flash创建缩略图