Home > .net 3.5, c#, LINQ, Visual Studio 2008 > Trimming the If/Then’s With LINQ

Trimming the If/Then’s With LINQ

July 26, 2007

I have a method that I’ve been dinking with this morning to get it a) to output exactly what I want from about five dimensions of data and b) for it to not take hours to process.  Hours to process is bad (I try not to live up to the expectations of public sector).

The original LINQ query looked like:

var incidents = from i in db.Incidents

where (i.SchoolYear == schoolYear)

       select i;

 

foreach (Enum item in Enum.GetValues(typeof(Schools.Level)))

{

 […]

foreach (var incidentType in db.IncidentTypes)

       {

         […] // Giving you an idea of some of the loops.

 

foreach (Incident inc in incidents)

{

if (inc.IncidentTypeId ==

incidentType.IncidentTypeId &&

Schools.GetSchool(inc.SchoolId).SchoolLevel ==

(Schools.Level)item)

              count += 1;

}

       }

}

Simple enough.  Capture the incidents (records), loop through them all and go.  But… can I cut down the lines with Lambda expressions and a bit better coding?  Hope so or this blog entry will be really short.

Now, I could narrow out that incidents variable with:

foreach (Incident inc in db.Incidents.Where(i => i.SchoolYear == schoolYear))

{

if (inc.IncidentTypeId == incidentType.IncidentTypeId &&

       Schools.GetSchool(inc.SchoolId).SchoolLevel == (Schools.Level)item)

       count += 1;

}

or even further with:

foreach (Incident inc in db.Incidents

.Where(i => (i.SchoolYear == schoolYear) &&

              (i.IncidentTypeId == incidentType.IncidentTypeId)))

{

if (Schools.GetSchool(inc.SchoolId).SchoolLevel == (Schools.Level)item)

              count += 1;

}

But, after looking at how the operation ran, it seemed to drag on.  I’m assuming it was querying the database each time rather than, with the var, querying the data in memory.  Bad deal.  Separating it out into a var BEFORE the entire grouping of foreach’s is a good thing for memory and database performance (or seems to be).

foreach (Incident inc in incidents

.Where(i => (i.IncidentTypeId == incidentType.IncidentTypeId)))

{

if (Schools.GetSchool(inc.SchoolId).SchoolLevel == (Schools.Level)item)

              count += 1;

}

Here, using our existing var (one call, save us some performance bits) and narrowing down without the if statement, helping remove the number of loops through the statement.  Now the report comes up and runs tremendously fast.  From here, we’ll populate an ActiveReport and be good!

On a final note, what’s a WISH?  I wish I could add an “order of operations” to LINQ expressions and that it wouldn’t try to render methods as SQL (giving the annoying “method has no supported SQL translation” error).  If that were the case, I could simply get my count with:

count = db.Incidents

.Where(i =>

(i.IncidentTypeId == incidentType.IncidentTypeId) &&

              (Schools.GetSchool(i.SchoolId).SchoolLevel == (Schools.Level)item))

       .Count();

*dreams*

 

Comments are closed.
%d bloggers like this: