Add a where clause to IQueryable without using generics

Question

I'm trying to write a piece of code that is going to be used to select a value from a database. As a test I have already created this test version of the method that "works" but isn't great.

private object? FindObjectByProperty(IQueryable query, PropertyInfo primaryKeyProperty, object lookupValue)
{
    object? result = null;
    foreach (var value in query)
    {
        if (!primaryKeyProperty.GetValue(value)!.Equals(lookupValue))
            continue;

        result = value;
        break;
    }

    return result;
}

The code will iterate through the query and find the first entry that matches the Equals. However, if the query contains millions of rows, as it's likely to when the IQueryable is really a DbSet (Which in this use case it is. Only I don't know what T, as it could be any of the DbSets on my EF Core data context.

What I'd like to end up with is something that looks something along these lines....

private object? FindObjectByProperty(IQueryable query, PropertyInfo primaryKeyProperty, object lookupValue)
{
    return query.Where( x => x.*primaryKeyProperty* == lookupValue).FirstOrDefault();
}

The above code is pseudo code, the problems that I have is that query does not have a .Where available. Also primaryKeyProperty will need to be translated to the actual property.

The idea is that when this code is executed, ultimately executing the query, will generate a sql statement which selects a single item and returns it.

Can anyone help with solving this?

Update: I'm working on a solution to this, so far this is what I've come up with

//using System.Linq.Dynamic.Core;

private object? FindObjectByProperty(IQueryable query, PropertyInfo primaryKeyProperty, object lookupValue)
{
    var parameter = Expression.Parameter(query.ElementType);
    var e1 = Expression.Equal(Expression.Property(parameter, primaryKeyProperty.Name), Expression.Constant(lookupValue));
    var lambda = Expression.Lambda<Func<object, bool>>(e1, parameter);
    return query.Where(lambda).FirstOrDefault();
}

This is failing on the because the func uses Object, when it really needs the real type. Trying to figure that bit out. This is getting closer.

Update #2: Here's the answer that I needed

private object? FindObjectByProperty(IQueryable query, PropertyInfo primaryKeyProperty, object lookupValue)
{
    var parameter = Expression.Parameter(query.ElementType);
    var propExpr = Expression.Property(parameter, primaryKeyProperty);
    var lambdaBody = Expression.Equal(propExpr, Expression.Constant(lookupValue, primaryKeyProperty.PropertyType));
    var filterEFn = Expression.Lambda(lambdaBody, parameter);
    return query.Where(filterEFn).FirstOrDefault();
}

The difference is that the Expression.Lambda no longer tries to define the func using generics. This is the only change that I needed to do to make the code function as I wanted.

In the test case that I was using, the T-SQL produced to lookup the value looks like this...

SELECT TOP(1) [g].[Id], [g].[Deleted], [g].[Guid], [g].[Name], [g].[ParentId]
FROM [Glossaries].[Glossaries] AS [g]
WHERE [g].[Id] = CAST(3 AS bigint)

The table [Glossaries].[Glossaries] is provided by the input query. The column name Id is provided by the primaryKeyProperty, and the number 3 is provided by the lookupValue.

This is perfect for my needs as I simply needed to select that one row and nothing else, so that I can effectively lazy load the my object property when I need it, and not before.

Also this code will be reused for many different tables.

Answer 1

In order to build add Where to an IQueryable where you don't have access to the actual IQueryable<T>, you need to take a step back (or up?) from calling Where at compile-time, and build the Where call at runtime, as well as the predicate lambda:

public static class DBExt {
    public static IQueryable WherePropertyIs<T2>(this IQueryable src, PropertyInfo propInfo, T2 propValue) {
        // return src.Where(s => s.{propInfo} == propValue)
        // (T s)
        var sParam = Expression.Parameter(src.ElementType, "s");
        // s.propInfo
        var propExpr = Expression.Property(sParam, propInfo);
        // s.{propInfo} == propValue
        var lambdaBody = Expression.Equal(propExpr, Expression.Constant(propValue));
        // (T s) => s.{PropInfo} == propValue
        var filterEFn = Expression.Lambda(lambdaBody, sParam);

        var origQuery = src.Expression;
        // IQueryable<Tx>.Where<Tx, Expression<Func<Tx, bool>>>()
        var whereGenericMI = typeof(Queryable).GetMethods("Where", 2).Where(mi => mi.GetParameters()[1].ParameterType.GenericTypeArguments[0].GenericTypeArguments.Length == 2).First();
        // IQueryable<T>.Where<T, Expression<Func<T, bool>>>()
        var whereMI = whereGenericMI.MakeGenericMethod(src.ElementType);
        // src.Where(s => s.{propertyInfo} == propValue)
        var newQuery = Expression.Call(whereMI, origQuery, filterEFn);

        return src.Provider.CreateQuery(newQuery);
    }
}