c# - 使用接口(interface)获取列名时如何使用表达式动态构建LINQ查询？

Question

我正在使用 Entity Framework Core 来存储和检索一些数据。我正在尝试编写一个适用于任何 DbSet<T> 的通用方法为了避免代码重复。此方法针对集合运行 LINQ 查询，为此它需要知道“键”列(即表的主键)。

为了解决这个问题，我定义了一个接口(interface)，它返回表示键列的属性的名称。然后实体实现这个接口(interface)。因此我有这样的东西:

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

我尝试编写的方法具有以下签名:

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

基本上，给定一个包含 TEntity 类型的实体的 DbSet 和一个 TKey 类型的键列表，该方法应该返回当前存在于数据库中的相关表。

查询看起来像这样:

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

在BuildWhereExpression我正在尝试创建一个合适的 Expression<Func<TEntity, bool>> ，并在 BuildSelectExpression我正在尝试创建一个合适的 Expression<Func<TEntity, TKey>> .但是，我正在为创建 Select() 表达式而苦苦挣扎，这是两者中更容易的一个。这是我到目前为止所拥有的:

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

这会运行，传递给数据库的查询看起来是正确的，但我得到的只是一个关键属性名称的列表。例如，这样调用:

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

它生成这个查询，看起来不错(注意:还没有 Where() 实现):

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

但是查询只返回一个包含“FooId”的列表，而不是存储在数据库中的实际 ID。

我觉得我已经接近解决方案了，但我只是在围绕表达式的东西转了一圈，之前没有做过太多。如果有人可以帮助解决 Select() 表达式，那将是一个开始。

完整代码如下:

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

这使用以下 NuGet 包:

• Microsoft.EntityFrameworkCore，版本 3.0.0
• Microsoft.EntityFrameworkCore.Sqlite，版本 3.0.0
• Microsoft.Extensions.DependencyInjection，版本 3.0.0
• Microsoft.Extensions.Logging.Console，版本 3.0.0

Answer 1

在这种情况下，IEntityWithKey 接口(interface)是多余的。要从 BuildSelectExpression 方法访问 KeyPropertyName 值，您需要有实体实例，但您只有 Type 对象。

您可以使用反射来查找关键属性名称:

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