Entity Framework Code First – Filtering And Sorting with paging (1)

In the previous post I described a way of how to apply global filters in the DbContext, so I would like to follow up with adding some more functionality and to describe one way of how to create a generic querying and sorting mechanism. This comes really handy when using the repository pattern.

The main idea is to have a method in our repository that would be responsible for searching and sorting in a dynamic way the data so that there wouldn’t be any need of exposing the IQueriable interface to the client because this is usually not a good idea. This method could be therefore reused for any Entity in the Context. To make the thing a bit more useful, especially if the code would be used in the ASP.NET or ASP.NET MVC, I will show here one way of implementing the paging mechanism.

For the sake of simplicity the IRepository interface contains only one method that:
1. Accepts as a parameter an instance of SearchQuery that will contain the information about filters and sorting.
2. Returns a PagedListResult object that will contain the result itself and additional information about paging navigation.

Implementation of the generic Repository.

A concrete repository class implements the generic IRepository interface. There are several protected methods that are translating the SearchQuery properties into the IQueriable object. The code is pretty much self explaining:
1. Repository instantiates the ProductContext, which is the implementation of our DbContext *
2. Repository needs to reference the Entity in the ProductContext itself, and this is done through DbSet = Context.Set() statement
3. The SearchQuery is transformed into a IQueriable object and then it gets executed.

*ProductContext has been described in the previous post, and it is simply a simple implementation of the DbContext class.

SearchQuery implementation

Implementation of the SearchQuery class is quite trivial. One important thing to pay attention to is the need to use the Expression Tree statement as follow: Expression> as this is needed in order to serialize the expression itself. The code Expression> filter means the caller will provide a lambda expression based on the TEntity type, and this expression will return a Boolean value.

The sorting criteria will be explained later on, but for the time being what is important is that the SearchQuery can contain more than one Sorting Criteria.

public class SearchQuery
{
//------------------------------------------------------------
///
/// Default constructor
///
public SearchQuery()
{
Filters = new List>>();
SortCriterias = new List>();
}
//-----------------------------------------------------------
///
/// Contains a list of filters to be applied to the query.
///
public List>> Filters { get; protected set; }
//-----------------------------------------------------------
///
/// Adds a new filter to the list
///
///
public void AddFilter(Expression> filter)
{
Filters.Add(filter);
}
//-----------------------------------------------------------
///
/// Contains a list of criterias that would be used for sorting.
///
public List> SortCriterias
{
get;
protected set;
}
//-------------------------------------------------------------
///
/// Adds a Sort Criteria to the list.
///
public void AddSortCriteria(ISortCriteria sortCriteria)
{
SortCriterias.Add(sortCriteria);
}
//-------------------------------------------------------------
///
/// Contains a list of properties that would be eagerly loaded
/// with he query.
///
public string IncludeProperties { get; set; }
//-------------------------------------------------------------
///
/// Number of items to be skipped. Useful for paging.
///
public int Skip { get; set; }
//-------------------------------------------------------------
///
/// Represents the number of items to be returned by the query.
///
public int Take { get; set; }
}

PagedListResult implementation

PagedListResult represents the return object from the search. It contains the information about the result (data) itself plus some additional info useful for the paging:

1. HasNext: Does the returned result contains more rows to be retrieved?
2. HasPrevious: Does the returned result contains previous items ? For instance, when navigating the paged content, will tell us if there is any “previous” item.
3. Count: Total number of rows that could be possibly be retrieved.
4. Entities: Contains the retrieved result.

public class PagedListResult
{
//-----------------------------------------------------------
///
/// Does the returned result contains more rows to be retrieved?
///
public bool HasNext { get; set; }
//-----------------------------------------------------------
///
/// Does the returned result contains previous items ?
///
public bool HasPrevious { get; set; }
//-----------------------------------------------------------
///
/// Total number of rows that could be possibly be retrieved.
///
public long Count { get; set; }
//-----------------------------------------------------------
///
/// Result of the query.
///
public IEnumerable Entities { get; set; }
}