How to use expressions to build a LINQ query dynamically when using an interface to get the column name?

Question

I'm using Entity Framework Core to store and retrieve some data. I'm trying to write a general-purpose method that will work on any DbSet<T> in order to avoid code duplication. This method runs a LINQ query against the set, for which it needs to know the "key" column (ie. the primary key of the table).

To help with this I've defined an interface that returns the name of the property that represents the key column. Entities then implement this interface. Hence I have something like this:

interface IEntityWithKey
{
    string KeyPropertyName { get; }
}

class FooEntity : IEntityWithKey
{
    [Key] public string FooId { get; set; }
    [NotMapped] public string KeyPropertyName => nameof(FooId);
}

class BarEntity : IEntityWithKey
{
    [Key] public string BarId { get; set; }
    [NotMapped] public string KeyPropertyName => nameof(BarId);
}

The method I'm trying to write has this signature:

static List<TKey> GetMatchingKeys<TEntity, TKey>(DbSet<TEntity> dbSet, List<TKey> keysToFind)
    where TEntity : class, IEntityWithKey

Basically, given a DbSet containing entities of type TEntity, and a list of keys of type TKey, the method should return a list of keys that currently exist in the related table in the database.

The query looks like this:

dbSet.Where(BuildWhereExpression()).Select(BuildSelectExpression()).ToList()

In BuildWhereExpression I'm trying to create an appropriate Expression<Func<TEntity, bool>>, and in BuildSelectExpression I'm trying to create an appropriate Expression<Func<TEntity, TKey>>. However I'm struggling with just creating the Select() expression, which is the easier of the two. Here's what I have so far:

Expression<Func<TEntity, TKey>> BuildSelectExpression()
{
    // for a FooEntity, would be:  x => x.FooId
    // for a BarEntity, would be:  x => x.BarId

    ParameterExpression parameter = Expression.Parameter(typeof(TEntity));
    MemberExpression property1 = Expression.Property(parameter, nameof(IEntityWithKey.KeyPropertyName));
    MemberExpression property2 = Expression.Property(parameter, property1.Member as PropertyInfo);
    UnaryExpression result = Expression.Convert(property2, typeof(TKey));
    return Expression.Lambda<Func<TEntity, TKey>>(result, parameter);
}

This runs, and the query that gets passed to the database looks correct, but all I get back is a list of the key property name. For example, called like this:

List<string> keys = GetMatchingKeys(context.Foos, new List<string> { "foo3", "foo2" });

It generates this query, which looks good (note: no Where() implementation yet):

SELECT "f"."FooId"
FROM "Foos" AS "f"

But the query just returns a list containing "FooId" rather than the actual IDs stored in the database.

I feel like I am close to a solution but I'm just going around in circles a bit with the expression stuff, having not done much of it before. If anyone can help with the Select() expression that would be a start.

Here is the full code:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace StackOverflow
{
    interface IEntityWithKey
    {
        string KeyPropertyName { get; }
    }

    class FooEntity : IEntityWithKey
    {
        [Key] public string FooId { get; set; }
        [NotMapped] public string KeyPropertyName => nameof(FooId);
    }

    class BarEntity : IEntityWithKey
    {
        [Key] public string BarId { get; set; }
        [NotMapped] public string KeyPropertyName => nameof(BarId);
    }

    class TestContext : DbContext
    {
        public TestContext(DbContextOptions options) : base(options) { }
        public DbSet<FooEntity> Foos { get; set; }
        public DbSet<BarEntity> Bars { get; set; }
    }

    class Program
    {
        static async Task Main()
        {
            IServiceCollection services = new ServiceCollection();
            services.AddDbContext<TestContext>(
            options => options.UseSqlite("Data Source=./test.db"),
                contextLifetime: ServiceLifetime.Scoped,
                optionsLifetime: ServiceLifetime.Singleton);
            services.AddLogging(
                builder =>
                {
                    builder.AddConsole(c => c.IncludeScopes = true);
                    builder.AddFilter(DbLoggerCategory.Infrastructure.Name, LogLevel.Error);
                });
            IServiceProvider serviceProvider = services.BuildServiceProvider();

            var context = serviceProvider.GetService<TestContext>();
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Foos.AddRange(new FooEntity { FooId = "foo1" }, new FooEntity { FooId = "foo2" });
            context.Bars.Add(new BarEntity { BarId = "bar1" });
            await context.SaveChangesAsync();

            List<string> keys = GetMatchingKeys(context.Foos, new List<string> { "foo3", "foo2" });
            Console.WriteLine(string.Join(", ", keys));

            Console.WriteLine("DONE");
            Console.ReadKey(intercept: true);
        }

        static List<TKey> GetMatchingKeys<TEntity, TKey>(DbSet<TEntity> dbSet, List<TKey> keysToFind)
            where TEntity : class, IEntityWithKey
        {
            return dbSet
                //.Where(BuildWhereExpression())   // commented out because not working yet
                .Select(BuildSelectExpression()).ToList();

            Expression<Func<TEntity, bool>> BuildWhereExpression()
            {
                // for a FooEntity, would be:  x => keysToFind.Contains(x.FooId)
                // for a BarEntity, would be:  x => keysToFind.Contains(x.BarId)

                throw new NotImplementedException();
            }

            Expression<Func<TEntity, TKey>> BuildSelectExpression()
            {
                // for a FooEntity, would be:  x => x.FooId
                // for a BarEntity, would be:  x => x.BarId

                ParameterExpression parameter = Expression.Parameter(typeof(TEntity));
                MemberExpression property1 = Expression.Property(parameter, nameof(IEntityWithKey.KeyPropertyName));
                MemberExpression property2 = Expression.Property(parameter, property1.Member as PropertyInfo);
                UnaryExpression result = Expression.Convert(property2, typeof(TKey));
                return Expression.Lambda<Func<TEntity, TKey>>(result, parameter);
            }
        }
    }
}

This uses the following NuGet packages:

Microsoft.EntityFrameworkCore, Version 3.0.0
Microsoft.EntityFrameworkCore.Sqlite, Version 3.0.0
Microsoft.Extensions.DependencyInjection, Version 3.0.0
Microsoft.Extensions.Logging.Console, Version 3.0.0

Using interface to describe column name looks wrong, because at the time you need the property name you have no entity instance. The column name is static (type related) information and in EF Core can be obtained from model metadata - see for instance https://stackoverflow.com/questions/55867725/generic-repository-in-c-sharp-using-entity-framework/55880724#55880724 — Ivan Stoev, Nov 07 '19 at 12:00

score 3 · Accepted Answer · answered Nov 07 '19 at 12:07

In this case IEntityWithKey interface is redundant. To access KeyPropertyName value from BuildSelectExpression method you would need to have entity instance, but you have only Type object.

You can use reflection to find key property name:

Expression<Func<TEntity, TKey>> BuildSelectExpression()
{
    // Find key property
    PropertyInfo keyProperty = typeof(TEntity).GetProperties()
        .Where(p => p.GetCustomAttribute<KeyAttribute>() != null)
        .Single();

    ParameterExpression parameter = Expression.Parameter(typeof(TEntity));
    MemberExpression result = Expression.Property(parameter, keyProperty);
    // UnaryExpression result = Expression.Convert(property1, typeof(TKey)); this is also redundant
    return Expression.Lambda<Func<TEntity, TKey>>(result, parameter);
}

I'm a bit concerned about the performance hit of using reflection, although given this isn't called in a tight loop that probably won't be an issue. Thanks anyway, this got me going in the right direction. — Steven Rands, Nov 07 '19 at 14:00

score 1 · Answer 2 · answered Nov 07 '19 at 13:53

Here's the code I ended-up with for the general-purpose method:

static List<TKey> GetMatchingKeys<TEntity, TKey>(DbSet<TEntity> dbSet, List<TKey> keysToFind)
    where TEntity : class, IEntityWithKey
{
    PropertyInfo keyProperty = typeof(TEntity).GetProperties().Single(x => x.GetCustomAttribute<KeyAttribute>() != null);
    return dbSet.Where(BuildWhereExpression()).Select(BuildSelectExpression()).ToList();

    Expression<Func<TEntity, bool>> BuildWhereExpression()
    {
        ParameterExpression entity = Expression.Parameter(typeof(TEntity));
        MethodInfo containsMethod = typeof(List<TKey>).GetMethod("Contains");
        ConstantExpression keys = Expression.Constant(keysToFind);
        MemberExpression property = Expression.Property(entity, keyProperty);
        MethodCallExpression body = Expression.Call(keys, containsMethod, property);
        return Expression.Lambda<Func<TEntity, bool>>(body, entity);
    }

    Expression<Func<TEntity, TKey>> BuildSelectExpression()
    {
        ParameterExpression entity = Expression.Parameter(typeof(TEntity));
        MemberExpression body = Expression.Property(entity, keyProperty);
        return Expression.Lambda<Func<TEntity, TKey>>(body, entity);
    }
}

Ultimately there was no need for the interface, as the code can take advantage of EF Core's use of the [Key] attribute.

Thanks to @Krzysztof for pointing me in the right direction.

How to use expressions to build a LINQ query dynamically when using an interface to get the column name?

2 Answers2