EF Core dynamic OR query from array

Question

I have an API and would like to query based on an array, but the number of parameters could be 1 or 1000.

Query URL:

https://localhost:1000/api/?uid=1a3d426d-2d37-43fd-b781-a7230235ba0f&names=John;Manuel;George;

What I am doing is splitting the string names in to an array and trying to use the array to query the table

string[] namesarray = names..Split(';');

What I want to do is return all employees on the company which contain each of the names using EF Core.

Answer 1

If you want an exact match on name, then this will do it:

var companies = context.Companies
    .Where(c => namesarray.Contains(c.Name))
    .ToList();

This will generate SQL that looks something like this:

SELECT Id, Name...
FROM Companies
WHERE Name IN ('John', 'Manuel', 'George')

Alternatively of you want a partial match then you will need to build your expression manually since a query in the form of .Any(... .Contains ) isn't supported in EF Core yet. If will however work in EF6, but this isn't of use to you. To do that, see this answer. For example:

private Expression<Func<Accounts, bool>> BuildOrSearchExpression(string[] searchWords)
{
    var expressions = searchWords
        .Select(s => (Expression<Func<Accounts, bool>>)(p => p.Name.Contains(s)))
        .ToList();

    if (expressions.Count == 1) return expressions[0];

    var orExpression = expressions.Skip(2).Aggregate(
        Expression.OrElse(expressions[0].Body, Expression.Invoke(expressions[1], expressions[0].Parameters[0])),
        (x, y) => Expression.OrElse(x, Expression.Invoke(y, expressions[0].Parameters[0])));

    return Expression.Lambda<Func<Accounts, bool>>(orExpression, expressions[0].Parameters);
}

And use like this:

var companies = context.Companies
    .Where(BuildOrSearchExpression(namesarray))
    .ToList();