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

Solution 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.

Solution 2

If you want to make a generic lookup method that finds all rows matching a list of primary keys, you can achieve this by inheriting those entity types from a base class in which they share the same name for the Primary Key column.

Think about it this way: how would that method behave if your entity (database table) has a composite key? So if you can conform to this type of design, the following implementation shows a simple logic to achieve this with .NET Core. (Actually, you can achieve the same behavior with EF6 as well)

public class MyBaseEntity
{
    public int Id { get; set; }
}

public class MyTable : MyBaseEntity
{
    public string MyProperty { get; set; }
}

public static class RepositoryExtensions
{
    public static IQueryable<T> FindMatches<T>(this DbContext db, IEnumerable<int> keys)
        where T : MyBaseEntity
        => db.Set<T>().Where(x => keys.Contains(x.Id));

}

class Program
{
    static void Main(string[] args)
    {
        // Initialize your own DbContext.
        var db = new DbContext(null);
        // Usage:
        var lookupKeys = new[] { 1, 2, 3 };
        var results = db.FindMatches<MyTable>(lookupKeys).ToList();
    }
}

Comments

[poke]

Use a normal query with Where.

[poke]

“I have no access to strongly-typed properties of my model” – Then you will probably have to write some low-level helper for this. Using Find will only allow you to query one element at a time, which will be very inefficient if you have to load them all from the database.

[mohammad rostami siahgeli]

Dear @Saeid, thanks for answering. But as I wrote in the update, I can't use x.Id here. And I don't intend to use a base entity simply because of an Id field. Again thank you for answering.

[Saeid]

If you want to achieve something that dynamic, you have to look into the table metadata first before you generate the SQL statement to retrieve the values. First of all, what you're asking is a "Magic" function, which automatically finds the primary key of a table, and filters the rows base on the provided lookup values. Besides the fact that Magic code is a bad practice, it will be extremely inefficient as it has to make some round trips to the database to figure out the primary key first. Correct me if I didn't get your requirements right.

[Saeid]

Another suggestion before you give up! Look into Dynamic Linq with EF Core. Maybe it helps you build something reletively close to what you're looking for. github.com/StefH/System.Linq.Dynamic.Core

[mohammad rostami siahgeli]

I actually did it using Dynamic Linq. But I'm still interested in seeing if it's possible to be done without that library or not. Thanks for guiding.