I’m working with Entity Framework a lot and sometimes you can get yourself tied into a knot so hard, you can’t figure out wtf is happening and why simple looking LINQ query leads to about 1K actual SQL requests. This is not good, so be careful with what you are doing there.
Today I had to untie one of these knots: a simple looking LINQ was producing horrible SQL requests and made many of them. Mostly due to poor legacy structure of the DB we have to battle at the moment. Anyway, it turned out that instead one massive LINQ to Objects, it was more performant to make 3 similar request to 3 similar tables in SQL Server. And all those requests were with similar .Where()
conditions. And it looked like this:
var holidayMovements = this.HolidayMovements
.Where (m => (startDate <= m.Start && m.Start <= endDate)
|| (startDate <= m.End && m.End <= endDate)
|| (m.Start <= startDate && endDate <= m.End));
var absenceMovements = this.AbsenceMovements
.Where (m => (startDate <= m.Start && m.Start <= endDate)
|| (startDate <= m.End && m.End <= endDate)
|| (m.Start <= startDate && endDate <= m.End));
notice that in both cases .Where()
clauses are identical? And I have 3 of these. I could live with 2 instances of the same condition, as later down the line it might turn up that 2 of the conditions are not really the same and you need to change one of them.. and if you DRY-it out into a method, you might end up with more crutches than you need without DRYing. But 3 instances are prime candidate for refactoring this out into a function/variable.
But how can you separate lambda expression into a separate variable? Like this:
Expression<Func<PersonnelMovementBase, bool>> timeBoundaryCondition =
m => (startDate <= m.Start && m.Start <= endDate)
|| (startDate <= m.End && m.End <= endDate)
|| (m.Start <= startDate && endDate <= m.End);
var holidayMovements = this.HolidayMovements
.Where (timeBoundaryCondition);
var absenceMovements = this.AbsenceMovements
.Where (timeBoundaryCondition);
Now condition is the same in the separate queries and the LINQ query looks much more sane now. Quite simple to my mind, but it took me more than 3 minutes to figure out how to create a lambda expression outside of the .Where()
, so here you go. Just don’t forget to look this up when you need similar thing again.