none
Expressionvisitor soft delete RRS feed

  • Question

  • Hi,

    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!

    • Moved by Bob Shen Tuesday, October 9, 2012 9:45 AM (From:Visual C# Language)
    Saturday, October 6, 2012 2:57 PM

Answers

  • Victory! Found the solution to append an IsDeleted to each table without append a Where(x => !x.isDeleted) to each table!
    • Marked as answer by JTI123 Sunday, March 17, 2013 6:00 PM
    Sunday, March 17, 2013 3:31 PM

All replies

  • Hi JTI123,

    According to your description, I'd like to move this thread to entity framework forum for better support, where more experts live.
     
    Thanks for your understanding.


    Bob Shen [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, October 9, 2012 9:44 AM
  • Hi,

    From a support perspective this is really beyond what we can do here in the forums. If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.

    Thanks,

    Cathy Miller

    Tuesday, October 23, 2012 2:20 PM
    Moderator
  • Hi,

    It has been a while but today I've found some time to continue my experiment with the ExpressionVisitor. With this TestVisitor I'm now able to iterate through each select. But now I want to rewrite those selects into a <Entity>.Where(x => !x.IsDeleted). But when creating the where expression the following exception is thrown

        ArgumentException: an expression of the SaleOrder can't be used as a parameter for IEnumerable<SaleRow>.

    This basically tells me that I'm using the wrong parameter (SaleRow) instead of a SaleOrder. Does anyone have an idea how to solve this ?


    Following is what I have right now :


        public class TestVisitor : ExpressionVisitor {
    static readonly MethodInfo whereMethod = typeof(Enumerable).GetMethods().Where(m => m.Name == "Where").First();

    protected override Expression VisitMember(MemberExpression ex)
    {
    if (!ex.Type.IsGenericType)
                 return base.VisitMember(ex);
                 
    var type = ex.Type.GenericTypeArguments.First();
    var condition = Expression.Equal(Expression.Property(Expression.Parameter(type,"x"),"IsDeleted"), Expression.Constant(false));
    var lambda = Expression.Lambda(condition, Expression.Parameter(ex.Type,"x"));
           Expression.Call(null, whereMethod.MakeGenericMethod(new [] { ex.Type }), new[] { ex.Expression, Expression.Quote(lambda) }).Dump();
           
           return base.VisitMember(ex);
    }
    }
    Thursday, March 14, 2013 10:33 AM
  • Hi,

    Been doing some extra research on this subject now and I think I'm a little bit further to the solution. Now the issue is that an OverFlowException is thrown because the VisitMember is called in a loop :(

    public class TestVisitor : ExpressionVisitor {
    	static readonly MethodInfo whereMethod = typeof(Enumerable).GetMethods().Where(m => m.Name == "Where").First();
    
    	protected override Expression VisitMember(MemberExpression ex)
    	{
    		if (!ex.Type.IsGenericType)
    			return base.VisitMember(ex);
    		
    		var type = ex.Type.GenericTypeArguments.First();
    		var condition = Expression.Equal(Expression.Property(Expression.Parameter(type,"z"),"IsDeleted"), Expression.Constant(false));
    		var lambda = Expression.Lambda(condition, Expression.Parameter(type,"z"));
    		
    		var test = whereMethod.MakeGenericMethod(new Type[] { type } );
    		var test2 = Expression.Parameter(typeof(TrackableCollection<SaleRow>), "z");
    		var ddd = Expression.Call(null, test, new[] { (Expression)ex, (Expression)lambda }); //.Dump();
    		
    		"Test".Dump();
    		Visit(Expression.Convert(ddd, typeof(TrackableCollection<SaleRow>))); //.Dump());
           return base.VisitMember(ex);
    	}
    }


    Friday, March 15, 2013 10:16 PM
  • Victory! Found the solution to append an IsDeleted to each table without append a Where(x => !x.isDeleted) to each table!
    • Marked as answer by JTI123 Sunday, March 17, 2013 6:00 PM
    Sunday, March 17, 2013 3:31 PM
  • Hi JT123,

    Can you please share the approach you found of appending the IsDeleted check to each table?

    Thanks,

    Alvin George


    Alvin

    Thursday, June 6, 2013 1:53 PM
  • Sorry for the late reply, but didn't notice that there were new items on this thread.

    I've solved this using a combination of ExpressionVisitor & QueryInterceptor (NuGet). The only thing which is not supported is applying IsDeleted using Include. This is because QueryInterceptor doesn't support this.

    To make everything a little more generic I creatd an "InjectConditionVisitor" which inherits from ExpressionVisitor. This visitor accepts a QueryCondition object which is basically just a container to hold a expression statement (x => !x.IsDeleted).

    Following is the code of this InjectConditionVisitor class :

    /// <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().First();
                // Only continue when the condition is applicable to the Type of the member
                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;
            }
        }

    Monday, September 16, 2013 9:06 AM