c# - Complexity limits of Linq queries -

i'm big fan of linq, , have been enjoying power of expression trees etc. have found whenever try clever queries, hit kind of limitation in framework: while query can take short time run on database (as shown performance analyzer), results take ages materialize. when happens know i've been fancy, , start breaking query smaller, bite sized chunks - have solution that, though might not optimal.

but i'd understand:

• what pushes linq framework on edge in terms of materializing query results?
• where can read mechanism of materializing query results?
• is there measurable complexity limit linq queries should avoided?
• what design patterns known cause problem, , patterns can remedy it?

edit: requested in comments, here's example of query measured run on sql server in few seconds, took 2 minutes materialize. i'm not going try explaining stuff in context; it's here can view constructs , see example of i'm talking about:

expression<func<staff, teacherinfo>> teacherinfo =     st => new teacherinfo     {         id = st.id,         name = st.firstname + " " + st.lastname,         email = st.email,         phone = st.telmobile,     };  var step1 =     currentreportcards.asexpandable()         .groupjoin(db.scholarreportcards,                              current =>                              new { current.scholarid, current.academicterm.academicyearid },                              past => new { past.scholarid, past.academicterm.academicyearid },                              (current, past) => new                              {                                  current = current,                                  pastcards =                                  past.where(                                      rc =>                                      rc.academicterm.startdate <                                      current.academicterm.startdate &&                                      rc.academicterm.grade == current.academicterm.grade &&                                      rc.academicterm.schoolid == current.academicterm.schoolid)                              }); // materialization takes long time: var subjects = step1.selectmany(x => key in x.current.subjects                             .select(s => new { s.subject.subjectid, s.subject.subjectcategoryid })                             .union(x.pastcards.selectmany(c => c.subjects)                                             .select(                                                 s => new { s.subject.subjectid, s.subject.subjectcategoryid }))          join cur in x.current.subjects on key equals              new { cur.subject.subjectid, cur.subject.subjectcategoryid } jcur          cur in jcur.defaultifempty()          join past in x.pastcards.selectmany(p => p.subjects) on key equals              new { past.subject.subjectid, past.subject.subjectcategoryid } past          select new          {              x.current.scholarid,              includeincontactsection =                  // resharper disable constantnullcoalescingcondition                 (bool?)cur.subject.includeincontactsection ?? false,              includegrades = (bool?)cur.subject.includegrades ?? true,              // resharper restore constantnullcoalescingcondition              subjectname =                 cur.subject.subject.name ?? past.firstordefault().subject.subject.name,              subjectcategoryname = cur.subject.subjectcategory.description,              classinfo = (from ce in mydb.classenrollments                              .where(                                  ce =>                                  ce.class.subjectid == cur.subject.subjectid                                  && ce.scholarid == x.current.scholarid)                              .where(enrollmentexpr)                              .orderbydescending(ce => ce.terminationdate ?? datetime.today)                      let teacher = ce.class.teacher                      let secteachers = ce.class.secondaryteachers                      select new                      {                          ce.class.nickname,                          primary = teacherinfo.invoke(teacher),                          secondaries = secteachers.asqueryable().asexpandable()                             .select(ti => teacherinfo.invoke(ti))                      })                 .firstordefault(),              comments = cur.comments                  .select(cc => new                  {                      staff = cc.staff.firstname + " "                                      + cc.staff.lastname,                      comment = cc.commenttemplate.text ??                                          cc.commentfreetext                  }),              // resharper disable constantnullcoalescingcondition              displayorder = (byte?)cur.subject.displayorder ?? (byte)99,              // resharper restore constantnullcoalescingcondition              cur.percentile,              cur.score,              cur.symbol,              cur.masterylevel,              pastscores = past.select(p => new                 {                     p.score,                     p.symbol,                     p.masterylevel,                     p.scholarreportcard                      .academictermid                 }),              assessments = cur.assessments                  .select(a => new                  {                      a.scholarassessment.assessmentid,                      a.scholarassessment.assessment.description,                      a.scholarassessment.assessment.type.nickname,                      a.scholarassessment.assessmentdate,                      a.scoredesc,                      a.scoreperc,                      a.masterylevel,                      a.scholarassessment.assessment.type.assessmentformat,                      a.scholarassessment.publishedstatus,                      a.scholarassessment.fpscore,                      a.scholarassessment.totalscore,                      a.scholarassessment.assessment.type.scoretype,                      a.scholarassessment.assessment.type.overridebelowlabel,                      a.scholarassessment.assessment.type.overrideapproachinglabel,                      a.scholarassessment.assessment.type.overridemeetinglabel,                      a.scholarassessment.assessment.type.overrideexceedinglabel,                  })             })         .tolist(); 

linq uses deferred execution tasks, example while iterating through ienumerable<>, call materialization includes actual data fetching.

var reportcards = db.scholarreportcards.where(cr => ...); // prepares query  foreach (var rc in reportcards) {} // executes query , calls db 

i think if trace/time queries on sql server may see queries arriving during "materialization" step. problem may exacerbated anti-patterns such the "select n+1" problem : example looks you're not including academicterm objects in request; if don't resolving these result in select n+1, every scholarreportcard there call db lazily resolve academicterm attached.

if focus on linq db aspect, @ least try not :

• select n+1: include related datatables need
• select data: include columns need in selection (include on table need)