.NET Repository pattern generic Query Methodology

Question

I am using Repository pattern and i have a base repository and i use Entity Framework and web api; my question is I want to allow my clients to be able to query any data dynamically; something like query expressions and fetch xml used in Dynamics CRM; I tried specification pattern but it isn't enough as i want to allow client code to order data with different columns e.g. Name asc Address desc and also, allow pagination on the returned result;so my method requirements are

1. Generic Filter that doesn't depend on ORM technology as i may change Entity Framework in the future
2. Generic Sorting methodology; allow multiple sort columns e.g. Name asc Address desc
3. The Client determine the returned columns or all columns e.g. columnSet to be "Address, Name and Id" or returned the entire record
4. Allow Pagination e.g. Page Index and Page Size
5. Support threshold on the number of returned records as large result may affect performance

this is my initial method but i don't know if it is better or not

IList<TEntity> AllMatching(ISpecification<TEntity> specification = null,
                Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
                IList<Expression<Func<TEntity, object>>> includes = null,
                int? pageIndex = null, int? pageCount = null);

Answer 1

You can start doing this:

 public IQueryable<TEntity> Select(
 Expression<Func<TEntity, bool>> filter = null,
 Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
 IList<Expression<Func<TEntity, object>>> includes = null,
 int? page = null,
 int? pageSize = null)
 {
        IQueryable<TEntity> query = _dbSet;

        if (includes != null)
        {
            query = includes.Aggregate(query, (current, include) => current.Include(include));
        }
        if (orderBy != null)
        {
            query = orderBy(query);
        }
        if (filter != null)
        {
            query = query.AsExpandable().Where(filter);
        }
        if (page != null && pageSize != null)
        {
            query = query.Skip((page.Value - 1)*pageSize.Value).Take(pageSize.Value);
        }
        return query;
 }

As you can see, it almost do all that you need, so, if you want to do the same in your Repository implementation, you must use the LinqKit nuget package.

If you want to select specific columns , you can create another method as I show below:

IEnumerable<TResult> AllMatching<TResult>(
Expression<Func<TEntity, TResult>> columns,
Expression<Func<TEntity, bool>> filter = null,
Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
IList<Expression<Func<TEntity, object>>> includes = null,
int? pageIndex = null,
int? pageCount = null)
{
  var query=Select(filter,orderby,includes,page,pageSize);
  return  query.Select(columns);

}