EF Core Find method equivalent for multiple records?

Question

EF Core's DbSet has a method called Find that:

Finds an entity with the given primary key values. If an entity with the given primary key values is being tracked by the context, then it is returned immediately without making a request to the database. Otherwise, a query is made to the dataabse for an entity with the given primary key values and this entity, if found, is attached to the context and returned. If no entity is found, then null is returned.

I need to return multiple items based on the given array of primary key values, all in one request of course. Is there a method to do that in EF Core?

Update: I know I can use Where clause in normal scenarios. But I'm creating a helper utility that is generic, and in it I have no access to strongly-typed properties of my model. Thus I can't use Where(x => ids.Contains(x.Id)) clause.

Update 2: The desirable method can have a simple signature that gets a list of long values, and returns a list of T. public static List<T> FindSet(List<long> ids) that can be used like this:

var foundRecords = dbset.FindSet(new List<long> { 5, 17, 93, 178, 15400 });

Answer 1

As mentioned in the comments, using Find in a naive way (e.g. looping through all your key values) will end up running a query for every single value, so that’s not what you would want to do. The proper solution is to use a Where query that fetches all the items at once. The problem here is just that you need to dynamically request this for the primary key.

Of course, the database context itself does know what the primary key for a given entity type is. The way Find internally works is that it uses that information to build a dynamic query where it checks for equality on the primary key. So in order to have some FindAll, we will have to do the same.

The following is a quick solution for this. This basically builds a dbSet.Where(e => keyValues.Contains(e.<PrimaryKey>)) query for you.

Note that the way I build it, it only works for a single primary key per entity type. If you attempt to use it with compound keys, it will throw a NotSupportedException. You absolutely can expand this though to add support for compound keys; I just didn’t do that because it makes everything a lot more complex (especially since you cannot use Contains then).

public static class DbContextFindAllExtensions
{
    private static readonly MethodInfo ContainsMethod = typeof(Enumerable).GetMethods()
        .FirstOrDefault(m => m.Name == "Contains" && m.GetParameters().Length == 2)
        .MakeGenericMethod(typeof(object));

    public static Task<T[]> FindAllAsync<T>(this DbContext dbContext, params object[] keyValues)
        where T : class
    {
        var entityType = dbContext.Model.FindEntityType(typeof(T));
        var primaryKey = entityType.FindPrimaryKey();
        if (primaryKey.Properties.Count != 1)
            throw new NotSupportedException("Only a single primary key is supported");

        var pkProperty = primaryKey.Properties[0];
        var pkPropertyType = pkProperty.ClrType;

        // validate passed key values
        foreach (var keyValue in keyValues)
        {
            if (!pkPropertyType.IsAssignableFrom(keyValue.GetType()))
                throw new ArgumentException($"Key value '{keyValue}' is not of the right type");
        }

        // retrieve member info for primary key
        var pkMemberInfo = typeof(T).GetProperty(pkProperty.Name);
        if (pkMemberInfo == null)
            throw new ArgumentException("Type does not contain the primary key as an accessible property");

        // build lambda expression
        var parameter = Expression.Parameter(typeof(T), "e");
        var body = Expression.Call(null, ContainsMethod,
            Expression.Constant(keyValues),
            Expression.Convert(Expression.MakeMemberAccess(parameter, pkMemberInfo), typeof(object)));
        var predicateExpression = Expression.Lambda<Func<T, bool>>(body, parameter);

        // run query
        return dbContext.Set<T>().Where(predicateExpression).ToArrayAsync();
    }
}

Usage is like this:

// pass in params
var result = await dbContext.FindAllAsync<MyEntity>(1, 2, 3, 4);

// or an object array
var result = await dbContext.FindAllAsync<MyEntity>(new object[] { 1, 2, 3, 4 });

I also added some basic validation, so things like context.FindAllAsync<MyEntity>(1, 2, "foo") will fail early.