Efcore: Dynamic Selects at Runtime

[Jonatthu]

I am using EF Core 2.0.1

I would like to know if I am doing something wrong with the following select query, since is not selecting the properties dynamically at run-time and instead is resolving ignoring the select and just executing a ToList

```C#
dbContext.User.Select(new Func(x =>
{
dynamic userResult = new ExpandoObject();

                if (includeFirstName)
                {
                    userResult.FirstName = x.FirstName;
                }

                if (includeLastName)
                {
                    userResult.LastName = x.LastName;
                }

                if (includeUserName)
                {
                    userResult.UserName = x.UserName;
                }

                if (includeId)
                {
                    userResult.Id = x.Id;
                }

                return userResult;
            })).ToList();

```

Maybe is a bug or not, but I would like to have a way to do this, so I can support graphql, otherwise I would need to generate store procedures or something like that.

Thanks!

[tuespetre]

Func is opaque to query providers. You would need to construct an Expression<Func<>> to pass into Queryable.Select instead of Enumerable.Select. Then you can dynamically build the expression itself instead of performing dynamic operations in the expression, which will have the benefit of being cacheable by the query provider.

For instance, make a User parameter with Expression.Parameter(typeof(User)). Make a list of MemberAssignments with Expression.Bind(userNameProperty, Expression.Property(userParameter, userNameProperty)). You can dynamically add such bindings to the list. Then create your actual ‘object’ with Expression.MemberInit(Expression.New(typeof(User)), memberAssignments). Finally, create your lambda expression with Expression.Lambda(memberInitExpression, userParameter) and cast it to Expression<Func<User,User>>. Pass that to Select.

If you want to get really dynamic you can come up with all sorts of crazy expressions to build.

[Jonatthu]

@tuespetre I will create this expressions with a generator :), Could you give me an example with two or three properties? I do not understand exactly how would look an implementation of this

[tuespetre]

Sure, I am on my phone but later I can type on a keyboard.

[tuespetre]

@Jonatthu

Something like this:

var userParameter = Expression.Parameter(typeof(User), "u");
var bindings = new List<MemberAssignment>();

if (includeFirstName)
{
    var firstNameProperty 
        = typeof(User)
            .GetProperty(nameof(User.FirstName));

    bindings.Add(
        Expression.Bind(
            firstNameProperty, 
            Expression.Property(
                userParameter, 
                firstNameProperty)));
}

if (includeLastName)
{
    var lastNameProperty 
        = typeof(User)
            .GetProperty(nameof(User.LastName));

    bindings.Add(
        Expression.Bind(
            lastNameProperty, 
            Expression.Property(
                userParameter, 
                lastNameProperty)));
}

// and so forth, conditionally binding any more members you want to select

var memberInit = Expression.MemberInit(Expression.New(typeof(User)), bindings);
var lambda = Expression.Lambda<Func<User,User>>(memberInit, userParameter);

var results = dbContext.Users.Select(lambda).ToList();

[ajcvickers]

@tuespetre Thanks for answering!

[Jonatthu]

@tuespetre Thanks a lot, I really appreciate this, I will test this as soon as possible,
Is there any performance issue using this for every single request on a high demand site?

[tuespetre]

@Jonatthu this is exactly what LINQ queries compile to (building expression trees), only difference here is you skip the ‘sugar’ step to insert conditional branches. EF will cache the distinct query plans still.

[Jonatthu]

@tuespetre Nice I am generating all this selects for a graphql implementation, so devs will never write this selects :) This can be useful even for OData or normal REST

[Jonatthu]

@tuespetre
Looks like creating expressions or selects this way performs even better.
Is there any other way to have performance gains in this selects?

Generated:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "u"."Id", "u"."FirstName", "u"."LastName"
      FROM "User" AS "u"
First Time: 197336
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "x"."FirstName", "x"."LastName", "x"."Id"
      FROM "User" AS "x"
First Time: 211451

                bool includeId = true;
                bool includeFirstName = true;
                bool includeLastName = true;

                Stopwatch stopwatch = new Stopwatch();

                stopwatch.Start();
                var userParameter = Expression.Parameter(typeof(User), "u");
                var bindings = new List<MemberAssignment>();

                if (includeId)
                {
                    var idName = typeof(User).GetProperty(nameof(User.Id));

                    bindings.Add(Expression.Bind(idName, Expression.Property(userParameter, idName)));
                }

                if (includeFirstName)
                {
                    var firstNameProperty
                        = typeof(User)
                            .GetProperty(nameof(User.FirstName));

                    bindings.Add(Expression.Bind(
                            firstNameProperty,
                            Expression.Property(
                                userParameter,
                                firstNameProperty)));
                }

                if (includeLastName)
                {
                    var lastNameProperty
                        = typeof(User)
                            .GetProperty(nameof(User.LastName));

                    bindings.Add(
                        Expression.Bind(
                            lastNameProperty,
                            Expression.Property(
                                userParameter,
                                lastNameProperty)));
                }

                // and so forth, conditionally binding any more members you want to select

                var memberInit = Expression.MemberInit(Expression.New(typeof(User)), bindings);
                var lambda = Expression.Lambda<Func<User, User>>(memberInit, userParameter);

                var results = dbContext.User.Select(lambda).ToList();
                stopwatch.Stop();
                Console.WriteLine($"First Time: {stopwatch.ElapsedTicks}");

                stopwatch.Start();
                results = dbContext.User.Select(x => new User { FirstName = x.FirstName, LastName = x.LastName, Id = x.Id }).ToList();
                stopwatch.Stop();
                Console.WriteLine($"First Time: {stopwatch.ElapsedTicks}");
            }

[tuespetre]

I don’t think running a single pass like that and comparing their ticks is going to be a faithful benchmark in itself. But one thing you could do is wrap your query building code in a caching block — that is, take the parameters that ‘identify’ the query and hash them like (includeFirstName, includeLastName).GetHashCode() and use that as the key to a dictionary lookup. If the entry is not in the dictionary, build the query, compile it with CompiledQuery, and cache it. Otherwise, use the cached query.

I wouldn’t bother until you can really see a difference though.