c# - 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.