CODEWITHSUNDEEP
c#.net-coreentity-framework-core
Paolo Tedesco
Paolo Tedesco

Reputation: 57282

Select only specific fields with Linq (EF core)

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?

Upvotes: 16

Views: 20125

Answers (7)

kurnakovv
kurnakovv

Reputation: 81

My answer based on answer of Ivan and on answer of orchic.

I wrote a nuget library.

Installation:

dotnet add package Kurnakov.SmartSelector

Code example:

IQueryable<Person> query = context.Persons
    .SelectFields(new List<string>() { "FirstName", "LastName" });

More details you can find here

Upvotes: 2

Tobias Lie-Atjam
Tobias Lie-Atjam

Reputation: 42

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.

Upvotes: -1

ornic
ornic

Reputation: 382

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.

Upvotes: 1

Ajeesh Joshy
Ajeesh Joshy

Reputation: 1537

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

Upvotes: 0

Bijay Koirala
Bijay Koirala

Reputation: 242

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

Upvotes: 0

Ivan Stoev
Ivan Stoev

Reputation: 205849

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

Upvotes: 23

Janus Pienaar
Janus Pienaar

Reputation: 1103

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

Upvotes: 1

Related Questions