ExpressionVisitor软删除 [英] ExpressionVisitor soft delete

问题描述

我们在实体框架中实现了软删除功能的一些问题。这个想法是使用知道EF上下文的存储库。在存储库的级别上，我们实现了一个插件系统，每当在存储库上执行一个操作时，这些插件都将被执行。例如，当我们调用 Repository.GetQuery< Relation>（）时，插件将被执行。其中一个插件是一个 LogicalDeletePlugin ，此插件应该添加一个 Where（x => x.IsDeleted）语句到选择中的每个表。这个想法是使用访问linq表达式的 ExpressionVisitor 实现这个 IsDeleted 插件，并查找所有的表选择语句并添加 IsDeleted 条件。

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);
    }
}

上述c＃代码将导致以下SQL代码：

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

正如您在生成的SQL查询中可以看到的， IsDeleted 语句添加到 TestWorks = x.Bonus.Where（y =>！y.IsDeleted）选择代码。这就是 TestVisitor 目前正在做的事情。但是现在的问题是我们如何可以在另外的选择上实现这个， x => ！x.IsDeleted 不会添加到 Test = x.Bonus 部分。

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.

ExpressionVisitor是否是正确的方法来完成此操作，还是应该使用其他解决方案？所有的帮助是赞赏！如果解释不够清楚，只需让我知道，我会尝试提供一些额外的信息！

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!

编辑：

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);
    }

上面是我添加到ExpressionVisitor的内容。现在当我取消注释返回Expression.MamkeMemberaccess代码时抛出一个异常，因为它不会期望一个MemberExpression或某些东西。

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.

以下是我想出的解决方案： / p>

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是一个类， code>表达式< Func< T，bool>> 。

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

InjectconditionVisitor可以与InterceptWith（QueryInterceptor NuGet软件包）一起使用，如 query.InterceptWith（new InjectConditionVisitor（new QueryConditional（x => x.Deleted == true））。

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

推荐答案

胜利！今天我创建了一个ExpressionVisitor它将IsDeleted where子句附加到每个选择，即使在导航属性中！

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

这篇关于ExpressionVisitor软删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！