How to make an EF Core query using Expression?

Question

How to query a database with EF Core using Expression?

I'd like to build a dynamic query according to metadata, and query/edit data in a dynamic way, probably it could be with Expression. How could I do that?

Target:

db.Blogs.AsNoTracking().FirstOrDefault(p => p.BlogId == 1);

Code?

var funcAsNoTracking = Expression.Call(dbSet.GetType().GetMethod("AsNoTracking")!);
var funcFirstOrDefault = Expression.Call(typeof(IQueryable<>).GetMethod("FirstOrDefault")!);
/* what to do next or else? */
//Code

Other code:

public class BloggingContext : DbContext
{
     public DbSet<Blog> Blogs { get; set; }
     public DbSet<Post> Posts { get; set; }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public List<Post> Posts { get; } = new();
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

Answer 1

If you can avoid using reflection and building expression trees manually, then you should do that. I'm not saying that all problems could be solved that way, but most can. From your example;

public interface IKey {
    int Id { get; set; }
}

public class Blog : IKey
{
    public int Id { get; set; }
    // etc
}

public class Post
{
    public int Id { get; set; }
    // etc
}

public T GetItem<T>(DbContext db, int id) where T : class, IKey
    => db.Set<T>().AsNoTracking().FirstOrDefault(p => p.Id == 1);

Of course supporting fully dynamic where clauses is an entirely separate problem. But there are other existing solutions for that, like Dynamic Linq. I wouldn't recommend trying to build your own solution to that.

Answer 2

Thanks for your answer @phuzi

After a nice weekend rest, I can focus on the implementation and figure it out myself, share it hoping help others.

The conclusion, EF core could make this, still I would like try another way witch like LINQ to SQL to build dynamic solution.

Note, this is just some demo code only show the purpose, the full code is large.

Part A: Metadata

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    public Dictionary<string, object> AllDbSets
    {
        get
        {
            //TODO: setup with dynamic registration and cache
            var dic = new Dictionary<string, object>() { { nameof(Blogs), Blogs }, { nameof(Posts), Posts } };
            return dic;
        }
    }
}

The newly added AllDbSets

Part B: Dynamic search model

internal class Query
{
    public Query(List<string> fileds, List<IFilter> filters)
    {
        this.Fileds = fileds;
        this.Filters = filters;
    }

    public List<string> Fileds { get; set; }
    public List<IFilter> Filters { get; set; }
}

internal interface IFilter
{

}

internal class EqualFilter : IFilter
{
    public EqualFilter(string field, object value)
    {
        this.Filed = field;
        this.Value = value;
    }

    public string Filed { get; set; }
    public object Value { get; set; }
}

Note: Just for this demo

Part C: Query Engine process with demo

static void Main(string[] args)
{
    var query = new Query(new List<string> { "Blogs.BlogId", "Blogs.Url" }, new List<IFilter>() { new EqualFilter("Blogs.BlogId", 1) });

    var queryDBSetName = query.Fileds.Select(p => p.Split(".")[0]).Distinct().First();


    using BloggingContext db = new();
    {
        //Get DB set and basic metadata from ef core
        var set = db.AllDbSets[queryDBSetName];
        var setType = set.GetType();
        var entityType = setType.GenericTypeArguments.First();

        //AsNoTracking methodinfo
        var methodInfoAsNoTracking = typeof(EntityFrameworkQueryableExtensions).GetTypeInfo().GetDeclaredMethod(nameof(EntityFrameworkQueryableExtensions.AsNoTracking))!.MakeGenericMethod(entityType);
        //Build AsNoTracking expression
        var sourceParam = Expression.Parameter(typeof(IQueryable<>).MakeGenericType(entityType), "source");
        var asNoTrackingExpression = Expression.Call(methodInfoAsNoTracking, sourceParam);

        //FirstOrDefault methodinfo
        var methodInfoFirstOrDefault = typeof(Queryable).GetTypeInfo().DeclaredMethods.First(p => p.Name == nameof(Queryable.FirstOrDefault) && p.GetParameters().Length == 2 && p.GetParameters()[1].Name == "predicate")!.MakeGenericMethod(entityType);

        //Build property predicate expression
        var propertyInfo = entityType.GetProperty("BlogId")!;
        var param = Expression.Parameter(entityType, "p");
        var left = Expression.Property(param, propertyInfo);
        var right = Expression.Constant(1, typeof(int));

        var predicateExpression = Expression.Equal(left, right);

        //Make lambda filter expression
        Expression filterExpression = Expression.Lambda(predicateExpression, param);

        //FirstOrDefault expression with filter
        var callFirstOrDefault = Expression.Call(methodInfoFirstOrDefault, Expression.Lambda(asNoTrackingExpression, sourceParam).Body, filterExpression);

        //Make the call of expression
        var result = Expression.Lambda(callFirstOrDefault, sourceParam).Compile().DynamicInvoke(set);
    }
}

The filter from query ignored, but does not matter for demo.

Again, it is just a test demo, lots of work to make it perfect.

Answer 3

From what you've stated I think you might be over-thinking the problem.

If it's as simple as you've stated then you could just do something like.

Expression<Func<Blog, bool>> selectorExpression = (p) => p.BlogId == 1;
var blogs = db.Blogs;
Blog? result = default;

if (useAsNoTracking) // Introduce some logic that sets this.
    blogs = blogs.AsNoTracking();

if (useFirstOrDefault) // Introduce some logic that sets this too.
    result = blogs.FirstOrDefault(selectorExpression);

Things get quite a bit more complicated if you're trying to dynamically build Expressions though and I have answered a similar question here c# use string parameter to define what property to filter by in List of objects