passing dynamic expression to order by in code first EF repository

Question

we have written a Generic function to get the records from EF code first in a repository pattern. Rest seems to be ok but when passing an Integer to the dynamic order by , it says Cannot cast System.Int32 to System.Object

the expression is as follows:

Expression<Func<HeadOffice, object>> orderByFunc = o =>  o.Id;

if (options.sort == "Id")
{
         // this is an Integer
    orderByFunc = o => o.Id;
}
if (options.sort =="Name")
{
   // string
    orderByFunc = o => o.Name;
}
if (options.sort == "Code")
{
    orderByFunc = o => o.Code;
}

the generic method is as follows:

public virtual IEnumerable<TEntity> GetSorted<TSortedBy>(
    Expression<Func<TEntity, object>> order,
    int skip, int take, 
    params Expression<Func<TEntity, object>>[] includes)
{
    IQueryable<TEntity> query = dbSet;

    foreach (var include in includes)
    {
        query = dbSet.Include(include);
    }

    IEnumerable<TEntity> data = query.OrderBy(order).Skip(skip).Take(take).ToList();

    return data;
}

if we convert Expression<Func<TEntity, object>> to Expression<Func<TEntity, int>> then it seems to work fine with integer but consequently not with strings

any help appreciated.

Answer 1

Create a Sorter class. We also need a property-type-neutral base class:

public class SorterBase<TEntity>
{                                                               
    public abstract IEnumerable<TEntity> GetSorted( // Note, no order argument here
        int skip, int take, 
        params Expression<Func<TEntity, object>>[] includes);
}

public class Sorter<TEntity, TSortProp> : SorterBase<TEntity>
{                                                               
    private Expression<Func<TEntity, TSortProp>> _order;

    public Sorter(Expression<Func<TEntity, TSortProp>> order)
    {
        _order = order;
    }

    public override IEnumerable<TEntity> GetSorted(...)
    {
       // Use _order here ...
    }
}

Now change the sort decision to:

SorterBase<HeadOffice> sorter;
if (options.sort == "Id") {
    sorter = new Sorter<HeadOffice, int>(o => o.Id);
} else if (options.sort == "Name") {
    sorter = new Sorter<HeadOffice, string>(o => o.Name);
}
...

var result = sorter.GetSorted(skip, take, includes);

Answer 2

Maybe if you change the type of that parameter for this Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy, it could make your life easier:

public virtual IEnumerable<TEntity> GetSorted(Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy,...)
{
    IQueryable<TEntity> query = dbSet;
    //...
    if (orderBy != null)
    {
        query = orderBy(query);
    }
    //...
}

This way you can pass an Func like this:

Func<IQueryable<HeadOffice>, IOrderedQueryable<HeadOffice>> orderBy=null;
if (options.sort == "Id")
{
   orderBy= query=>query.OrderBy(o => o.Id);
}
//...

Update

Another thing that I notice now is you are not using the TSortedBy generic parameter, so, you could also do this:

public virtual IEnumerable<TEntity> GetSorted<TSortedBy>(Expression<Func<TEntity, TSortedBy>> order,
                                                         int skip, int take, 
                                                         params Expression<Func<TEntity, object>>[] includes)
{
}

But anyway I think is better use the first option and remove that generic parameter.

Answer 3

If none of the answers work for you and you must have the order expression as:

Expression<Func<TEntity,object>>

then try the following solution:

public class ExpressionHelper : ExpressionVisitor
{
    private MemberExpression m_MemberExpression;

    public MemberExpression GetPropertyAccessExpression(Expression expression)
    {
        m_MemberExpression = null;

        Visit(expression);


        return m_MemberExpression;
    }

    protected override Expression VisitMember(MemberExpression node)
    {

        var property = node.Member as PropertyInfo;

        if (property != null)
        {
            m_MemberExpression = node;
        }

        return base.VisitMember(node);

    }
}

public class DataClass<TEntity>
{
    private readonly IQueryable<TEntity> m_Queryable;

    public DataClass(IQueryable<TEntity> queryable)
    {
        m_Queryable = queryable;
    }


    public virtual IEnumerable<TEntity> GetSorted(
        Expression<Func<TEntity, object>> order,
        int skip, int take,
        params Expression<Func<TEntity, object>>[] includes)
    {

        var property_access_expression = new ExpressionHelper().GetPropertyAccessExpression(order);

        if(property_access_expression == null)
            throw new Exception("Expression is not a property access expression");

        var property_info = (PropertyInfo) property_access_expression.Member;

        var covert_method = this.GetType().GetMethod("Convert").MakeGenericMethod(property_info.PropertyType);

        var new_expression = covert_method.Invoke(this, new object[] {property_access_expression, order.Parameters });


        var get_sorted_method = this.GetType()
            .GetMethod("GetSortedSpecific")
            .MakeGenericMethod(property_info.PropertyType);


        return (IEnumerable<TEntity>)get_sorted_method.Invoke(this, new object[] { new_expression, skip, take, includes });
    }

    public virtual IEnumerable<TEntity> GetSortedSpecific<TSortedBy>(
        Expression<Func<TEntity, TSortedBy>> order,
        int skip, int take,
        params Expression<Func<TEntity, object>>[] includes)
    {

        IQueryable<TEntity> query = m_Queryable;

        //Here do your include logic and any other logic

        IEnumerable<TEntity> data = query.OrderBy(order).Skip(skip).Take(take).ToList();

        return data;
    }

    public Expression<Func<TEntity, TNewKey>> Convert<TNewKey>(
        MemberExpression expression, ReadOnlyCollection<ParameterExpression> parameter_expressions )
    {
        return Expression.Lambda<Func<TEntity, TNewKey>>(expression, parameter_expressions);
    }
}

Here is how I tested this:

    void Test()
    {
        Expression<Func<Entity, object>> exp = (x) => x.Text;

        List<Entity> entities = new List<Entity>();

        entities.Add(new Entity()
        {
            Id = 1,
            Text = "yacoub"
        });


        entities.Add(new Entity()
        {
            Id = 2,
            Text = "adam"
        });


        DataClass<Entity> data = new DataClass<Entity>(entities.AsQueryable());

        var result = data.GetSorted(exp, 0, 5, null);

    }

I tested this with both integer and string properties.

Please note that this only works for simple property access expressions.

You can enhance the GetSorted method to make this work for more complex cases.

Answer 4

One solution is to have two overloaded methods, one takes

Expression<Func<TEntity, int>>

and one takes

Expression<Func<TEntity, string>>

To minimize code duplication, extract common code (for example the query initialization statement and the for loop) to a shared method, and just let the two methods call this shared method and then invoke OrderBy on the result.