Exception when trying to make a dynamic database call

Question

I have a group class that contains members, which can be of different class types.

All other functionality regarding this works, I am only missing the database call, which I have run out of ideas on.

My code is as follows:

response = group.MapEntity<GroupDataResponse>(); //Irrelevant for this question (kept due to comment about it).

var resourceType = group.GetResourceType();

var functionType = typeof(Func<,>).MakeGenericType(typeof(IGroupMember), typeof(bool));

var expressionType = typeof(Expression<>).MakeGenericType(functionType);

var contextSetMethod = _context.GetType().GetMethod("Set", genericParameterCount: 1, Array.Empty<Type>())!.MakeGenericMethod(resourceType);

var dbSetMethod = typeof(Enumerable).GetMethods().FirstOrDefault(x => x.Name == "SingleOrDefault")!.MakeGenericMethod(expressionType);
            
var dynamicContext = contextSetMethod.Invoke(_context, null);
            
foreach (var member in group.Members)
{
    var func = BuildExpression(member.MemberId);
    var result = dbSetMethod.Invoke(dynamicContext, new object?[] { func });
                
    if (result is null || result.GetType() != resourceType)
        throw new Exception($"Something went wrong!!!!! {result}");
                
    members.Add((IGroupMember)result);
}
response.Members = members;

The exception happens on dbSetMethod.Invoke(...)

Exception:

System.ArgumentException: Object of type 'System.Linq.Expressions.Expression1'1[System.Func'2[Trasolu.Domain.Common.IGroupMember,System.Boolean]]' cannot be converted to type 'System.Collections.Generic.IEnumerable'1[System.Linq.Expressions.Expression'1[System.Func`2[Trasolu.Domain.Common.IGroupMember,System.Boolean]]]'.

What I want, is to make a dynamic call to the database, the func in the loop, is a lambda expression, that I want to run on the dbSetMethod to make sure i only get the entity with the given id.

This is my function i try to run.

private Func<IGroupMember, bool> BuildExpression(int id)
{
    Expression<Func<IGroupMember, int>> queryFunction = x => x.Id;
    var paramExpr = Expression.Parameter(typeof(IGroupMember));
    var varExpr = Expression.Constant(id, typeof(int));
    var equalExpr = Expression.Equal(
        left: Expression.Invoke(queryFunction, paramExpr),
        right: varExpr);
    var lambda = Expression.Lambda<Func<IGroupMember, bool>>(equalExpr, paramExpr);
    return lambda.Compile();
}

Answer 1

EF Core doesn't support parsing arbitrary compiled functions. It can only parse normal expression trees.

You need to first create the expression in the normal way. The easiest way to do this is using generics.

private Expression<Func<T, bool>> BuildExpression<T>(int id) where T : IGroupMember
{
    Expression<Func<IGroupMember, bool>> queryFunction = x => x.Id == id;
    return queryFunction;
}

Then you can use another function to call that. You can also combine these two into a single function if you wish. Note that the expression is not compiled, or composed over, it's passed direct to SingleOrDefault.

private static IGroupMember GetMember<T>(DbContext dbContext, int id) where T : IGroupMember
{
    return dbContext.Set<T>().SingleOrDefault(BuildExpression(id));
}

Finally call that function dynamically. You now only need to construct a single dynamic call, rather than the whole chain.

var func = this.GetType()
    .GetMethod(nameof(GetMember), BindingFlags.Static | BindingFlags.NonPublic)
    .MakeGenericMethod(resourceType)
    .CreateDelegate<Func<DbContext, int, IGroupMember>>(this);
            
foreach (var member in group.Members)
{
    var result = func.Invoke(_context, member.MemberId);
    members.Add(result);
}
response.Members = members;

Note however, that looping like this is inefficient. It's probably better for the database if you just give it a list in a Table Valued Paramter or similar and then join it to get multiple results.

For example, on SQL Server, you can create a Table Type. It's useful to have a few standard one and two column types of common columns.

CREATE TYPE dbo.IntList (Value int PRIMARY KEY);

Then create a DataTable and push it in as a TVP via FromSql. Note that the FromSql cannot be pushed into a single LINQ query, you need to create that separately and compose it afterwards.

var dt = new DataTable { Columns = {
    { "Id", typeof(int) },
} };
foreach (var member in group.Members)
    dt.Rows.Add(member.MemberId);

var sqlParam = new SqlParameter("@ids", dt) { TypeName = "dbo.IntList" };
var tvp = _context.Database.SqlQuery<int>($"SELECT Value FROM {sqlParam}");
// must keep these two parts separate, do not compose in one query
var results = _context.Set<T>()
    .Where(x => tvp.Any(id => id == x.Id));