Support for the In-Memory Database Provider

[yv989c]

https://docs.microsoft.com/en-us/ef/core/providers/in-memory/
Will be useful in test scenarios.

[yv989c]

Some refactoring around DI must be done before starting this. Will table for now.

[fiseni]

Hi @yv989c

Any plans for this? Would be neat to have this feature. In the case of an in-memory provider perhaps we can fall back to the standard Contains expression?

In the meantime, if this is a deal breaker for anyone, here is a workaround that can be used.

using BlazarTech.QueryableValues;
using BlazarTech.QueryableValues.Builders;
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Linq.Expressions;
using System.Reflection;

namespace EFCoreContains;

public static class IQueryableValuesExtensions
{
    private static readonly MethodInfo _asQueryableValuesMethodInfo = typeof(QueryableValuesDbContextExtensions)
            .GetTypeInfo()
            .GetDeclaredMethods(nameof(QueryableValuesDbContextExtensions.AsQueryableValues))
            .Single(mi => mi.GetParameters().Length == 3
                && mi.GetGenericArguments().Length == 1
                && mi.GetParameters()[0].ParameterType == typeof(DbContext)
                && mi.GetParameters()[1].ParameterType.GetGenericTypeDefinition() == typeof(IEnumerable<>));

    private static readonly MethodInfo _containsQueryableMethodInfo = typeof(Queryable)
            .GetTypeInfo()
            .GetDeclaredMethods(nameof(Queryable.Contains))
            .Single(mi => mi.GetParameters().Length == 2
                && mi.GetGenericArguments().Length == 1
                && mi.GetParameters()[0].ParameterType.GetGenericTypeDefinition() == typeof(IQueryable<>)
                && mi.GetParameters()[1].ParameterType.IsGenericParameter);

    private static readonly MethodInfo _containsEnumerableMethodInfo = typeof(Enumerable)
            .GetTypeInfo()
            .GetDeclaredMethods(nameof(Queryable.Contains))
            .Single(mi => mi.GetParameters().Length == 2
                && mi.GetGenericArguments().Length == 1
                && mi.GetParameters()[0].ParameterType.GetGenericTypeDefinition() == typeof(IEnumerable<>)
                && mi.GetParameters()[1].ParameterType.IsGenericParameter);

    public static IQueryable<TEntity> In<TEntity, TKey>(
        this IQueryable<TEntity> source,
        Expression<Func<TEntity, TKey>> keySelector,
        IEnumerable<TKey> values,
        DbContext dbContext,
        Action<EntityOptionsBuilder<TKey>>? configure = null)
        => dbContext.Database.IsSqlServer()
            ? InSQL(source, keySelector, values, dbContext, configure)
            : InMemory(source, keySelector, values);

    public static IQueryable<TEntity> InSQL<TEntity, TKey>(
        this IQueryable<TEntity> source,
        Expression<Func<TEntity, TKey>> keySelector,
        IEnumerable<TKey> values,
        DbContext dbContext,
        Action<EntityOptionsBuilder<TKey>>? configure = null)
    {
        ArgumentNullException.ThrowIfNull(dbContext);
        ArgumentNullException.ThrowIfNull(values);
        ArgumentNullException.ThrowIfNull(keySelector);

        var parameter = Expression.Parameter(typeof(TEntity), "x");
        var propertySelector = ParameterReplacerVisitor.Replace(keySelector, keySelector.Parameters[0], parameter) as LambdaExpression;
        _ = propertySelector ?? throw new InvalidExpressionException();

        // Get generic methodInfo for AsQueryableValues.
        var asQueryableValuesMethod = _asQueryableValuesMethodInfo.MakeGenericMethod(typeof(TKey));

        // Create closures so EF can parameterize the query.
        var dbContextAsExpression = ((Expression<Func<DbContext>>)(() => dbContext)).Body;
        var valuesAsExpression = ((Expression<Func<IEnumerable<TKey>>>)(() => values)).Body;
        var configurationAsExpression = ((Expression<Func<Action<EntityOptionsBuilder<TKey>>?>>)(() => configure)).Body;

        // Create an expression for the AsQueryableValues method.
        var asQueryableValuesExpression = Expression.Call(
            null,
            asQueryableValuesMethod,
            dbContextAsExpression,
            valuesAsExpression,
            configurationAsExpression);

        // Get generic methodInfo for Contains.
        var containsMethod = _containsQueryableMethodInfo.MakeGenericMethod(typeof(TKey));

        // Create an expression for the Contains method.
        var containsExpression = Expression.Call(
            null,
            containsMethod,
            asQueryableValuesExpression,
            propertySelector.Body);

        // Create the final lambda expression
        var whereLambda = Expression.Lambda<Func<TEntity, bool>>(containsExpression, parameter);

        // Use the expression with the Where method
        var result = source.Where(whereLambda);

        return result;
    }

    public static IQueryable<TEntity> InMemory<TEntity, TKey>(
        this IQueryable<TEntity> source,
        Expression<Func<TEntity, TKey>> keySelector,
        IEnumerable<TKey> values)
    {
        ArgumentNullException.ThrowIfNull(values);
        ArgumentNullException.ThrowIfNull(keySelector);

        var parameter = Expression.Parameter(typeof(TEntity), "x");
        var propertySelector = ParameterReplacerVisitor.Replace(keySelector, keySelector.Parameters[0], parameter) as LambdaExpression;
        _ = propertySelector ?? throw new InvalidExpressionException();

        // Create closures so EF can parameterize the query.
        var valuesClosure = ((Expression<Func<IEnumerable<TKey>>>)(() => values)).Body;

        // Get MethodInfo for Contains.
        var containsMethod = _containsEnumerableMethodInfo.MakeGenericMethod(typeof(TKey));

        // Create an expression for the Contains method.
        var containsExpression = Expression.Call(
            null,
            containsMethod,
            valuesClosure,
            propertySelector.Body);

        // Create the final lambda expression
        var whereLambda = Expression.Lambda<Func<TEntity, bool>>(containsExpression, parameter);

        // Use the expression with the Where method
        var result = source.Where(whereLambda);

        return result;
    }
}

public class ParameterReplacerVisitor : ExpressionVisitor
{
    private readonly Expression _newExpression;
    private readonly ParameterExpression _oldParameter;

    private ParameterReplacerVisitor(ParameterExpression oldParameter, Expression newExpression)
    {
        _oldParameter = oldParameter;
        _newExpression = newExpression;
    }

    internal static Expression Replace(Expression expression, ParameterExpression oldParameter, Expression newExpression)
    {
        return new ParameterReplacerVisitor(oldParameter, newExpression).Visit(expression);
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        return node == _oldParameter ? _newExpression : node;
    }
}

The usage would be as follows

using (var dbContext = new AppDbContextQueryableJSON())
{
    var ids = Enumerable.Range(1, 2).Select(x => Guid.NewGuid());

    var result = await dbContext.Customers.In(x => x.Id, ids, dbContext).ToListAsync();
}

Note:The extension method above will create closures per generic types TEntity and TKey. This might make the execution slightly faster, but the EF's query cache will contain more items. It won't affect the SQL server side, only EF's cache. Here are a few benchmarks for Guid type.

Method	Count	Mean	Error	StdDev	Gen0	Gen1	Gen2	Allocated
QueryableValuesJSON	1024	3.027 ms	0.0475 ms	0.0397 ms	19.5313	3.9063	-	178.55 KB
In	1024	2.843 ms	0.0496 ms	0.0464 ms	19.5313	3.9063	-	181.55 KB
QueryableValuesJSON	2048	5.497 ms	0.1091 ms	0.1020 ms	46.8750	46.8750	46.8750	259.39 KB
In	2048	5.431 ms	0.1046 ms	0.1360 ms	46.8750	46.8750	46.8750	262.31 KB

Update: EF doesn't cache the closure types, it considers them the same. So, there is no drawback at all, you may ignore the last comment.

[drdamour]

@fiseni couldn't InSQL simply be:

    public static IQueryable<TEntity> InSQL<TEntity, TKey>(
        this IQueryable<TEntity> source,
        Expression<Func<TEntity, TKey>> keySelector,
        IQueryable<TKey> values
    ) 
    {
        return source
            .Join(
                values,
                keySelector,
                v => v,
                (l, r) => l
            );
    }

[fiseni]

The InSql implementation here will generate a Contains expression (e.g. .Where(i => dbContext.AsQueryableValues(values).Contains(i.MyEntityID))). That's the reason for building the expression manually. There were some corner cases that prevented me from using Join.

Refer to this comment for an updated version using Join operation. #30 (comment)

[drdamour]

Ah yup we came to practically same solution. Wonder what your edge cases were, ive got this in the middle of some gnarly linq and its working well.

i did change the facade router to get the context from the DbSet via GetService so i didnt have to pass it in…did u look at that?

[fiseni]

The issue with the join variant was that the overall query couldn't be translated if it contains 'Include' statements. That is already fixed in the latest version, so you don't have to worry about it anymore. Always use Join, it's more efficient.

Yes, you can get the dbContext out of the DbSet, but you'd be relying on EF internals. I wouldn't suggest it to be honest.