Complex Queries With Repository Pattern and the Entity Framework

• Sample solution and source code

When I started using the Repository pattern I noticed a problem fairly quickly. If you need more specific queries you will write new methods for each query. This can cause the repositories to get too big and doesn't exactly encourage re-use I decided to tackle this problem and see if I could come up with an appropriate solution I should mention, this solution really makes more sense for larger code bases.

I had 6 goals for this architecture:

1. Allow the re-use of filters 
2. Provide centralized sorting and paging 
3. Make sorting, paging and filters discover-able 
4. Allow the addition of new filters without touching the existing code
5. Provide all of the information so that the filtering, sorting, and paging can be done in the database. 
6. Provide separation of the entity framework classes from the client applications I didn't want to allow clients to create queries against the entity framework "ObjectContext" directly. 

To start out, I wanted to encapsulate the filters into small pieces so they would be easy to manage. So I created the following interface:

public interface IFilter<T>
{
    Expression<Func<T, bool>> GetExpression();
}
internal class NameFilter : IFilter<TaskQueryResult>
{
    private readonly string name;

    public NameFilter(string name)
    {
        this.name = name;
    }

    public Expression<Func<TaskQueryResult, bool>> GetExpression()
    {
        Expression<Func<TaskQueryResult, bool>> condition = i => i.TaskItem.task_name == name;
        return condition;
    }
}

The IFilter is responsible for providing the expression that will be used to filter the database entities when the query executes. The T type argument will allow you to provide the proper filter logic for the executing query.

One thing I learned while building this is that there are some quirks in the entity framework as far as what types of objects can be sent around in the IQueryable<t> interface. At runtime, if the entity framework isn't familiar with the types in the IQueryable<t> it whill throw an exception because the provider isn't aware of the type you are using. In order to get around that you can create a class composed of entity aware types.

Here is an example of a composed type:

internal class TaskQueryResult
{
    public task TaskItem { get; set; }
    public task_note TaskNote { get; set; }
}

The "task" and "task_note" classes are entity framework classes so I can pass them around if I need to and apply the IFilters to this class and the query that is sent the the database will be formed in a pretty normal way.

A couple other things to note are that the execution has to be done in a certain order Specifically, the ordering has to be done after the filtering. Otherwise, the ordering will be wiped out.

The way I decided to implement this was to provide access to a "QueryContext" that would allow me access everything from the repositories so the repository has a "GetQueryContext()" that will allow access to an interface which will provide all of the needed methods.

Here is an example of the "ITaskQueryContext" and the base interface "IQueryContext" along with the implementation:

public interface IQueryContext<TFilter, TModel>
{
    TFilter Ands { get; }
    TFilter Ors { get; }
}

public interface ITaskQueryContext : IQueryContext<TaskFilters, Task>
{
    IEnumerable<Task> GetTasks();
    void AddSorting(SortCriteria criteria, SortDirection direction);
}

internal class TaskQueryContext : 
    QueryContextBase<TaskQueryResult, TaskFilters, Task>, 
    ITaskQueryContext
{
    public TaskRepository repository;
    private SortCriteria criteria;
    protected SortDirection direction;

    private Dictionary<SortCriteria, Func<IQueryable<TaskQueryResult>, 
        IOrderedQueryable<TaskQueryResult>>> SortDictionary = new Dictionary<SortCriteria, 
            Func<IQueryable<TaskQueryResult>, IOrderedQueryable<TaskQueryResult>>>();

    public TaskQueryContext(TaskRepository repository)
    {
        this.repository = repository;

        SortDictionary.Add(SortCriteria.Name, 
            i => i.OrderByDirection(l => l.TaskItem.task_name, this.direction));

        SortDictionary.Add(SortCriteria.Date, 
            i => i.OrderByDirection(l => l.TaskItem.created_date, this.direction));
    }

    private TaskFilters andsField;
    public TaskFilters Ands
    {
        get { return this.andsField ?? (this.andsField = new TaskFilters(AndFilters)); }
    }

    private TaskFilters orsField;
    public TaskFilters Ors
    {
        get { return this.orsField ?? (this.orsField = new TaskFilters(OrFilters)); }
    }

    public void AddSorting(SortCriteria criteria, SortDirection direction)
    {
        this.criteria = criteria;
        this.direction = direction;
    }

    public IEnumerable<Task> GetTasks()
    {
        return this.repository.ProcessQuery(GetQuery());
    }

    protected override void ApplySorting()
    {
        var func = SortDictionary[this.criteria];
        this.FinalQuery = func(this.Query);
    }

    protected override IQueryable<TaskQueryResult> GetInitializedQuery()
    {
        return from t in repository.DbContext.tasks.AsExpandable()
               join tn in repository.DbContext.task_note on t.task_id equals tn.task_id
               select new TaskQueryResult() { TaskItem = t, TaskNote = tn };
    }
}

The nice thing about having the sorting in the implementations is that you can have different sort criteria for different query context types. This helps a lot when people are trying to figure out what they can sort by I also use a dictionary in the constructor to provide mapping for the sort criteria for the actual sort expression This allowed me to avoid using a case statement to map everything.

I also delegated the mapping from the query to back to the repository because I already have all the mapping code there for my other repository methods. The Ands and Ors allow me to take filters that I need to be put  processed separately where the the logic is basically (All ands have to be true and at least one of the ors does).

Here is an example of the TaskFilters class which is basically a wrapper to construct all of the possible IFilters for tasks:

public class TaskFilters
{
    private readonly IList<IFilter<TaskQueryResult>> filters;

    internal TaskFilters(IList<IFilter<TaskQueryResult>> filters)
    {
        this.filters = filters;
    }

    public void AddNameFilter(string name)
    {
        var filter = new NameFilter(name);
        filters.Add(filter);
    }

    public void AddCreatedDateFilter(DateTime createdDate)
    {
        var filter = new CreatedDateFilter(createdDate);
        filters.Add(filter);
    }

    public void AddTaskNoteFilter(string taskNote)
    {
        var filter = new TaskNoteFilter(taskNote);
        filters.Add(filter);
    }
}

Here is an example of using the filtering and sorting:

var queryContext = repository.GetQueryContext();
queryContext.Ors.AddNameFilter(task.Name);
queryContext.Ors.AddNameFilter(secondTask.Name);
queryContext.AddSorting(SortCriteria.Name, SortDirection.Descending);

var allTasks = queryContext.GetTasks();

As you can see the filtering is broken up into Ands and Ors which will be discover-able from the interface which makes it pretty easy to use. Then when calling "GetTasks()" it will execute the query for you and provide you with the model objects that you need sorted and filtered appropriately.

There are a lot of classes and interfaces in here to make all of this work but it is worth it in the end if you can get better control over your filtering and centralize it I hope you can find this example useful as it has helped me solve several problems.