我试图得到一个左联接工作在Linq使用ASP.NET核心和EntityFramework核心。
两张table的简单情况：
人员（ID、名字、姓氏）
人员详细信息（标识、人员ID、详细信息文本）
我尝试查询的数据是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 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);
}

swagger中的结果是缺少人员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()

谷歌给了我一个："LEFT OUTER JOIN PROBLEMS #4002"以及"Left outer join @ Stackoverflow"
现在我不确定这是否是一些仍然存在的bug或者应该已经修复了。我使用的是EntityFramework核心RC2。

溶液1：导航属性

正如Gert Arnold在评论中指出的：使用导航属性
这意味着（工作）查询看起来如下所示

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报告）。
当连接方式工作一次时，仍然对更新感到高兴。

4条答案

按热度按时间

lawou6xi1#

如果需要执行左连接，则必须使用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
                };

您可以了解更多信息：Left Outer Join in LINQ to Entities

赞(0）回复(0）举报 2023-03-21

xmq68pz92#

你不用做左连接，你使用的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);
}

gopyfrb33#

我同意这篇文章的作者-它看起来仍然像一个bug！如果你有空的连接表，你总是会收到“对象引用没有设置为对象的示例"。唯一的方法是检查连接表为空：

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()“，因为我的连接表在不同的数据库中。

ftf50wuq4#

下面是一个使用方法语法的通用实现（对于那些喜欢它的人），它不需要你记住DefaultIfEmpty()之类的《双城之战》实现。它使用表达式树魔术来产生等价的结果。

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

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

ASP.NET核心和实体框架核心：Linq中的左(外)联接

更新1：

溶液1：导航属性

4条答案

相关问题

热门标签

最新问答