c# - Adding OrderBy to an EF query causes OutOfMemoryException -

edit - happens when add orderby before projection. if add after projection, query quick , has no out of memory problem! used linq pad check gen'ed sql. when order before projection sql hundreds of lines longer , has far more projections in when after.

here's shortened example of sorting pre projection

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 } 

and same example, sorted post projection

from contact in contacts let defaultaddress = contact.addresses.firstordefault(x => x.isdefault.hasvalue && x.isdefault.value) select new {     contactid = contact.contactid,     defaultaddressline2 = defaultaddress.line2 } x orderby x.contactid select x 

the second example results in straight select single outer apply address. first results in 2 outer apply's. in full version of query, same "doubling" of outer apply happens exponentially , end hundreds of applys!

original - have query return contacts along default address, phone number, , e-mail along these lines

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) } 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 default phone/email } 

that query works fine, when add orderby, simple orderby(x => x.contactid), query crashes outofmemoryexception.

i can see stack trace has query plan compiler, can't see cause is. here's full stack trace.

at system.text.stringbuilder.tostring() @ system.data.entity.core.metadata.edm.edmtype.get_identity() @ system.data.entity.core.metadata.edm.typeusage.buildidentity(stringbuilder builder) @ system.data.entity.core.metadata.edm.rowtype.getrowtypeidentityfromproperties(ienumerable`1 properties, initializermetadata initializermetadata) @ system.data.entity.core.metadata.edm.rowtype..ctor(ienumerable`1 properties, initializermetadata initializermetadata) @ system.data.entity.core.metadata.edm.typeusage.get_modeltypeusage() @ system.data.entity.core.common.commandtrees.expressionbuilder.internal.argumentvalidation.validateproperty(dbexpression instance, string propertyname, boolean ignorecase, edmmember& foundmember) @ system.data.entity.core.common.commandtrees.expressionbuilder.dbexpressionbuilder.propertybyname(dbexpression instance, string propertyname, boolean ignorecase) @ system.data.entity.core.query.plancompiler.ctreegenerator.bindingscope.tryresolvevar(var targetvar, dbexpression& resultexpr) @ system.data.entity.core.query.plancompiler.ctreegenerator.resolvevar(var referencedvar) @ system.data.entity.core.query.plancompiler.ctreegenerator.visit(varrefop op, node n) @ system.data.entity.core.query.internaltrees.varrefop.accept[tresulttype](basicopvisitoroft`1 v, node n) @ system.data.entity.core.query.internaltrees.basicopvisitoroft`1.visitnode(node n) @ system.data.entity.core.query.plancompiler.ctreegenerator.visit(comparisonop op, node n) @ system.data.entity.core.query.internaltrees.comparisonop.accept[tresulttype](basicopvisitoroft`1 v, node n) @ system.data.entity.core.query.internaltrees.basicopvisitoroft`1.visitnode(node n) @ system.data.entity.core.query.plancompiler.ctreegenerator.visit(conditionalop op, node n) @ system.data.entity.core.query.internaltrees.conditionalop.accept[tresulttype](basicopvisitoroft`1 v, node n) @ system.data.entity.core.query.internaltrees.basicopvisitoroft`1.visitnode(node n) @ system.data.entity.core.query.plancompiler.ctreegenerator.visit(filterop op, node n) @ system.data.entity.core.query.internaltrees.filterop.accept[tresulttype](basicopvisitoroft`1 v, node n) @ system.data.entity.core.query.internaltrees.basicopvisitoroft`1.visitnode(node n) @ system.data.entity.core.query.plancompiler.ctreegenerator.visitasrelop(node inputnode) @ system.data.entity.core.query.plancompiler.ctreegenerator.buildprojection(node relopnode, ienumerable`1 projectionvars) @ system.data.entity.core.query.plancompiler.ctreegenerator.visit(singlerowop op, node n) @ system.data.entity.core.query.internaltrees.singlerowop.accept[tresulttype](basicopvisitoroft`1 v, node n) @ system.data.entity.core.query.internaltrees.basicopvisitoroft`1.visitnode(node n) @ system.data.entity.core.query.plancompiler.ctreegenerator.visitasrelop(node inputnode) @ system.data.entity.core.query.plancompiler.ctreegenerator.visitapply(node applynode, dbexpressionkind applykind) @ system.data.entity.core.query.plancompiler.ctreegenerator.visit(outerapplyop op, node n) @ system.data.entity.core.query.internaltrees.outerapplyop.accept[tresulttype](basicopvisitoroft`1 v, node n) @ system.data.entity.core.query.internaltrees.basicopvisitoroft`1.visitnode(node n) @ system.data.entity.core.query.plancompiler.ctreegenerator.visitasrelop(node inputnode) @ system.data.entity.core.query.plancompiler.ctreegenerator.visitapply(node applynode, dbexpressionkind applykind) @ system.data.entity.core.query.plancompiler.ctreegenerator.visit(outerapplyop op, node n) @ system.data.entity.core.query.internaltrees.outerapplyop.accept[tresulttype](basicopvisitoroft`1 v, node n) @ system.data.entity.core.query.internaltrees.basicopvisitoroft`1.visitnode(node n) @ system.data.entity.core.query.plancompiler.ctreegenerator.visitasrelop(node inputnode) @ system.data.entity.core.query.plancompiler.ctreegenerator.visitapply(node applynode, dbexpressionkind applykind) @ system.data.entity.core.query.plancompiler.ctreegenerator.visit(outerapplyop op, node n) @ system.data.entity.core.query.internaltrees.outerapplyop.accept[tresulttype](basicopvisitoroft`1 v, node n) @ system.data.entity.core.query.internaltrees.basicopvisitoroft`1.visitnode(node n) @ system.data.entity.core.query.plancompiler.ctreegenerator.visitasrelop(node inputnode) @ system.data.entity.core.query.plancompiler.ctreegenerator.visitapply(node applynode, dbexpressionkind applykind) @ system.data.entity.core.query.plancompiler.ctreegenerator.visit(outerapplyop op, node n) @ system.data.entity.core.query.internaltrees.outerapplyop.accept[tresulttype](basicopvisitoroft`1 v, node n) @ system.data.entity.core.query.internaltrees.basicopvisitoroft`1.visitnode(node n) @ system.data.entity.core.query.plancompiler.ctreegenerator.visitasrelop(node input…top.accept[tresulttype](basicopvisitoroft`1 v, node n) @ system.data.entity.core.query.internaltrees.basicopvisitoroft`1.visitnode(node n) @ system.data.entity.core.query.plancompiler.ctreegenerator..ctor(command itree, node toconvert) @ system.data.entity.core.query.plancompiler.providercommandinfoutils.create(command command, node node) @ system.data.entity.core.query.plancompiler.codegen.process(list`1& childcommands, columnmap& resultcolumnmap, int32& columncount) @ system.data.entity.core.query.plancompiler.plancompiler.compile(list`1& providercommands, columnmap& resultcolumnmap, int32& columncount, set`1& entitysets) @ system.data.entity.core.query.plancompiler.plancompiler.compile(dbcommandtree ctree, list`1& providercommands, columnmap& resultcolumnmap, int32& columncount, set`1& entitysets) @ system.data.entity.core.entityclient.internal.entitycommanddefinition..ctor(dbproviderfactory storeproviderfactory, dbcommandtree commandtree, dbinterceptioncontext interceptioncontext, idbdependencyresolver resolver, bridgedatareaderfactory bridgedatareaderfactory, columnmapfactory columnmapfactory) @ system.data.entity.core.entityclient.internal.entityproviderservices.createdbcommanddefinition(dbprovidermanifest providermanifest, dbcommandtree commandtree, dbinterceptioncontext interceptioncontext) @ system.data.entity.core.common.dbproviderservices.createcommanddefinition(dbcommandtree commandtree, dbinterceptioncontext interceptioncontext) @ system.data.entity.core.objects.internal.objectqueryexecutionplanfactory.createcommanddefinition(objectcontext context, dbquerycommandtree tree) @ 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) @ system.data.entity.core.objects.elinq.elinqquerystate.getexecutionplan(nullable`1 formergeoption) @ system.data.entity.core.objects.objectquery`1.<>c__displayclass7.<getresults>b__6() @ system.data.entity.core.objects.objectcontext.executeintransaction[t](func`1 func, idbexecutionstrategy executionstrategy, boolean startlocaltransaction, boolean releaseconnectiononsuccess) @ system.data.entity.core.objects.objectquery`1.<>c__displayclass7.<getresults>b__5() @ system.data.entity.sqlserver.defaultsqlexecutionstrategy.execute[tresult](func`1 operation) @ system.data.entity.core.objects.objectquery`1.getresults(nullable`1 formergeoption) @ system.data.entity.core.objects.objectquery`1.<system.collections.generic.ienumerable<t>.getenumerator>b__0() @ system.data.entity.internal.lazyenumerator`1.movenext() @ newtonsoft.json.serialization.jsonserializerinternalwriter.serializelist(jsonwriter writer, ienumerable values, jsonarraycontract contract, jsonproperty member, jsoncontainercontract collectioncontract, jsonproperty containerproperty) @ newtonsoft.json.serialization.jsonserializerinternalwriter.serializevalue(jsonwriter writer, object value, jsoncontract valuecontract, jsonproperty member, jsoncontainercontract containercontract, jsonproperty containerproperty) @ newtonsoft.json.serialization.jsonserializerinternalwriter.serializeobject(jsonwriter writer, object value, jsonobjectcontract contract, jsonproperty member, jsoncontainercontract collectioncontract, jsonproperty containerproperty) @ newtonsoft.json.serialization.jsonserializerinternalwriter.serializevalue(jsonwriter writer, object value, jsoncontract valuecontract, jsonproperty member, jsoncontainercontract containercontract, jsonproperty containerproperty) @ newtonsoft.json.serialization.jsonserializerinternalwriter.serialize(jsonwriter jsonwriter, object value, type objecttype) @ newtonsoft.json.jsonserializer.serializeinternal(jsonwriter jsonwriter, object value, type objecttype) @ system.net.http.formatting.basejsonmediatypeformatter.writetostream(type type, object value, stream writestream, encoding effectiveencoding) @ system.net.http.formatting.jsonmediatypeformatter.writetostream(type type, object value, stream writestream, encoding effectiveencoding) @ system.net.http.formatting.basejsonmediatypeformatter.writetostream(type type, object value, stream writestream, httpcontent content) @ system.net.http.formatting.basejsonmediatypeformatter.writetostreamasync(type type, object value, stream writestream, httpcontent content, transportcontext transportcontext, cancellationtoken cancellationtoken) --- end of stack trace previous location exception thrown --- @ system.runtime.compilerservices.taskawaiter.throwfornonsuccess(task task) @ system.runtime.compilerservices.taskawaiter.handlenonsuccessanddebuggernotification(task task) @ system.web.http.owin.httpmessagehandleradapter.<bufferresponsecontentasync>d__13.movenext() 

i can't sure help, you're giving plan compiler awful lot that's not necessary. removing redundancies, query this:

from contact in db.contacts let defaultaddress = contact.addresses.firstordefault(x => x.isdefault.value) let defaultphone = contact.phones.firstordefault(x => x.isdefault.value) let defaultemail = contact.emails.firstordefault(x => x.isdefault.value) select new contactwithdefaultsmodel {     contactid = contact.contactid,     surname = contact.esurname,     first = contact.efirst,      // other contact props      defaultaddressline2 = defaultaddress.line2,     defaultaddresscityid = defaultaddress.cityid,     defaultaddressstateid = defaultaddress.stateid,     defaultaddresscountryid = defaultaddress.countryid,     defaultaddresszip = defaultaddress.zip,      // same default phone/email } 

here's changed:

• removed projection intermediate anonymous type , replaces let calls.
• removed null checks. can done safely, because entire expression translated sql, doesn't have null reference concept. in fact, sql has null propagation c# has, without explicit operator (?). leaving these null checks here them translated final sql query, they're redundant.

this should give plan compiler less code chew on , skirt around exception.