The Code Slinger

October 28, 2008

LINQKit: PredicateBuilder<T> Goodness

Filed under: Uncategorized — Pete @ 2:52 pm
Tags: , ,

Remember the good ol’ days when you wanted to create a query that had dependencies that were unknown at runtime? You’d create a nice StringBuilder instance, and start building your own SQL to handle the complex logic of ever possible permutation that you allowed via your UI.  Or you create the hairiest stored procedure known to man with umpteen variables and then tried not only to not screw it up internally, but to make every scenario as fast as possible.  Yeah, those days were fun weren’t they?

With LINQ, such tasks are still moderately tough, but now that I’ve found the very nice PredicateBuilder<T> class that comes in the free LINQKit, the ease with which one can create dynamic SQL calls is significantly easier. 

As an example, supposed you have a table which contains a description column.  And you want to implement a sort of “smart search” on that column such that if your user types in:

Include: Dog Cat
Exclude: Hamster

you code translates that into a query such as:

SELECT * FROM Pets
WHERE Description LIKE ‘%Dog%’
AND Description LIKE ‘%Cat%’
AND Description NOT LIKE ‘%Hamster%’

Behold, PredicateBuilder<T>.

            //First get a list of keywords that match the description entered.

            string[] parts = txtInclude.Text.Split(new[] {‘ ‘});

            string[] noparts = null;

            if(txtButNot.Text.Trim().Length > 0)

                noparts = txtExclude.Text.Trim().Split(new[] {‘ ‘});

 

            var pred = PredicateBuilder.True<Pet>();

            parts.ForEach(p => pred = pred.And(pl => pl.description.Contains(p)));

            if(noparts != null)

                noparts.ForEach(p => pred = pred.And(pl => !pl.description.Contains(p)));

 

            var pets = from s in db.Pets.Where(pred)

                        select s;

Notice that it doesn’t matter what order the user types the keywords in, nor does it matter how many there are.    Obviously this is a trivial example, and I don’t really have a table that stores pet names 😉  However, I do have some pretty complex requirements to create a way to backtrack and find potential buyers in our system based on previous items they’ve shown interest in.  Sounds like a nice use for this gem of a find.

Til next time….keep on slangin!

Advertisements

2 Comments »

  1. exactly what i needed, Thanks

    Comment by Wohlfarth,Real Estate — July 1, 2009 @ 1:43 pm | Reply

  2. LINQKit is a life saver when a context class doesn’t support Contains, such as Dynamics XRM.

    Comment by nick — February 7, 2013 @ 9:25 pm | Reply


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

Blog at WordPress.com.

%d bloggers like this: