Include null checking in expression of Query Filter

Question

A while back I raised this question for applying a query filter

However, I now have a requirement where if the property in the record of the table is null I need to include it

I have made the code a bit more generic so we just pass in a dictionary of the properties to filter and the value to filter for

public static class ModelBuilderExtensions
{
   public static void ApplyQueryFiltersForProperties<TProp>(this ModelBuilder builder,
        Dictionary<string, Expression<Func<TProp>>> propertiesToFilter)
    {
        foreach (var (propertyName, expression) in propertiesToFilter)
        {
            foreach (var entityType in builder.Model.GetEntityTypes())
            {
                var tenantProp = entityType.GetProperties().FirstOrDefault(p => p.Name == propertyName);
                if (tenantProp == null)
                    continue;

                var entityParam = Expression.Parameter(entityType.ClrType, "e");

                var contextPropertyAccess = expression.Body;

                var propertyExpression = GetPropertyExpression(entityParam, tenantProp);
                if (propertyExpression.Type != contextPropertyAccess.Type)
                    propertyExpression = Expression.Convert(propertyExpression, contextPropertyAccess.Type);

                // ctx.Property == null || ctx.Property == e.Property
                var filterBody = (Expression) Expression.OrElse(
                    Expression.Equal(contextPropertyAccess, Expression.Default(contextPropertyAccess.Type)),
                    Expression.Equal(contextPropertyAccess, propertyExpression));

                filterBody = (Expression) Expression.Or(
                    filterBody,
                    Expression.Equal(propertyExpression, Expression.Default(contextPropertyAccess.Type)));

                var filterLambda = entityType.GetQueryFilter();

                // we have to combine filters
                if (filterLambda != null)
                {
                    filterBody = ReplacingExpressionVisitor.Replace(entityParam, filterLambda.Parameters[0], filterBody);
                    filterBody = Expression.AndAlso(filterLambda.Body, filterBody);
                    filterLambda = Expression.Lambda(filterBody, filterLambda.Parameters);
                }
                else
                {
                    filterLambda = Expression.Lambda(filterBody, entityParam);
                }

                entityType.SetQueryFilter(filterLambda);
            }
        }
    }

    private static Expression GetPropertyExpression(Expression objExpression, IProperty property)
    {
        Expression propExpression;
        if (property.PropertyInfo == null)
        {
            // 'property' is Shadow property, so call via EF.Property(e, "name")
            propExpression = Expression.Call(typeof(EF), nameof(EF.Property), new[] {property.ClrType},
                objExpression, Expression.Constant(property.Name));
        }
        else
        {
            propExpression = Expression.MakeMemberAccess(objExpression, property.PropertyInfo);
        }

        return propExpression;
    }
}

How can I do this?

I tried

filterBody = (Expression) Expression.Or(
                        filterBody,
                        Expression.Equal(propertyExpression, Expression.Default(contextPropertyAccess.Type)));

If I supply a Tenant ID, I need all of the records that match my tenant id to be returned OR the records where TenantId is null. Originally I thought this didnt work, now it actually looks like it does, but is this the best approach?

Lets say we have 3 flats

Each Flat has a nullable property called TenantId

Flat 1 - TenantId 222
Flat 2 - TenantId 333
Flat 3 - TenantId NULL

Our DbContext has a property called TenantId

So if TenantId is 333 I want Flat 2 and Flat 3 to be returned. Flat 3 because it has no TenantId

The idea of this approach is to have information available to all TenantIds, and other tied down to the specific tenant Id requested

I dont need to change the query filter on the fly

My generated SQL using TenantId and including records with null is really complex and potentially could be slow as well given that case statements in where clauses are not great

 WHERE ((CASE
          WHEN (@__ef_filter__p_0 = CAST(1 AS bit)) OR ((@__ef_filter__TenantId_1 = [b].[TenantId]) AND [b].[TenantId] IS NOT NULL) THEN CAST(1 AS bit)
          ELSE CAST(0 AS bit)
      END | CASE
          WHEN [b].[TenantId] IS NULL THEN CAST(1 AS bit)
          ELSE CAST(0 AS bit)
      END) = CAST(1 AS bit))

this question

Answer 1

Try simplify filterBody to the following:

var filterBody = (Expression) Expression.Equal(contextPropertyAccess, propertyExpression);

Corrected answer:

public static class ModelBuilderExtensions
{
   public static void ApplyQueryFiltersForProperties<TProp>(this ModelBuilder builder,
        Dictionary<string, Expression<Func<TProp>>> propertiesToFilter)
    {
        foreach (var (propertyName, expression) in propertiesToFilter)
        {
            foreach (var entityType in builder.Model.GetEntityTypes())
            {
                var tenantProp = entityType.GetProperties().FirstOrDefault(p => p.Name == propertyName);
                if (tenantProp == null)
                    continue;

                var entityParam = Expression.Parameter(entityType.ClrType, "e");

                var contextPropertyAccess = expression.Body;

                var propertyExpression = GetPropertyExpression(entityParam, tenantProp);
                if (propertyExpression.Type != contextPropertyAccess.Type)
                    propertyExpression = Expression.Convert(propertyExpression, contextPropertyAccess.Type);

                // ctx.Property == e.Property
                var filterBody = (Expression) Expression.Equal(contextPropertyAccess, propertyExpression);

                var filterLambda = entityType.GetQueryFilter();

                // we have to combine filters
                if (filterLambda != null)
                {
                    filterBody = ReplacingExpressionVisitor.Replace(entityParam, filterLambda.Parameters[0], filterBody);
                    filterBody = Expression.AndAlso(filterLambda.Body, filterBody);
                    filterLambda = Expression.Lambda(filterBody, filterLambda.Parameters);
                }
                else
                {
                    filterLambda = Expression.Lambda(filterBody, entityParam);
                }

                entityType.SetQueryFilter(filterLambda);
            }
        }
    }

    private static Expression GetPropertyExpression(Expression objExpression, IProperty property)
    {
        Expression propExpression;
        if (property.PropertyInfo == null)
        {
            // 'property' is Shadow property, so call via EF.Property(e, "name")
            propExpression = Expression.Call(typeof(EF), nameof(EF.Property), new[] {property.ClrType},
                objExpression, Expression.Constant(property.Name));
        }
        else
        {
            propExpression = Expression.MakeMemberAccess(objExpression, property.PropertyInfo);
        }

        return propExpression;
    }
}