In writing SQL we’ve all no doubt come up against scenarios in which we wanted to return data conditionally based upon some arbitrary input. There are entire case studies written about how to get the most efficiency out of the various and sundry methods employed to complete such a task.
In a great many cases, the dynamic SQL we all hate writing so much in sprocs really is the fastest and most efficient means of getting the job done. However, with the new 3.5 Framework extensions and LINQ, tasks like these become child’s play. Here’s how:
First let’s create a generic extension method that we can use for any conditional statement.
public static IEnumerable<T> ConditionalWhere<T, K>(this IEnumerable<T> query,
Func<T, bool> pred) where K : struct
Now, when we want to conditionally include (or exclude) data from a LINQ call, we simply use:
DateTime? start = null;
if (txtStartRange.Text.Length > 0)
start = DateTime.Parse(txtStartRange.Text);
DateTime? end = null;
if (txtEndRange.Text.Length > 0)
end = DateTime.Parse(txtEndRange.Text);
var nts = (from obj in db.Interests
.ConditionalWhere(start, nt => nt.Timestamp >= start.Value)
.ConditionalWhere(end, nt => nt.Timestamp <= end.Value);