I recently ran into a situation where I needed to generate a dynamic query using the Entity Framework.  I needed to come up with a way to simulate an “OR” type of operation.  I started down the path of trying to figure out how to generate Expression Trees, and I stumbled across a fantastic library that already does this called PredicateBuilder.

The best way to show this is through example.  I whipped up a sample database to show exactly what I mean.  This is a simple example, but hopefully you get the idea.

db

I’ve created 3 tables, one for shirts, 1 for size, and 1 for color.  I dumped some dummy data into the tables, and away we go.

After dropping in a quick entity data model and putting in some test data, I’m ready to start querying the model.

Here is a quick look at what the sample data looks like for reference:

size                  color

shirts

Now, suppose we have a user interface where the user can query this data however they want to.  How do we go about coding this?  For example, say we wanted to get the list of all shirts that were either red or yellow, regardless of size?

First, our basic UI:

ui

I just threw a quick web form together to get this data.  It looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="BlogSamples.TestWeb.Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
    <body>
        <form runat="server">
        <table>
            <tr>
                <td>Colors:</td>
            </tr>
            <tr>
                <td>
                    <asp:CheckBoxList ID="chkColors" runat="server" />
                </td>
            </tr>
            <tr><td>&nbsp;</td></tr>
            <tr>
                <td>Sizes:</td>
            </tr>
            <tr>
                <td>
                    <asp:CheckBoxList ID="chkSizes" runat="server" />
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Button ID="btnQuery" OnClick="btnQuery_OnClick" runat="server" Text="Run Query" />
                </td>
            </tr>
        </table>
        <br /><br />
        <asp:GridView ID="gridResults" runat="server" Visible="false" />
        </form>
    </body>
</html>

First, I put together a quick repository to access the data.  I needed methods to load all of the colors and sizes for the UI, and I put in methods to load each by ID so I can figure out which ones are selected.

namespace BlogExamples.Predicates
{
    public class Repository : IDisposable
    {
        protected readonly BlogSamplesEntities _context;

        public Repository() {
            _context = new BlogSamplesEntities();
        }

        public List<Color> LoadColors() {
            return _context.Colors.ToList();
        }

        public List<Size> LoadSizes() {
            return _context.Sizes.ToList();
        }

        public Color LoadColor(int ID) {
            return _context.Colors.FirstOrDefault(c => c.ColorID == ID);
        }

        public Size LoadSize(int ID) {
            return _context.Sizes.FirstOrDefault(s => s.SizeID == ID);
        }

        public void Dispose() {
            if (_context != null) {
                _context.Dispose();
            }
        }
    }
}

The next part is figuring out how to dynamically query the data set, which is where PredicateBuilder really shines.  The key difficulty is that I have no idea which color/size combinations that they are going to pick.  I can’t just chain together where clauses in the Entity Framework, because those don’t behave like “OR” operations, they are “AND” operations.  Therefore, if I check both red and yellow on the user interface and run the query, it won’t get any results because the shirt can’t be both red and yellow.

To get PredicateBuilder up and going, I first created a wrapper class so I can store which colors and sizes that the user checked.  It looks like this:

namespace BlogExamples.Predicates
{
    public class ShirtQueryParameters
    {
        public List<Size> Sizes;
        public List<Color> Colors;

        public ShirtQueryParameters() {
            Sizes = new List<Size>();
            Colors = new List<Color>();
        }
    }
}

 

This brings me to the query.  Below is the method that I inserted into the repository to allow me to perform dynamic queries.

public IQueryable<Shirt> Query(ShirtQueryParameters parameters) {

    var shirts = _context.Shirts.AsQueryable();

    // Query by size.
    if (parameters.Sizes.Count > 0) {
        var sizePredicate = PredicateBuilder.False<Shirt>();

        foreach (var size in parameters.Sizes) {
            var temp = size;
            sizePredicate = sizePredicate.Or(s => s.SizeID == temp.SizeID);
        }

        shirts = shirts.AsExpandable().Where(sizePredicate);
    }

    // Query by color.
    if (parameters.Colors.Count > 0) {
        var colorPredicate = PredicateBuilder.False<Shirt>();

        foreach (var color in parameters.Colors)
        {
            var temp = color;
            colorPredicate = colorPredicate.Or(s => s.ColorID == temp.ColorID);
        }

        shirts = shirts.AsExpandable().Where(colorPredicate);
    }

    return shirts;
}

 

I am returning an IQueryable of type Shirt from the method.  I included a reference to LinqKit.dll, which I downloaded from this page.  When performing “OR” operations, you need to initiate the predicate to PredicateBuilder.False<T>, where T is the type of data you are looking for.  As you can see, I just loop through the collections and “OR” the predicates together for any color or size that is checked. 

The next thing to point out is the AsExpandable() method that is chained to the IQueryable<Shirt>.  This is required by PredicateBuilder, as explained on the project site.  That’s really all there is to it. 

The last step is to wire up my UI, like this:

namespace BlogSamples.TestWeb
{
    public partial class Default : System.Web.UI.Page
    {
        private Repository repo;
        protected void Page_Load(object sender, EventArgs e)
        {
            repo = new Repository();

            if (!Page.IsPostBack) {
                var allColors = repo.LoadColors();
                var allSizes = repo.LoadSizes();

                foreach (var color in allColors) {
                    chkColors.Items.Add(new ListItem(color.Color1, color.ColorID.ToString()));
                }

                foreach (var size in allSizes) {
                    chkSizes.Items.Add(new ListItem(size.Size1, size.SizeID.ToString()));
                }
            }
        }

        protected void btnQuery_OnClick(object sender, EventArgs e) {
            var parms = new ShirtQueryParameters();
            foreach (ListItem item in chkColors.Items) {
                if (item.Selected) {
                    parms.Colors.Add(repo.LoadColor(Convert.ToInt32(item.Value)));
                }
            }

            foreach (ListItem item in chkSizes.Items) {
                if (item.Selected) {
                    parms.Sizes.Add(repo.LoadSize(Convert.ToInt32(item.Value)));
                }
            }

            var shirts = repo.Query(parms);
            gridResults.DataSource = shirts;
            gridResults.DataBind();
            gridResults.Visible = true;
        }
    }
}

 

I am just running the query and binding the results to a grid I put down on the page.  To test it, I ran a few queries.  The first query I wanted was to get any shirt back that was red or yellow, regardless of size.  I did this by checking the red and yellow checkboxes and hitting the Run Query button.  This gave me 4 shirts, which is what I expected when looking at the data above.

query1

Next, I wanted to perform a query that used both color and size.  I wanted to find any black shirt that was either large or 2XL.  After running this query, I got the expected 2 results back, as seen here:

query2

This project was a really nice find for me, it saved me a lot of time that I thought I was going to have to spend writing an Expression Tree generator.  You can hopefully see from this example how powerful this library is.