Dynamically Select Columns in Runtime Using Entity Framework

Dynamically select columns in runtime using entity framework

This might help to solve your problem:

public int sFunc(string sCol, int iId)
{
    var _tableRepository = TableRepository.Entities.Where(x => x.ID == iId).Select(e => e).FirstOrDefault();
    if (_tableRepository == null) return 0;

    var _value = _tableRepository.GetType().GetProperties().Where(a => a.Name == sCol).Select(p => p.GetValue(_tableRepository, null)).FirstOrDefault();

    return _value != null ? Convert.ToInt32(_value.ToString()) : 0;
}

This method now work for dynamically input method parameter sCol.

Update:
This is not in context of current question but in general how we can select dynamic column using expression:

var parameter = Expression.Parameter(typeof(EntityTable));
var property = Expression.Property(parameter, "ColumnName");
//Replace string with type of ColumnName and entity table name.
var selector = Expression.Lambda<Func<EntityTable, string>>(property, parameter);

//Before using queryable you can include where clause with it. ToList can be avoided if need to build further query.
var result = queryable.Select(selector).ToList();

Dynamically build select list from linq to entities query

Dynamic select expression to a compile time known type can easily be build using Expression.MemberInit method with MemberBindings created using the Expression.Bind method.

Here is a custom extension method that does that:

public static class QueryableExtensions
{
    public static IQueryable<TResult> Select<TResult>(this IQueryable source, string[] columns)
    {
        var sourceType = source.ElementType;
        var resultType = typeof(TResult);
        var parameter = Expression.Parameter(sourceType, "e");
        var bindings = columns.Select(column => Expression.Bind(
            resultType.GetProperty(column), Expression.PropertyOrField(parameter, column)));
        var body = Expression.MemberInit(Expression.New(resultType), bindings);
        var selector = Expression.Lambda(body, parameter);
        return source.Provider.CreateQuery<TResult>(
            Expression.Call(typeof(Queryable), "Select", new Type[] { sourceType, resultType },
                source.Expression, Expression.Quote(selector)));
    }
}

The only problem is what is the TResult type. In EF Core you can pass the entity type (like EntityModel.Core.User in your example) and it will work. In EF 6 and earlier, you need a separate non entity type because otherwise you'll get NotSupportedException - The entity or complex type cannot be constructed in a LINQ to Entities query.

UPDATE: If you want a to get rid of the string columns, I can suggest you replacing the extension method with the following class:

public class SelectList<TSource>
{
    private List<MemberInfo> members = new List<MemberInfo>();
    public SelectList<TSource> Add<TValue>(Expression<Func<TSource, TValue>> selector)
    {
        var member = ((MemberExpression)selector.Body).Member;
        members.Add(member);
        return this;
    }
    public IQueryable<TResult> Select<TResult>(IQueryable<TSource> source)
    {
        var sourceType = typeof(TSource);
        var resultType = typeof(TResult);
        var parameter = Expression.Parameter(sourceType, "e");
        var bindings = members.Select(member => Expression.Bind(
            resultType.GetProperty(member.Name), Expression.MakeMemberAccess(parameter, member)));
        var body = Expression.MemberInit(Expression.New(resultType), bindings);
        var selector = Expression.Lambda<Func<TSource, TResult>>(body, parameter);
        return source.Select(selector);
    }
}

with sample usage:

var selectList = new SelectList<EntityModel.Core.User>();
selectList.Add(e => e.UserType);
selectList.Add(e => e.Name);

var selectResult = selectList.Select<UserDto>(entities);

Dynamically Create GroupBy and Select with EF

You could build an expression tree and create linq statements dynamically.

In this post, check Jorrit Steetskamp's answer to create an expression tree for group by and its usage.

You can similarly do something for select statement also as this post suggests.

Another option could be ESQL (Entity Sql) which uses more like a sql syntax to execute a query against the DB. Check this for example.

---

Related Topics