实体框架按 PrimaryKey 过滤 [英] Entity Framework Filter By PrimaryKey

问题描述

我正在编写一个通用的 crud 服务我正在尝试使用可选的虚拟方法来实现 Get 方法以包含属性但是我遇到了一些麻烦，因为 FindAsync 仅在 DbSet 上声明:

I'm writing a generic crud service I'm trying to implement the Get method with an optional virtual method to include properties However I'm having some trouble because FindAsync is only declared on a DbSet:

public async virtual Task<TDTO> Get(object[] id)
{
     // I want to do something like this
     var entity = await this.ApplyGetIncludes(this.GetEntityDBSet()).FindAsync(id)
     return this.AdaptToDTO(entity);
}

protected virtual DbSet<TEntity> GetEntityDBSet()
{
    return this._context.Set<TEntity>();
}

protected virtual IQueryable<TEntity> ApplyGetIncludes(IQueryable<TEntity> queryable)
{
    return queryable;
}

如上图所示，我想做这样的事情:

I want to do something like this as depicted above:

var entity = await this.ApplyGetIncludes(this.GetEntityDBSet()).FindAsync(id)

但我知道这行不通，因为我们需要数据库集，所以我会设置做这样的事情:

but I know that won't work because we need the DB set so I would setting for doing something like this:

var entity = await this.ApplyGetIncludes(this.GetEntityDBSet().FilterByPK(id))
                      .FirstOrDefaultAsync();

有谁知道我如何通过 DbSet 中的主键进行过滤?

Does anyone know how I can filter by primary key from a DbSet?

推荐答案

可以，但是该方法需要访问 DbContext 才能获取描述主键的元数据.然后它可以根据元数据和传递的值动态构建谓词 lambda 表达式.

It's possible, but the method needs access to the DbContext in order to get the metadata describing the primary key. Then it can build dynamically predicate lambda expression based on that metadata and the passed values.

首先我们需要一个收集实体主键属性信息的方法.

First we need a method which gathers information about entity primary key properties.

对于 EF Core，这很简单:

For EF Core it's simple:

static IReadOnlyList<IProperty> GetPrimaryKeyProperties(DbContext dbContext, Type clrEntityType)
{
    return dbContext.Model.FindEntityType(clrEntityType).FindPrimaryKey().Properties;
}

对于 EF6，它有点复杂，但仍然可行:

For EF6 it's a bit more complicated, but still doable:

struct KeyPropertyInfo
{
    public string Name;
    public Type ClrType;
}

public static IReadOnlyList<KeyPropertyInfo> GetPrimaryKeyProperties(DbContext dbContext, Type clrEntityType)
{
    var objectContext = ((IObjectContextAdapter)dbContext).ObjectContext;
    var metadata = objectContext.MetadataWorkspace;
    var objectItemCollection = ((ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace));
    var entityType = metadata.GetItems<EntityType>(DataSpace.OSpace)
        .Single(e => objectItemCollection.GetClrType(e) == clrEntityType);
    return entityType.KeyProperties
        .Select(p => new KeyPropertyInfo
        {
            Name = p.Name,
            ClrType = p.PrimitiveType.ClrEquivalentType
        })
        .ToList();
}

现在构建谓词的方法是这样的:

Now the method for building the predicate is like this:

static Expression<Func<T, bool>> BuildKeyPredicate<T>(DbContext dbContext, object[] id)
{
    var keyProperties = GetPrimaryKeyProperties(dbContext, typeof(T));
    var parameter = Expression.Parameter(typeof(T), "e");
    var body = keyProperties
        // e => e.PK[i] == id[i]
        .Select((p, i) => Expression.Equal(
            Expression.Property(parameter, p.Name),
            Expression.Convert(
                Expression.PropertyOrField(Expression.Constant(new { id = id[i] }), "id"),
                p.ClrType)))
        .Aggregate(Expression.AndAlso);
    return Expression.Lambda<Func<T, bool>>(body, parameter);
}

这里棘手的部分是如何让 EF 使用参数化查询.如果我们简单地使用 Expression.Constant(id[i])，生成的 SQL 将使用常量值而不是参数.所以诀窍是使用临时匿名类型的常量表达式的成员访问表达式(即属性或字段)来保存值(基本上模拟闭包).

The tricky part here is how to let EF use parameterized query. If we simply use Expression.Constant(id[i]), the generated SQL will use constant values instead of parameters. So the trick is to use member access expression (i.e. property or field) of a constant expression of temporary anonymous type holding the value (basically simulating closure).

一旦通过上述方法获得谓词，就可以将其用于FirstOrDefaultAsync或任何其他过滤方法.

Once you obtain predicate from the above method, you can use it for FirstOrDefaultAsync or any other filtering method.

这篇关于实体框架按 PrimaryKey 过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！