The Code Slinger

April 16, 2008

ConditionalWhere & LINQ

Filed under: Uncategorized — Pete @ 7:45 am

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,

                                                            System.Nullable<K> id,

                                                            Func<T, bool> pred) where K : struct

        {

            if (id.HasValue)

                return query.Where(pred);

            return query;

        }

 

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

          select new

            {

                obj.ID,

                obj.Display,

                obj.EnteredBy,

                obj.Note,

                obj.Timestamp,

                obj.Contact

            })

            .ConditionalWhere(start, nt => nt.Timestamp >= start.Value)

            .ConditionalWhere(end, nt => nt.Timestamp <= end.Value);

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: