Geek Answers Handbook

Linq query complexity limits

I am a big fan of Linq, and I really enjoyed the power of expression trees, etc. But I found that whenever I try to work diligently with my queries, I encounter some kind of restriction in the framework: while the query can take a very short time to work in the database (as shown by the performance analyzer), the results take time to materialize. When this happens, I know that I am too bizarre, and I start breaking up the request into smaller bite-sized pieces, so I have a solution for this, although it may not always be optimal.

But I would like to understand:

  • What places the Linq structure around the edge in terms of materializing query results?
  • Where can I read about the mechanism for materializing query results?
  • Is there a definable measurable limit of complexity for Linq queries that should be avoided?
  • What design patterns are known to cause this problem, and which patterns can fix it?

EDIT:. As requested in the comments, here is an example of a query that I measured to run on SQL Server in a few seconds, but it took almost 2 minutes to materialize. I am not going to explain all things in context; this is here, so you can view the constructions and see an example of what I am saying:

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) }); // This materialization is what takes a long time: var subjects = step1.SelectMany(x => from 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 } into jcur from cur in jcur.DefaultIfEmpty() join past in x.PastCards.SelectMany(p => p.Subjects) on key equals new { past.Subject.SubjectID, past.Subject.SubjectCategoryID } into 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();
+7
c # linq linq-to-entities entity-framework
source share
1 answer

Linq uses delayed execution for some tasks, for example when iterating through IEnumerable<> , so what you call materialization includes some actual data samples.

 var reportCards = db.ScholarReportCards.Where(cr => ...); // this prepares the query foreach (var rc in reportCards) {} // this executes your query and calls the DB

I think that if you track / query time on your SQL server, you can see some queries coming in during the "materialization" stage. This problem may even be exacerbated by anti-patterns, such as the "Select N + 1" problem : for example, it looks like you are not including AcademicTerm objects in your request; if you do not allow, this will lead to the choice of N + 1, that is, for each ScholarReportCard will be a database call to lazily enable the attached AcademicTerm .

If we focus on the Linq to DB aspect, at least try:

  • select n + 1: Include related data you need
  • select too much data: include only the necessary columns in your selection ( Include in the desired table)
+1
source share

More articles:


All Articles