Ядро ASP.NET и ядро EntityFramework: левое (внешнее) соединение в Linq
Я пытаюсь заставить работать левое соединение в Linq с использованием ASP.NET Core и EntityFramework Core.
Простая ситуация с двумя таблицами:
Персона (идентификатор, имя, фамилия)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 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, 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"
Теперь я не уверен, что это какая-то ошибка, которая либо существует, либо уже должна быть исправлена. Я использую EntityFramework Core RC2.
Решение 1. Свойства навигацииКак отметил Герт Арнольд в комментариях: используйте свойства навигации
Это означает, что (рабочий) запрос просто выглядит
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
не был в модельном классе лесов. Но использовать это простейшее решение (и работает, и даже работает быстро) вместо объединения (см. Отчеты об ошибках).
Все еще рады обновлениям, когда способ соединения работает один раз.