Entity Framework Filter By PrimaryKey

Question

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

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

Answer 1

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.

Answer 2

You question seems a little difficult. In my view, it is impossible to achieve you aim by a generic method to Filter by primary key to all Tables. The Id in you code above, means the keys of the Table(DBSet). And you have to treat the id differently according to different Table query. In this way, I think you`d better use a abstract method as the following to get the data

public async abstract 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);
}

You must implement your Get method according to you concrete tables(while each table usually have different primary keys).

Answer 3

I took the liberty of making some extension methods to make this easier, Currently you have to pass in the context, because it's a pain getting the context from its private field.

public static IReadOnlyList<IProperty> GetPrimaryKeyProperties<T>(this DbContext dbContext)
{
    return dbContext.Model.FindEntityType(typeof(T)).FindPrimaryKey().Properties;
}

public static Expression<Func<T, bool>> FilterByPrimaryKeyPredicate<T>(this DbContext dbContext, object[] id)
{
    var keyProperties = dbContext.GetPrimaryKeyProperties<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);
}

public static IQueryable<TEntity> FilterByPrimaryKey<TEntity>(this DbSet<TEntity> dbSet, DbContext context, object[] id)
    where TEntity : class
{
    return dbSet.Where(context.FilterByPrimaryKeyPredicate<TEntity>(id));
}