How do I select specific columns from IQueryable in C#

Question

I need select some columns dynamically from a IQueryble.

Example:

IQueryable<Car> query = (from a in _dbContext.Table1
                     select new Car
                     {
                         Prop1 = a.Prop1,
                         Prop2 = a.Prop2,
                         Prop3 = a.Prop3
                     });

I need to do something like:

var lambda = new Test().CreateNewStatement<Car>("Prop1,Prop2");

I am using the function:

public Func<T, T> CreateNewStatement<T>(string fields)
    {
        var xParameter = Expression.Parameter(typeof(T), "o");
        var xNew = Expression.New(typeof(T));

        var bindings = fields.Split(',').Select(o => o.Trim())
            .Select(o =>
            {
                var mi = CustomGetType<T>(o);
                var xOriginal = Expression.Property(xParameter, mi);
                return Expression.Bind(mi, xOriginal);
            }
        );
        var xInit = Expression.MemberInit(xNew, bindings);
        var lambda = Expression.Lambda<Func<T, T>>(xInit, xParameter);
        return lambda.Compile();
    }

And the Call:

var lambda = new Test().CreateNewStatement<Car>("Prop1");
var ax = query.Select(lambda);
var result = ax.ToList();

But in database, the query is wrong:

SELECT 
[Limit1].[C1] AS [C1], 
[Limit1].[Prop1] AS [Prop1], 
[Limit1].[Prop2] AS [Prop2], 
[Limit1].[Prop3] AS [Prop3], 
FROM ( ...

The correct search in database should be:

SELECT 
[Limit1].[C1] AS [C1], 
[Limit1].[Prop1] AS [Prop1]
FROM ( ...

I need manipulate the IQueryable< T > before the search in database.

Rob · Answer 1 · 2017-02-03T14:56:25.157

The issue you're having is that you're passing a Func<User, User> into Select. This essentially means that the method is executed in memory, not in the database.

Entity Framework cannot generate SQL code from a compiled function.

When you write this:

var users = Users.Select(u => new User { a = 1 });

You're passing it an Expression<Func<User, User>> which entity framework CAN turn into SQL.

So, what you need to change is the end of your function. Instead of this:

var lambda = Expression.Lambda<Func<T, T>>(xInit, xParameter);
return lambda.Compile()

You want to simply do

return Expression.Lambda<Func<T, T>>(xInit, xParameter);

And change the return type of your function to be Expression<Func<T,T>>

HOWEVER

For whatever reason, EntityFramework does not allow you to construct objects which are entities. Linq-To-Sql does allow this, and running the code with my above changes will indeed work, and only select the one column.

Essentially this means that we need to change the expression to return a class which is not an entity used by entity framework.

So, instead of writing this:

var users = Users.Select(u => new User { a = 1 });

We need to write this:

var users = Users.Select(u => new NotAnEntityUser { a = 1 });

We then need to change our method to return a NotAnEntityUser instead of a User.

public class Test
{
    public Expression<Func<TEntityType, TModelType>> CreateNewStatement<TEntityType, TModelType>(string fields)
    {
        var xParameter = Expression.Parameter(typeof (TEntityType), "o");
        var xNew = Expression.New(typeof (TModelType));

        var bindings = fields.Split(',').Select(o => o.Trim())
            .Select(paramName =>
            {
                var xOriginal = Expression.Property(xParameter, typeof(TEntityType).GetProperty(paramName));
                return Expression.Bind(typeof(TModelType).GetProperty(paramName), xOriginal);
            }
            );
        var xInit = Expression.MemberInit(xNew, bindings);

        var lambda = Expression.Lambda<Func<TEntityType, TModelType>>(xInit, xParameter);
        return lambda;
    }
}

And define our entity and model:

public class User
{
    public virtual string Id { get; set; }
    public virtual string UserName { get; set; }
    public virtual string Salt { get; set; }

    public static implicit operator User(NotAnEntityUser o)
    {
        return new User { Id = o.Id, UserName = o.UserName, Salt = o.Salt };
    }
}
public class NotAnEntityUser
{
    public virtual string Id { get; set; }
    public virtual string UserName { get; set; }
    public virtual string Salt { get; set; }
}

Which then lets you write:

var lam = new Test().CreateNewStatement<User, NotAnEntityUser>("Id");
var c = new MyContext().Users.Select(lam).AsEnumerable().Select(u => (User)u).ToList();

And indeed, the SQL generated from this is:

SELECT 
    1 AS [C1], 
    [Extent1].[Id] AS [Id]
    FROM [dbo].[User] AS [Extent1]

Instead of writing the implicit cast operator, you could also use a tool like automapper, etc.

With automapper, you'd end up with something like:

var c = AutoMapper.Map<List<User>>(new MyContext().Users.Select(lam).ToList());

Fantastic explanation and certainly damn right. However, the solution is so complicated that I just abandoned my lambda expression and did a plain Linq To Sql! — Luis Gouveia, Jul 07 '16 at 14:18

How do I select specific columns from IQueryable in C#

1 Answers1