Dynamically build select list from linq to entities query

Question

I'm looking for a way to dynamically create a select list from a iQueryable object.

Concrete example, i want to do something like the following:

public void CreateSelectList(IQueryable(of EntityModel.Core.User entities), string[] columns)
{
    foreach(var columnID in columns)
    {
        switch(columnID)
        {
            case "Type":
                SelectList.add(e => e.UserType);
                break;
            case "Name":
                SelectList.add(e => e.Name);
                break;
            etc....
        }
    }
    var selectResult = (from u in entities select objSelectList);
}

So all properties are known, i however don't know beforehand what properties are to be selected. That will be passed via the columns parameter.

I know i'm going to run into issues with the type of the selectResult type, because when the select list is dynamic, the compiler doesn't know what the properties of the anonymous type needs to be.

If the above is not possible: The scenario I need it for is the following:
I'm trying to create a class that can be implemented to display a paged/filtered list of data. This data can be anything (depends on the implementations).The linq used is linq to entities. So they are directly linked to sql data. Now i want to only select the columns of the entities that i am actually showing in the list. Therefore i want the select to be dynamic. My entity might have a hundred properties, but if only 3 of them are shown in the list, i don't want to generate a query that selects the data of all 100 columns and then only uses 3 of them. If there is a different approach that I haven't thought of, I'm open to ideas

Edit:

Some clarifications on the contraints:
- The query needs to work with linq to entities (see question subject)
- an entity might contain 100 columns, so selecting ALL columns and then only reading the ones i need is not an option.
- The end user decides what columns to show, so the columns to select are determined at run time
- i need to create a SINGLE select, having multiple select statements means having multiple queries on the database, which i don't want

Answer 1

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);

Answer 2

What you are going for is possible, but it's not simple. You can dynamically build EF queries using the methods and classes in the System.Linq.Expressions namespace.

See this question for a good example of how you can dynamically build your Select expression.

Answer 3

I believe this is what you need:

var entities = new List<User>();

entities.Add(new User { Name = "First", Type = "TypeA" });
entities.Add(new User { Name = "Second", Type = "TypeB" });

string[] columns = { "Name", "Type" };

var selectResult = new List<string>();

foreach (var columnID in columns)
{
    selectResult.AddRange(entities.Select(e => e.GetType().GetProperty(columnID).GetValue(e, null).ToString()));
}

foreach (var result in selectResult)
{
    Console.WriteLine(result);
}

This code outputs:

• First
• Second
• TypeA
• TypeB

UPDATE (according to comments)

// initialize alist of entities (User)
var entities = new List<User>();
entities.Add(new User { Name = "First", Type = "TypeA", SomeOtherField="abc" });
entities.Add(new User { Name = "Second", Type = "TypeB", SomeOtherField = "xyz" });

// set the wanted fields
string[] columns = { "Name", "Type" };

// create a set of properties of the User class by the set of wanted fields
var properties = typeof(User).GetProperties()
                        .Where(p => columns.Contains(p.Name))
                        .ToList();

// Get it with a single select (by use of the Dynamic object)
var selectResult = entities.Select(e =>
{
    dynamic x = new ExpandoObject();
    var temp = x as IDictionary<string, Object>;
    foreach (var property in properties)
        temp.Add(property.Name, property.GetValue(e));
    return x;
});

// itterate the results
foreach (var result in selectResult)
{
    Console.WriteLine(result.Name);
    Console.WriteLine(result.Type);
}

This code outputs:

• First
• TypeA
• Second
• TypeB