Добавление OrderBy в запрос EF вызывает исключение OutOfMemoryException. ⇐ C#

[Anonymous]

EDIT. Это происходит только тогда, когда я добавляю OrderBy перед проекцией. Если я добавлю его после проекции, запрос будет выполнен очень быстро и не возникнет проблем с нехваткой памяти! Я использовал Linq Pad для проверки сгенерированного SQL. Когда я делаю порядок перед проекцией, SQL становится на сотни строк длиннее и содержит гораздо больше проекций, чем после.

Вот значительно сокращенный пример сортировки перед проекцией

from contact in Contacts
orderby contact.ContactID
let DefaultAddress = contact.Addresses.FirstOrDefault(x => x.IsDefault.HasValue && x.IsDefault.Value)
select new {
ContactID = contact.ContactID,
DefaultAddressLine2 = DefaultAddress.Line2
}


И тот же пример, но с отсортированной проекцией публикации

from contact in Contacts
let DefaultAddress = contact.Addresses.FirstOrDefault(x => x.IsDefault.HasValue && x.IsDefault.Value)
select new {
ContactID = contact.ContactID,
DefaultAddressLine2 = DefaultAddress.Line2
} into x
orderby x.ContactID
select x


Второй пример приводит к прямому SELECT FROM с одним OUTER APPLY для адреса. Первый результат — два OUTER APPLY. В полной версии запроса такое же «удвоение» внешнего применения происходит экспоненциально, и в итоге я получаю сотни дополнительных заявок!

ОРИГИНАЛ – у меня есть запрос на возврат контактов вместе с их адресом по умолчанию, номером телефона и адресом электронной почты по этим строкам

from contact in Db.Contacts
select new
{
Contact = contact,
DefaultAddress = contact.Addresses.FirstOrDefault(x => x.IsDefault.HasValue && x.IsDefault.Value),
DefaultPhone = contact.Phones.FirstOrDefault(x => x.IsDefault.HasValue && x.IsDefault.Value),
DefaultEmail = contact.Emails.FirstOrDefault(x => x.IsDefault.HasValue && x.IsDefault.Value)
} into withDefaults
select new ContactWithDefaultsModel
{
ContactID = withDefaults.Contact.ContactID,

Surname = withDefaults.Contact.ESurname,
First = withDefaults.Contact.EFirst,

// other contact props

DefaultAddressLine2 = withDefaults.DefaultAddress != null ? withDefaults.DefaultAddress.Line2 : null,
DefaultAddressCityID = withDefaults.DefaultAddress != null ? withDefaults.DefaultAddress.CityID : null,
DefaultAddressStateID = withDefaults.DefaultAddress != null ? withDefaults.DefaultAddress.StateID : null,
DefaultAddressCountryID = withDefaults.DefaultAddress != null ? withDefaults.DefaultAddress.CountryID : null,
DefaultAddressZip = withDefaults.DefaultAddress != null ? withDefaults.DefaultAddress.Zip : null,

// same for default phone/email
}


Этот запрос работает нормально, но когда я добавляю OrderBy, даже для чего-то простого, например OrderBy(x => x.ContactID), запрос завершается с ошибкой OutOfMemoryException.

Из трассировки стека я вижу, что это как-то связано с компилятором плана запроса, но не могу понять, в чем причина. Вот полная трассировка стека.

at System.Text.StringBuilder.ToString()
at System.Data.Entity.Core.Metadata.Edm.EdmType.get_Identity()
at System.Data.Entity.Core.Metadata.Edm.TypeUsage.BuildIdentity(StringBuilder builder)
at System.Data.Entity.Core.Metadata.Edm.RowType.GetRowTypeIdentityFromProperties(IEnumerable`1 properties, InitializerMetadata initializerMetadata)
at System.Data.Entity.Core.Metadata.Edm.RowType..ctor(IEnumerable`1 properties, InitializerMetadata initializerMetadata)
at System.Data.Entity.Core.Metadata.Edm.TypeUsage.get_ModelTypeUsage()
at System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder.Internal.ArgumentValidation.ValidateProperty(DbExpression instance, String propertyName, Boolean ignoreCase, EdmMember& foundMember)
at System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder.DbExpressionBuilder.PropertyByName(DbExpression instance, String propertyName, Boolean ignoreCase)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.BindingScope.TryResolveVar(Var targetVar, DbExpression& resultExpr)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.ResolveVar(Var referencedVar)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.Visit(VarRefOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.VarRefOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.Visit(ComparisonOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.ComparisonOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.Visit(ConditionalOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.ConditionalOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.Visit(FilterOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.FilterOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.VisitAsRelOp(Node inputNode)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.BuildProjection(Node relOpNode, IEnumerable`1 projectionVars)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.Visit(SingleRowOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.SingleRowOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.VisitAsRelOp(Node inputNode)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.VisitApply(Node applyNode, DbExpressionKind applyKind)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.Visit(OuterApplyOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.OuterApplyOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.VisitAsRelOp(Node inputNode)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.VisitApply(Node applyNode, DbExpressionKind applyKind)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.Visit(OuterApplyOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.OuterApplyOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.VisitAsRelOp(Node inputNode)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.VisitApply(Node applyNode, DbExpressionKind applyKind)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.Visit(OuterApplyOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.OuterApplyOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.VisitAsRelOp(Node inputNode)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.VisitApply(Node applyNode, DbExpressionKind applyKind)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.Visit(OuterApplyOp op, Node n)
at System.Data.Entity.Core.Query.InternalTrees.OuterApplyOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator.VisitAsRelOp(Node input…tOp.Accept[TResultType](BasicOpVisitorOfT`1 v, Node n)
at System.Data.Entity.Core.Query.InternalTrees.BasicOpVisitorOfT`1.VisitNode(Node n)
at System.Data.Entity.Core.Query.PlanCompiler.CTreeGenerator..ctor(Command itree, Node toConvert)
at System.Data.Entity.Core.Query.PlanCompiler.ProviderCommandInfoUtils.Create(Command command, Node node)
at System.Data.Entity.Core.Query.PlanCompiler.CodeGen.Process(List`1& childCommands, ColumnMap& resultColumnMap, Int32& columnCount)
at System.Data.Entity.Core.Query.PlanCompiler.PlanCompiler.Compile(List`1& providerCommands, ColumnMap& resultColumnMap, Int32& columnCount, Set`1& entitySets)
at System.Data.Entity.Core.Query.PlanCompiler.PlanCompiler.Compile(DbCommandTree ctree, List`1& providerCommands, ColumnMap& resultColumnMap, Int32& columnCount, Set`1& entitySets)
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree, DbInterceptionContext interceptionContext, IDbDependencyResolver resolver, BridgeDataReaderFactory bridgeDataReaderFactory, ColumnMapFactory columnMapFactory)
at System.Data.Entity.Core.EntityClient.Internal.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext context, DbQueryCommandTree tree)
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, AliasGenerator aliasGenerator)
at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.c__DisplayClass7.b__6()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery`1.c__DisplayClass7.b__5()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.b__0()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)
at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject(JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, 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 System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)
at System.Net.Http.Formatting.JsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)
at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content)
at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken)
--- 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 System.Web.Http.Owin.HttpMessageHandlerAdapter.d__13.MoveNext()


Подробнее здесь: https://stackoverflow.com/questions/461 ... yexception