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