Dynamic Entity Framework Queries with Predicate Builder

Posted by Jack Altiere on February 15th, 2012

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.

Software Development No Comments »

Ninject with MVC and ValidationAttributes

Posted by Jack Altiere on May 4th, 2010

I was recently working on an MVC application and I ran into a problem.  I was creating a ValidationAttribute and I needed to have access to my repository in this attribute.  The use case is that I want to add a release of an application, and I want to make sure that the ID being passed in is actually a valid application.  My first pass at this was to try and use property injection, like this:

public class ApplicationIDValidAttribute : ValidationAttribute
{
    [Inject]
    public IRepository<Application> AppRepo { set; private get; }

    public override bool IsValid(object value)
    {
        // Don't force required here, they can use the required attribute.
        if (value == null)
        {
            return true;
        }

        int appID;
        if (!Int32.TryParse(value.ToString(), out appID))
        {
            return false;
        }

        // This did not work, AppRepo was not injected
        var app = AppRepo.LoadApplicationById(appID);

        return (app != null);
    }
}

The problem here is that because of the way attributes are instantiated, this injection method does not work.  In the above code sample, when this gets executed my AppRepo is null.

Service Locator


The solution to my problem ended up being the use of a service locator to handle my injection.  I could have went with the Common Service Locator library, but that isn’t exactly what I want.  I already know what DI container I want to use, so completely abstracting that layer away isn’t what I need.  I ended up creating a library to handle the wiring up of Ninject, and to basically act as a wrapper to access the Ninject kernel.  

public static class Container
{
    private static IKernel _kernel;

    public static void Initialize(IKernel kernel)
    {
        _kernel = kernel;
    }

    public static T Get<T>()
    {
        return _kernel.Get<T>();
    }

    public static object Get(Type type)
    {
        return _kernel.Get(type);
    }
}


Then, I could wire up my service locator in my global.asax file, like this:

// This is in my global.asax.cs file
// Required because I inherited from NinjectHttpApplication
protected override IKernel CreateKernel()
{
    var kernel =  new StandardKernel(new DataModule());

    // Gives my wrapper class access to the kernel instance
    Container.Initialize(kernel);

    return kernel;
}

// This is my data module for reference.
public class DataModule : NinjectModule
{
    public override void Load()
    {
        Bind(typeof (IRepository<>)).To(typeof (Repository<>));
    }
}

Then, to close the loop, I can wire up my attribute like this:

public class ApplicationIDValidAttribute : ValidationAttribute
{
    public override bool IsValid(object value)
    {
        // Don't force required here, they can use the required attribute.
        if (value == null)
        {
            return true;
        }

        int appID;
        if (!Int32.TryParse(value.ToString(), out appID))
        {
            return false;
        }

        var repo = Container.Get<IRepository<Application>>();
        var app = repo.LoadApplicationById(appID);

        return (app != null);
    }
}

Don’t Abuse It


I try to only use my service locator container when I have to.  I use standard property or constructor injection whenever possible, but this does provide a nice way to be able to inject into my ValidationAttribute classes.

kick it on DotNetKicks.com

Software Development 4 Comments »
Previous Entries
Wordpress Theme by Windows Vista Security
Copyright © 2007 Jack Altiere. All rights reserved.