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?

Use reflection. It's going to get ugly and messy and you'll have a performance penalty, but other than mapping each property to a string, it's probably the only way. — Zohar Peled, Feb 06 '19 at 08:45
Even using reflection, how could I create the select statements? What I want to avoid is to fetch all the data from the DB. — Paolo Tedesco, Feb 06 '19 at 08:50
Use raw sql then map into your own DTO. We did that in the past. https://learn.microsoft.com/en-us/ef/core/querying/raw-sql — Hasan Emrah Süngü, Feb 06 '19 at 08:56
I'm pretty sure you can do this, Google Search "Dynamic Linq" this might get you on the right tracks, also take a look at this answer https://stackoverflow.com/questions/16516971/linq-dynamic-select — SimonGates, Feb 06 '19 at 08:59

score 20 · Accepted Answer · answered Feb 06 '19 at 09:15

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

Thanks, this is exactly what I needed. I was trying to do something like that based on this answer (https://stackoverflow.com/questions/12701737/expression-to-create-an-instance-with-object-initializer) but I got lost on the `Lambda` bit :) — Paolo Tedesco, Feb 06 '19 at 09:22
@Wendell As mentioned in the beginning, this approach is applicable only for projecting into the same type. Now with your comment looks like `T` cannot be anonymous type if that's what you mean. I have to admit the code doesn't work with anonymous types. — Ivan Stoev, Dec 17 '19 at 08:26

Janus Pienaar · Answer 2 · 2019-02-06T09:08:16.643

1

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

edited Feb 06 '19 at 09:08

answered Feb 06 '19 at 08:58

Janus Pienaar

1,083
7
14

1

Please give an example how this can be achieved by using Dynamic Linq – Hasan Emrah Süngü Feb 06 '19 at 08:58
1

The OP has tagged the question as .NET Core, this package is ~3 years old now and is probably just targeting full .NET. – SimonGates Feb 06 '19 at 09:06
1

There are Dynamic Linq for .Net Core too, I've updated my answer to include a link to the .net Core Nuget library – Janus Pienaar Feb 06 '19 at 09:08
Thanks. I tried this out but I couldn't find the correct syntax for the select clause. – Paolo Tedesco Feb 06 '19 at 09:24
would be interesting how the performance is, compared to a self build solution using expression trees – Josef Biehler Feb 06 '19 at 09:33

ornic · Answer 3 · 2021-05-02T17:07:13.977

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.

score 0 · Answer 4 · answered Feb 06 '19 at 09:26

Try this code:

string fieldsToSelect = "new Person { FirstName = p.FirstName }"; //Pass this as parameter.

public static IQueryable<Person> GetPersons(TestContext context, string fieldsToSelect) 
{
    IQueryable<Person> query = context.Persons.Select(fieldsToSelect);
}

score 0 · Answer 5 · answered Dec 18 '19 at 01:58

I was able to do this with the package https://github.com/StefH/System.Linq.Dynamic.Core so easily.

Here is an example code.

use namespacing, using System.Linq.Dynamic.Core;

//var selectQuery = "new(Name, Id, PresentDetails.RollNo)";

var selectQuery = "new(Name, Id, PresentDetails.GuardianDetails.Name as GuardianName)";

var students = dbContext.Students
    .Include(s => s.PresentDetails)
    .Include(s => s.PresentDetails.GuardianDetails)
    .Where(s => s.StudentStatus == "Admitted")
    .Select(selectQuery);

Tobias Lie-Atjam · Answer 6 · 2021-09-02T11:11:40.790

0

var students = dbContext.Students
    .Include(s => s.PresentDetails)
    .Where(s => s.StudentStatus == "Admitted")
    .Select(p => new Person() 
                       { 
                           Id = p.Id, 
                           Name = p.Name
                       });

Why not minimize the selected columns in the regular way? this is way cleaner.

edited Sep 02 '21 at 11:11

answered Jul 25 '20 at 08:42

Tobias Lie-Atjam

44
5

In the question, I said that I want to specify the column names with a set of strings :) – Paolo Tedesco Jul 25 '20 at 21:59

Select only specific fields with Linq (EF core)

6 Answers6

Linked