ExpressionVisitor soft delete

Question

We're having some issues implementing soft delete functionality with entity framework. The idea is to use a repository which is aware of the EF context. On the level of the repository we implemented a plugin system, these plugins get executed whenever an action is done on the repository. For example when we call Repository.GetQuery<Relation>() the plugins get executed. One of the plugins is a LogicalDeletePlugin, this plugin should add a Where(x => x.IsDeleted) statement to each table which is in the select. The idea was to implement this IsDeleted plugin using an ExpressionVisitor which visits the linq expression and finds all the "table" select statements and add the IsDeleted condition.

To clarify the question/problem I'll explain the issue using some code examples.

void Main()
{
var options = new ReadonlyRepositoryOptions() { ConnectionStringDelegate = () => Connection.ConnectionString };
using (var context = new ReadonlyObjectContextRepository<PFishEntities>(options))
{
var query = context.GetQuery<Relation>()
.Select(x => new {
Test = x.Bonus,
TestWorks = x.Bonus.Where(y => y.bonID == 100)
});

query.InterceptWith(new TestVisitor()).ToList();
}
}

public class TestVisitor : ExpressionVisitor {
private ParameterExpression Parameter { get; set; }

protected override Expression VisitBinary(BinaryExpression node) {
"VisitBinary".Dump();
Expression left = this.Visit(node.Left);
Expression right = this.Visit(node.Right);

var newParams = new[] { Parameter };
var condition = (LambdaExpression)new LogicalDeletePlugin().QueryConditionals.First().Conditional;
var paramMap = condition.Parameters.Select((original, i) => new { original, replacement = newParams[i] }).ToDictionary(p => p.original, p => p.replacement);

var fixedBody = ParameterRebinder.ReplaceParameters(paramMap, condition.Body);
return Expression.MakeBinary(ExpressionType.AndAlso, node, fixedBody, node.IsLiftedToNull, node.Method);
    }

protected override Expression VisitParameter(ParameterExpression expr)
{
Parameter = expr;
return base.VisitParameter(expr);
}
}
void Main()
{
    var options = new ReadonlyRepositoryOptions() { ConnectionStringDelegate = () => Connection.ConnectionString };
    using (var context = new ReadonlyObjectContextRepository<PFishEntities>(options))
    {
        var query = context.GetQuery<Relation>()
        .Select(x => new {
            Test = x.Bonus,
            TestWorks = x.Bonus.Where(y => y.bonID == 100)
        });

        query.InterceptWith(new TestVisitor()).ToList();
    }
}

public class TestVisitor : ExpressionVisitor {
    private ParameterExpression Parameter { get; set; }

    protected override Expression VisitBinary(BinaryExpression node) {
        "VisitBinary".Dump();
        Expression left = this.Visit(node.Left);
        Expression right = this.Visit(node.Right);

        var newParams = new[] { Parameter };
        var condition = (LambdaExpression)new LogicalDeletePlugin().QueryConditionals.First().Conditional;
        var paramMap = condition.Parameters.Select((original, i) => new { original, replacement = newParams[i] }).ToDictionary(p => p.original, p => p.replacement);
        var fixedBody = ParameterRebinder.ReplaceParameters(paramMap, condition.Body);
        return Expression.MakeBinary(ExpressionType.AndAlso, node, fixedBody, node.IsLiftedToNull, node.Method);
    }

    protected override Expression VisitParameter(ParameterExpression expr)
    {
        Parameter = expr;
        return base.VisitParameter(expr);
    }
}

The above c# code will result in the following SQL code :

SELECT 
[UnionAll1].[relID] AS [C1], 
[UnionAll1].[C2] AS [C2], 
[UnionAll1].[C1] AS [C3], 
[UnionAll1].[bonID] AS [C4], 
[UnionAll1].[bonCUSTOMERID] AS [C5], 
[UnionAll1].[bonRELATIONARTICLEBONUSID] AS [C6], 
[UnionAll1].[bonINVOICEID] AS [C7], 
[UnionAll1].[bonSALEROWID] AS [C8], 
[UnionAll1].[bonVALUE] AS [C9], 
[UnionAll1].[bonPERCENTAGE] AS [C10], 
[UnionAll1].[bonMANUAL] AS [C11], 
[UnionAll1].[bonPAID] AS [C12], 
[UnionAll1].[IsDeleted] AS [C13], 
[UnionAll1].[InternalReference] AS [C14], 
[UnionAll1].[ConcurrencyToken] AS [C15], 
[UnionAll1].[Created] AS [C16], 
[UnionAll1].[CreatedBy] AS [C17], 
[UnionAll1].[Updated] AS [C18], 
[UnionAll1].[UpdatedBy] AS [C19], 
[UnionAll1].[DisplayMember] AS [C20], 
[UnionAll1].[ValueMember] AS [C21], 
[UnionAll1].[SearchField] AS [C22], 
[UnionAll1].[CreateDate] AS [C23], 
[UnionAll1].[C3] AS [C24], 
[UnionAll1].[C4] AS [C25], 
[UnionAll1].[C5] AS [C26], 
[UnionAll1].[C6] AS [C27], 
[UnionAll1].[C7] AS [C28], 
[UnionAll1].[C8] AS [C29], 
[UnionAll1].[C9] AS [C30], 
[UnionAll1].[C10] AS [C31], 
[UnionAll1].[C11] AS [C32], 
[UnionAll1].[C12] AS [C33], 
[UnionAll1].[C13] AS [C34], 
[UnionAll1].[C14] AS [C35], 
[UnionAll1].[C15] AS [C36], 
[UnionAll1].[C16] AS [C37], 
[UnionAll1].[C17] AS [C38], 
[UnionAll1].[C18] AS [C39], 
[UnionAll1].[C19] AS [C40], 
[UnionAll1].[C20] AS [C41], 
[UnionAll1].[C21] AS [C42], 
[UnionAll1].[C22] AS [C43]
FROM  (SELECT 
    CASE WHEN ([Extent2].[bonID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
    [Extent1].[relID] AS [relID], 
    1 AS [C2], 
    [Extent2].[bonID] AS [bonID], 
    [Extent2].[bonCUSTOMERID] AS [bonCUSTOMERID], 
    [Extent2].[bonRELATIONARTICLEBONUSID] AS [bonRELATIONARTICLEBONUSID], 
    [Extent2].[bonINVOICEID] AS [bonINVOICEID], 
    [Extent2].[bonSALEROWID] AS [bonSALEROWID], 
    [Extent2].[bonVALUE] AS [bonVALUE], 
    [Extent2].[bonPERCENTAGE] AS [bonPERCENTAGE], 
    [Extent2].[bonMANUAL] AS [bonMANUAL], 
    [Extent2].[bonPAID] AS [bonPAID], 
    [Extent2].[IsDeleted] AS [IsDeleted], 
    [Extent2].[InternalReference] AS [InternalReference], 
    [Extent2].[ConcurrencyToken] AS [ConcurrencyToken], 
    [Extent2].[Created] AS [Created], 
    [Extent2].[CreatedBy] AS [CreatedBy], 
    [Extent2].[Updated] AS [Updated], 
    [Extent2].[UpdatedBy] AS [UpdatedBy], 
    [Extent2].[DisplayMember] AS [DisplayMember], 
    [Extent2].[ValueMember] AS [ValueMember], 
    [Extent2].[SearchField] AS [SearchField], 
    [Extent2].[CreateDate] AS [CreateDate], 
    CAST(NULL AS bigint) AS [C3], 
    CAST(NULL AS bigint) AS [C4], 
    CAST(NULL AS bigint) AS [C5], 
    CAST(NULL AS bigint) AS [C6], 
    CAST(NULL AS bigint) AS [C7], 
    CAST(NULL AS decimal(20,4)) AS [C8], 
    CAST(NULL AS decimal(20,4)) AS [C9], 
    CAST(NULL AS bit) AS [C10], 
    CAST(NULL AS decimal(20,4)) AS [C11], 
    CAST(NULL AS bit) AS [C12], 
    CAST(NULL AS varchar(1)) AS [C13], 
    CAST(NULL AS varbinary(1)) AS [C14], 
    CAST(NULL AS datetimeoffset) AS [C15], 
    CAST(NULL AS varchar(1)) AS [C16], 
    CAST(NULL AS datetimeoffset) AS [C17], 
    CAST(NULL AS varchar(1)) AS [C18], 
    CAST(NULL AS varchar(1)) AS [C19], 
    CAST(NULL AS varchar(1)) AS [C20], 
    CAST(NULL AS varchar(1)) AS [C21], 
    CAST(NULL AS datetime2) AS [C22]
    FROM  [dbo].[Relation] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Bonus] AS [Extent2] ON [Extent1].[relID] = [Extent2].[bonCUSTOMERID]
UNION ALL
    SELECT 
    2 AS [C1], 
    [Extent3].[relID] AS [relID], 
    2 AS [C2], 
    CAST(NULL AS bigint) AS [C3], 
    CAST(NULL AS bigint) AS [C4], 
    CAST(NULL AS bigint) AS [C5], 
    CAST(NULL AS bigint) AS [C6], 
    CAST(NULL AS bigint) AS [C7], 
    CAST(NULL AS decimal(20,4)) AS [C8], 
    CAST(NULL AS decimal(20,4)) AS [C9], 
    CAST(NULL AS bit) AS [C10], 
    CAST(NULL AS decimal(20,4)) AS [C11], 
    CAST(NULL AS bit) AS [C12], 
    CAST(NULL AS varchar(1)) AS [C13], 
    CAST(NULL AS varbinary(1)) AS [C14], 
    CAST(NULL AS datetimeoffset) AS [C15], 
    CAST(NULL AS varchar(1)) AS [C16], 
    CAST(NULL AS datetimeoffset) AS [C17], 
    CAST(NULL AS varchar(1)) AS [C18], 
    CAST(NULL AS varchar(1)) AS [C19], 
    CAST(NULL AS varchar(1)) AS [C20], 
    CAST(NULL AS varchar(1)) AS [C21], 
    CAST(NULL AS datetime2) AS [C22], 
    [Extent4].[bonID] AS [bonID], 
    [Extent4].[bonCUSTOMERID] AS [bonCUSTOMERID], 
    [Extent4].[bonRELATIONARTICLEBONUSID] AS [bonRELATIONARTICLEBONUSID], 
    [Extent4].[bonINVOICEID] AS [bonINVOICEID], 
    [Extent4].[bonSALEROWID] AS [bonSALEROWID], 
    [Extent4].[bonVALUE] AS [bonVALUE], 
    [Extent4].[bonPERCENTAGE] AS [bonPERCENTAGE], 
    [Extent4].[bonMANUAL] AS [bonMANUAL], 
    [Extent4].[bonPAID] AS [bonPAID], 
    [Extent4].[IsDeleted] AS [IsDeleted], 
    [Extent4].[InternalReference] AS [InternalReference], 
    [Extent4].[ConcurrencyToken] AS [ConcurrencyToken], 
    [Extent4].[Created] AS [Created], 
    [Extent4].[CreatedBy] AS [CreatedBy], 
    [Extent4].[Updated] AS [Updated], 
    [Extent4].[UpdatedBy] AS [UpdatedBy], 
    [Extent4].[DisplayMember] AS [DisplayMember], 
    [Extent4].[ValueMember] AS [ValueMember], 
    [Extent4].[SearchField] AS [SearchField], 
    [Extent4].[CreateDate] AS [CreateDate]
    FROM  [dbo].[Relation] AS [Extent3]
    INNER JOIN [dbo].[Bonus] AS [Extent4] ON ([Extent3].[relID] = [Extent4].[bonCUSTOMERID]) AND (100 = [Extent4].[bonID]) AND ([Extent4].[IsDeleted] <> cast(1 as bit))) AS [UnionAll1]
ORDER BY [UnionAll1].[relID] ASC, [UnionAll1].[C1] ASC

As you can see in the resulting SQL query the IsDeleted statements gets added to the TestWorks = x.Bonus.Where(y => !y.IsDeleted) "select" code. That's what the TestVisitor is currently doing. But the question is now how we can also implement this on the other selects, the x => !x.IsDeleted doesn't get added on the Test = x.Bonus part.

Is the ExpressionVisitor the correct approach to get this done or should I go with another solution? All help is appreciated! If the explanation wasn't clear enough just let me know and I'll try to give some additional info!

Edit:

protected override Expression VisitMember(MemberExpression node)
    {
        var test = typeof(bool);
        if (node.Type != test && node.Type != typeof(string))
        {
            var type = typeof(ArticleVat);
            var condition = (LambdaExpression)Condition;
            var newParams = new[] { Expression.Parameter(type, "x") };
            var paramMap = condition.Parameters.Select((original, i) => new { original, replacement = newParams[i] }).ToDictionary(p => p.original, p => p.replacement);
            var fixedBody = ParameterRebinder.ReplaceParameters(paramMap, condition.Body);
            condition = Expression.Lambda(fixedBody, newParams);
            var whereM = whereMethod.MakeGenericMethod(new [] { type });
            var expr = Expression.Property(node.Expression, "ArticleVat");
            var whereExpr = Expression.Call(whereM, expr, condition);
//          whereExpr.Dump();
node.Dump();
//          return Expression.MakeMemberAccess(whereExpr, node.Expression.Type.GetMember(node.Member.Name).Single());
//           return Expression.MakeMemberAccess(
//                whereExpr,
//                node.Expression.Type.GetMember(node.Member.Name).Single());
        }

        return base.VisitMember(node);
    }

The above is what I've added to the ExpressionVisitor. Now when I uncomment the return Expression.MamkeMemberaccess code an exception is thrown because it doesn't expect a MemberExpression or something.

Following is the solution I came up with :

/// <summary>
/// This visitor will append a .Where(QueryCondition) clause for a given Condition to each Navigation property
/// </summary>
public class InjectConditionVisitor : ExpressionVisitor
{
    private QueryConditional QueryCondition { get; set; }

    public InjectConditionVisitor(QueryConditional condition)
    {
        QueryCondition = condition;
    }

    protected override Expression VisitMember(MemberExpression ex)
    {
        // Only change generic types = Navigation Properties
        // else just execute the normal code.
        return !ex.Type.IsGenericType ? base.VisitMember(ex) : CreateWhereExpression(QueryCondition, ex) ?? base.VisitMember(ex);
    }

    /// <summary>
    /// Create the where expression with the adapted QueryConditional
    /// </summary>
    /// <param name="condition">The condition to use</param>
    /// <param name="ex">The MemberExpression we're visiting</param>
    /// <returns></returns>
    private Expression CreateWhereExpression(QueryConditional condition, Expression ex)
    {
        var type = ex.Type.GetGenericArguments().First();
        var test = CreateExpression(condition, type);
        if (test == null)
            return null;
        var listType = typeof(IQueryable<>).MakeGenericType(type);
        return Expression.Convert(Expression.Call(typeof(Enumerable), "Where", new Type[] { type }, (Expression)ex, test), listType);
    }

    /// <summary>
    /// Adapt a QueryConditional to the member we're currently visiting.
    /// </summary>
    /// <param name="condition">The condition to adapt</param>
    /// <param name="type">The type of the current member (=Navigation property)</param>
    /// <returns>The adapted QueryConditional</returns>
    private LambdaExpression CreateExpression(QueryConditional condition, Type type)
    {
        var lambda = (LambdaExpression)condition.Conditional;
        var conditionType = condition.GetType().GetGenericArguments().FirstOrDefault();
        // Only continue when the condition is applicable to the Type of the member
        if (conditionType == null)
            return null;
        if (!conditionType.IsAssignableFrom(type))
            return null;

        var newParams = new[] { Expression.Parameter(type, "bo") };
        var paramMap = lambda.Parameters.Select((original, i) => new { original, replacement = newParams[i] }).ToDictionary(p => p.original, p => p.replacement);
        var fixedBody = ParameterRebinder.ReplaceParameters(paramMap, lambda.Body);
        lambda = Expression.Lambda(fixedBody, newParams);

        return lambda;
    }
}

QueryConditional is a class which holds an Expression of type Expression<Func<T, bool>>.

The InjectconditionVisitor can be used in combination with InterceptWith (QueryInterceptor NuGet package) like query.InterceptWith(new InjectConditionVisitor(new QueryConditional(x => x.Deleted == true)).

Minnieminnnie

I don't understand your solution. You say QueryConditional is a class where, I'm assuming, the Conditional property is an Expression<Func<T, bool>>. But where does T come from? When you say QueryConditional(x => x.Deleted == true)) where is x's type defined? It seems like T would have to be a specific base class or interface or QueryConditional would have to be a generic class QueryConditional<T>. Also what is ParameterRebinder? – Pilcomayo

Answer 1

Yes, using ExpressionVisitor is the correct approach.

You need to transform x.Bonus into x.Bonus.Where(x => !x.IsDeleted). I suggest you do the same thing for x.Bonus.Where(y => y.bonID == 100). Transform it into x.Bonus.Where(x => !x.IsDeleted).Where(y => y.bonID == 100)

This means that you need to convert any Expression of type IQueryable<Bonus> to another expression of type IQueryable<Bonus>, but with the where-clause appended.

You probably need to override the very general ExpressionVisitor.Visit method to visit all expressions, not just binary ones.

You are very likely to run into special cases here that you haven't thought about yet. This is going to be difficult, but fun :)

Basil

Thanks for the reply. I haven't had a lot of time to continue working on this problem. The last thing I ran against was that when you look at the linq code is that the x.Bonus.Where(x => X...) is of a different type as the x.Bonus code. Been trying to convert the x.Bonus to a x.Bonus.Where but without any luck. Do you perhaps have an idea how this can be done ? – Minnieminnnie

I think what you want is (x.Bonus.IsDeleted ? null : x.Bonus). This might be slow on the SQL side because EF might translate this into an inefficient form. – Basil

Answer 2

Victory! Today I've created an ExpressionVisitor which appends IsDeleted where clause to each select, even in navigation properties!

Minnieminnnie

I'm trying to solve this exact same problem. Can you update your answer to include the final code you came up with? – Casemaker

Hi user1725275, I tried following your sample code, but am not getting the same result. I've posted a new question with my code and more info here. Would you please take a look? – Shove