实体框架过滤器由PrimaryKey [英] Entity Framework Filter By PrimaryKey
问题描述
Get
方法,但是我有一些麻烦,因为 FindAsync
仅在 DbSet
上声明: public async virtual Task< TDTO> Get(object [] id)
{
//我想做这样的事情
var entity = await this.ApplyGetIncludes(this.GetEntityDBSet())。FindAsync(id)
return this.AdaptToDTO(entity);
}
protected virtual DbSet< TEntity> GetEntityDBSet()
{
return this._context.Set< TEntity>();
}
保护虚拟IQueryable< TEntity> ApplyGetIncludes(IQueryable< TEntity>可查询)
{
返回可查询;
}
我想像上述那样做这样的事情:
var entity = await this.ApplyGetIncludes(this.GetEntityDBSet())。FindAsync(id)
/ pre>
但是我知道这将无法正常工作,因为我们需要数据库,所以我将设置为这样做:
var entity = await this.ApplyGetIncludes(this.GetEntityDBSet()。FilterByPK(id))
.FirstOrDefaultAsync();有没有人知道如何通过主键从一个DbSet b \\ code>?
解决方案这是可能的,但该方法需要访问
DbContext
以获取描述主键的元数据。然后,它可以基于该元数据和传递的值来构建动态的谓词lambda表达式。
首先,我们需要一种收集有关实体主键属性的信息的方法。
对于EF Core,这很简单:
static IReadOnlyList< IProperty> GetPrimaryKeyProperties(DbContext dbContext,Type clrEntityType)
{
return dbContext.Model.FindEntityType(clrEntityType).FindPrimaryKey()。
}
对于EF6,这有点复杂,但仍然可行:
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();
}
现在建立谓词的方法是这样的:
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 。并且);
返回Expression.Lambda< Func< T,bool>>(body,parameter);
}
这里的棘手部分是如何让EF使用参数化查询。如果我们使用
Expression.Constant(id [i])
,则生成的SQL将使用常量值而不是参数。所以诀窍是使用持有该值的临时匿名类型的常量表达式的成员访问表达式(即属性或字段)(基本上模拟闭包)。
一旦获得从上述方法可以看出,您可以使用
FirstOrDefaultAsync
或任何其他过滤方法。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 becauseFindAsync
is only declared on aDbSet
: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();
Does anyone know how I can filter by primary key from a
DbSet
?解决方案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.
For EF Core it's simple:
static IReadOnlyList<IProperty> GetPrimaryKeyProperties(DbContext dbContext, Type clrEntityType) { return dbContext.Model.FindEntityType(clrEntityType).FindPrimaryKey().Properties; }
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); }
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).Once you obtain predicate from the above method, you can use it for
FirstOrDefaultAsync
or any other filtering method.这篇关于实体框架过滤器由PrimaryKey的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!