Select only specific fields with Linq (EF core)

Question

I have a DbContext where I would like to run a query to return only specific columns, to avoid fetching all the data.
The problem is that I would like to specify the column names with a set of strings, and I would like to obtain an IQueryable of the original type, i.e. without constructing an anonymous type.

Here is an example:

// Install-Package Microsoft.AspNetCore.All
// Install-Package Microsoft.EntityFrameworkCore

using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;

public class Person {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class TestContext : DbContext {
    public virtual DbSet<Person> Persons { get; set; }
    public TestContext(DbContextOptions<TestContext> options) : base(options) {
    }
}

class Program {
    static void Main(string[] args) {

        var builder = new DbContextOptionsBuilder<TestContext>();
        builder.UseInMemoryDatabase(Guid.NewGuid().ToString());
        var context = new TestContext(builder.Options);

        context.Persons.Add(new Person { FirstName = "John", LastName = "Doe" });
        context.SaveChanges();

        // How can I express this selecting columns with a set of strings? 
        IQueryable<Person> query = from p in context.Persons select new Person { FirstName = p.FirstName };
    }
}

I would like to have something like this method:

static IQueryable<Person> GetPersons(TestContext context, params string[] fieldsToSelect) {
    // ...
}

Is there a way I can do this?

Answer 1

Since you are projecting (selecting) the members of the type T to the same type T, the required Expression<Func<T, T>> can relatively easy be created with Expression class methods like this:

public static partial class QueryableExtensions
{
    public static IQueryable<T> SelectMembers<T>(this IQueryable<T> source, params string[] memberNames)
    {
        var parameter = Expression.Parameter(typeof(T), "e");
        var bindings = memberNames
            .Select(name => Expression.PropertyOrField(parameter, name))
            .Select(member => Expression.Bind(member.Member, member));
        var body = Expression.MemberInit(Expression.New(typeof(T)), bindings);
        var selector = Expression.Lambda<Func<T, T>>(body, parameter);
        return source.Select(selector);
    }
}

Expression.MemberInit is the expression equivalent of the new T { Member1 = x.Member1, Member2 = x.Member2, ... } C# construct.

The sample usage would be:

return context.Set<Person>().SelectMembers(fieldsToSelect);

Answer 2

This can be achieved by using Dynamic Linq.

and for .Net Core - System.Linq.Dynamic.Core

With Dynamic Linq you can pass in your SELECT and WHERE as a string.

Using your example, you could then do something like:

IQueryable<Person> query = context.Persons
                        .Select("new Person { FirstName = p.FirstName }");

Answer 3

Based on answer of Ivan I made crude version of caching function to eliminate the toll layed on us by using of reflexion. It allow as to lower this toll from milliseconds to microseconds on repeated requests (typical for DbAccess API, for example).

public static class QueryableExtensions
{
    public static IQueryable<T> SelectMembers<T>(this IQueryable<T> source, IEnumerable<string> memberNames)
    {
        var result = QueryableGenericExtensions<T>.SelectMembers(source, memberNames);
        return result;
    }
}


public static class QueryableGenericExtensions<T>
{
    private static readonly ConcurrentDictionary<string, ParameterExpression> _parameters = new();
    private static readonly ConcurrentDictionary<string, MemberAssignment> _bindings = new();
    private static readonly ConcurrentDictionary<string, Expression<Func<T, T>>> _selectors = new();

    public static IQueryable<T> SelectMembers(IQueryable<T> source, IEnumerable<string> memberNames)
    {
        var parameterName = typeof(T).FullName;

        var requestName = $"{parameterName}:{string.Join(",", memberNames.OrderBy(x => x))}";
        if (!_selectors.TryGetValue(requestName, out var selector))
        {
            if (!_parameters.TryGetValue(parameterName, out var parameter))
            {
                parameter = Expression.Parameter(typeof(T), typeof(T).Name.ToLowerInvariant());

                _ = _parameters.TryAdd(parameterName, parameter);
            }

            var bindings = memberNames
                .Select(name =>
                {
                    var memberName = $"{parameterName}:{name}";
                    if (!_bindings.TryGetValue(memberName, out var binding))
                    {
                        var member = Expression.PropertyOrField(parameter, name);
                        binding = Expression.Bind(member.Member, member);

                        _ = _bindings.TryAdd(memberName, binding);
                    }
                    return binding;
                });

            var body = Expression.MemberInit(Expression.New(typeof(T)), bindings);
            selector = Expression.Lambda<Func<T, T>>(body, parameter);

            _selectors.TryAdd(requestName, selector);
        }

        return source.Select(selector);
    }
}

Example of results after sequential run with same params (please note that this is NANOseconds):

SelectMembers time ... 3092214 ns
SelectMembers time ... 145724 ns
SelectMembers time ... 38613 ns
SelectMembers time ... 1969 ns

I have no idea why the time decreases gradually, not from "without cache" to "with cache", may be it is because of my environment with loop of questioning 4 servers with same request and some deep-level magic with asyncs. Repeating request produces consistent results similar to the last one +/- 1-2 microseconds.