c# - How to force Entity Framework to produce more efficient SQL code? -

we using ef 6.1. despite improvements v4, there need ef in decision on how generate sql more efficient. helps use linq in our case , specify joins.

however, have case don't know how (besides circumventing ef completely):

return db.testlets.include("testtasks.testquestions.testanswers")          .include("testtasks.testquestions.testquestioncriteriongroups.testquestioncriterions")          .include("testtasks.testquestions.question.answers")          .where(x => x.testid == testid && x.shownon.hasvalue)          .tolist(); 

this produces code inefficient. in fact should enough , best if ef produced this:

select *  testlet tl inner join testtask tt on tl.guid = tt.testletid inner join testquestion tq on tt.guid = tq.testtaskid inner join testanswer ta on tq.guid = ta.testquestionid left outer join testquestioncriteriongroup tqcg on tqcg.testquestionid = tq.guid left outer join testquestioncriterion tqc on tqcg.guid = tqc.testquestioncriteriongroupid inner join question q on tq.questionid = q.questionid , q.isactive = 1 inner join answer on q.questionid = a.questionid , a.isactive = 1      tl.testid='59adfb3f-16a6-46e0-8054-7f6e83414dc9'     , tl.shownon not null 

i came point following code below (without includes in end) produced sql above, selecting columns of testlets (no includes applied, because not there , therefore no mapping ef entities) , need whole hierarchy eagerly loaded. when added includes in end, generated sql again horrible , slow:

                (from tl in                 db.testlets.where(tl => tl.testid == testid && tl.shownon.hasvalue)                 tt in db.testtasks.where(tt => tl.guid == tt.testletid)                 tq in db.testquestions.where(tq => tt.guid == tq.testtaskid)                 ta in db.testanswers.where(ta => tq.guid == ta.testquestionid)                 q in db.questions.where(q => tq.questionid == q.id)                 in db.answers.where(a => q.id == a.questionid)                 tqcg in                     db.testquestioncriteriongroups.where(tqcg => tq.guid == tqcg.testquestionid).defaultifempty()                 tqc in                     db.testquestioncriterions.where(tqc => tqcg.guid == tqc.testquestioncriteriongroupid)                         .defaultifempty()                 select tl).include("testtasks.testquestions.testanswers")                 .include("testtasks.testquestions.testquestioncriteriongroups.testquestioncriterions")                 .include("testtasks.testquestions.question.answers")  

does know how write linq2sql o entities2sql code efficient , has correct outcome? or there way of abandoning ef more complex scenarios? if how mapping ef structures (from sql joins above) in easy way?

in case wants know more how left joins: https://msdn.microsoft.com/en-us/library/bb397895.aspx

and why includes not work on when specified in query @ beginning: http://blogs.msdn.com/b/alexj/archive/2009/06/02/tip-22-how-to-make-include-really-include.aspx

update: gist generated sql: https://gist.github.com/ondrashx/d0347fc807f0f7fbdf46

break query two. load testlets, testtasks, testquestions, testanswers in 1, , remaining in second -- assuming objectcontexts have autofixups dbcontext does:

something like:

var results=db.testlets.include("testtasks.testquestions.testanswers")     .where(x => x.testid == testid && x.shownon.hasvalue)     .tolist(); 

then load children:

var questionids=results.testquestions.select(tq=>tq.guid).toarray();  db.testquestions     .include("testquestioncriteriongroups.testquestioncriterions")     .include("testtasks.testquestions.question.answers")     .where(tq=>questionids.contains(tq.guid))     .load(); 

i've never used objectcontext, dbcontext load children, , automatic fix ups of proxies in first query filled. (or should -- similar, load entire table, not select portion).

this should work if performance problem caused resultset becoming large , needing transmit , throw away redundany column data. can of course, break query further 2 queries if need, you'll need balance performance improvement transmitting/processing fewer redundant columns more round trips database.

you try (i've never done myself, looks promising... not sure if load children either)

var conn=new sqlconnection("{your sqlconnection string}"); conn.open(); var cmd=new sqlcommand("{your query}",conn); var dr=cmd.executereader(); var result=db.translate<testlets>(dr);