使用ExpressionVisitor排除连接中的软删除记录

Question

我有一个在数据库中实现软删除的框架(可空DateTime，称为DeletedDate)。我正在使用一个存储库来处理主要实体请求，如下所示：

/// <summary>
/// Returns a Linq Queryable instance of the entity collection.
/// </summary>
public IQueryable<T> All
{
    get { return Context.Set<T>().Where(e => e.DeletedDate == null); }
}

这很好，但我遇到的问题是何时包含导航属性，以及如何确保只查询活动记录。所讨论的存储库方法的开头如下：

/// <summary>
/// Returns a Linq Queryable instance of the entity collection, allowing connected objects to be loaded.
/// </summary>
/// <param name="includeProperties">Connected objects to be included in the result set.</param>
/// <returns>An IQueryable collection of entity.</returns>
public IQueryable<T> AllIncluding(params Expression<Func<T, object>>[] includeProperties)
{
    IQueryable<T> query = Context.Set<T>().Where(e => e.DeletedDate == null);

    foreach (var includeProperty in includeProperties)
    {
        query = query.Include(includeProperty);
    }

    return query;
}

因此，如果一个名为Parent的实体使用该存储库，该实体具有名为called的导航属性，则AllIncluding方法将正确地筛选出已删除的软父记录，但仍将包含软删除的子记录。

查看发送到数据库的查询，似乎只需添加到sql子句“和Children.DeletedDate为NULL”，查询将返回正确的结果。

在我的研究中，我发现这个职位似乎正是我所需要的，但是我的实现并没有得到与海报相同的结果。通过遍历代码，查询中的子部分似乎什么也没有发生。

下面是我当前的相关代码(注意:使用nuget的QueryInterceptor )

BaseClass：

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DomainClasses
{
    /// <summary>
    /// Serves as the Base Class for All Data Model Classes
    /// </summary>
    public class BaseClass
    {
        /// <summary>
        /// Default constructor, sets EntityState to Unchanged.
        /// </summary>
        public BaseClass()
        {
            this.StateOfEntity = DomainClasses.StateOfEntity.Unchanged;
        }

        /// <summary>
        /// Indicates the current state of the entity. Not mapped to Database.
        /// </summary>
        [NotMapped]
        public StateOfEntity StateOfEntity { get; set; }

        /// <summary>
        /// The entity primary key.
        /// </summary>
        [Key, Column(Order = 0), ScaffoldColumn(false)]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        /// <summary>
        /// The date the entity record was created. Updated in InsightDb.SaveChanges() method
        /// </summary>
        [Column(Order = 1, TypeName = "datetime2"), ScaffoldColumn(false)]
        public DateTime AddDate { get; set; }

        /// <summary>
        /// The UserName of the User who created the entity record. Updated in InsightDb.SaveChanges() method
        /// </summary>
        [StringLength(56), Column(Order = 2), ScaffoldColumn(false)]
        public string AddUser { get; set; }

        /// <summary>
        /// The date the entity record was modified. Updated in InsightDb.SaveChanges() method
        /// </summary>
        [Column(Order = 3, TypeName = "datetime2"), ScaffoldColumn(false)]
        public DateTime ModDate { get; set; }

        /// <summary>
        /// The UserName of the User who modified the entity record.
        /// </summary>
        [StringLength(56), Column(Order = 4), ScaffoldColumn(false)]
        public string ModUser { get; set; }

        /// <summary>
        /// Allows for Soft Delete of records.
        /// </summary>
        [Column(Order = 5, TypeName = "datetime2"), ScaffoldColumn(false)]
        public DateTime? DeletedDate { get; set; }
    }
}

家长班：

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace DomainClasses
{
    /// <summary>
    /// The Parent Entity.
    /// </summary>
    public class Parent : BaseClass
    {
        /// <summary>
        /// Instantiates a new instance of Parent, initializes the virtual sets.
        /// </summary>
        public Parent()
        {
            this.Children = new HashSet<Child>();
        }

        #region Properties

        /// <summary>
        /// The Parent's Name
        /// </summary>
        [StringLength(50), Required, Display(Name="Parent Name")]
        public string Name { get; set; }

        #endregion

        #region Relationships
        /// <summary>
        /// Relationship to Child, 1 Parent = Many Children.
        /// </summary>
        public virtual ICollection<Child> Children { get; set; }

        #endregion
    }
}

儿童班：

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DomainClasses
{
    /// <summary>
    /// The Child entity. One Parent = Many Children
    /// </summary>
    public class Child : BaseClass
    {
        #region Properties

        /// <summary>
        /// Child Name.
        /// </summary>
        [Required, StringLength(50), Display(Name="Child Name")]
        public string Name { get; set; }

        #endregion

        #region Relationships
        /// <summary>
        /// Parent Relationship. 1 Parent = Many Children.
        /// </summary>
        public virtual Parent Parent { get; set; }

        #endregion
    }
}

上下文类：

using DomainClasses;
using System;
using System.Data;
using System.Data.Entity;
using System.Linq;

namespace DataLayer
{
    public class DemoContext : DbContext, IDemoContext
    {
        /// <summary>
        /// ActiveSession object of the user performing the action.
        /// </summary>
        public ActiveSession ActiveSession { get; private set; }

        public DemoContext(ActiveSession activeSession)
            : base("name=DemoDb")
        {
            ActiveSession = activeSession;
            this.Configuration.LazyLoadingEnabled = false;
        }

        #region Db Mappings

        public IDbSet<Child> Children { get; set; }
        public IDbSet<Parent> Parents { get; set; }

        #endregion

        public override int SaveChanges()
        {
            var changeSet = ChangeTracker.Entries<BaseClass>();

            if (changeSet != null)
            {
                foreach (var entry in changeSet.Where(c => c.State != EntityState.Unchanged))
                {
                    entry.Entity.ModDate = DateTime.UtcNow;
                    entry.Entity.ModUser = ActiveSession.UserName;

                    if (entry.State == EntityState.Added)
                    {
                        entry.Entity.AddDate = DateTime.UtcNow;
                        entry.Entity.AddUser = ActiveSession.UserName;
                    }
                    else if (entry.State == EntityState.Deleted)
                    {
                        entry.State = EntityState.Modified;
                        entry.Entity.DeletedDate = DateTime.UtcNow;
                    }
                }
            }

            return base.SaveChanges();
        }

        public new IDbSet<T> Set<T>() where T : BaseClass
        {
            return ((DbContext)this).Set<T>();
        }
    }
}

存储库类：

using DomainClasses;
using QueryInterceptor;
using System;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;

namespace DataLayer
{ 
    /// <summary>
    /// Entity Repository to be used in Business Layer.
    /// </summary>
    public class EntityRepository<T> : IEntityRepository<T> where T : BaseClass
    {
        public IDemoContext Context { get; private set; }

        /// <summary>
        /// Main Constructor for Repository. Creates an instance of DemoContext (derives from DbContext).
        /// </summary>
        /// <param name="activeSession">UserName of the User performing the action.</param>
        public EntityRepository(ActiveSession activeSession)
            : this(new DemoContext(activeSession))
        {
        }

        /// <summary>
        /// Constructor for Repository. Allows a context (i.e. FakeDemoContext) to be passed in for testing.
        /// </summary>
        /// <param name="context">IDemoContext to be used in the repository. I.e. FakeDemoContext.</param>
        public EntityRepository(IDemoContext context)
        {
            Context = context;
        }

        /// <summary>
        /// Returns a Linq Queryable instance of the entity collection.
        /// </summary>
        public IQueryable<T> All
        {
            get { return Context.Set<T>().Where(e => e.DeletedDate == null); }
        }

        /// <summary>
        /// Returns a Linq Queryable instance of the entity collection, allowing connected objects to be loaded.
        /// </summary>
        /// <param name="includeProperties">Connected objects to be included in the result set.</param>
        /// <returns>An IQueryable collection of entity.</returns>
        public IQueryable<T> AllIncluding(params Expression<Func<T, object>>[] includeProperties)
        {
            IQueryable<T> query = Context.Set<T>().Where(e => e.DeletedDate == null);

            InjectConditionVisitor icv = new InjectConditionVisitor();

            foreach (var includeProperty in includeProperties)
            {
                query = query.Include(includeProperty);
            }

            return query.InterceptWith(icv);
        }

        /// <summary>
        /// Finds a single instance of the entity by the Id.
        /// </summary>
        /// <param name="id">The primary key for the entity.</param>
        /// <returns>An instance of the entity.</returns>
        public T Find(int id)
        {
            return Context.Set<T>().Where(e => e.DeletedDate == null).SingleOrDefault(e => e.Id == id);
        }

        /// <summary>
        /// Takes a single entity or entity graph and reads the explicit state, then applies the necessary State changes to Update or Add the entities.
        /// </summary>
        /// <param name="entity">The entity object.</param>
        public void InsertOrUpdate(T entity)
        {
            if (entity.StateOfEntity == StateOfEntity.Added)
            {
                Context.Set<T>().Add(entity);
            }
            else
            {
                Context.Set<T>().Add(entity);
                Context.ApplyStateChanges();
            }
        }

        /// <summary>
        /// Deletes the instance of the entity.
        /// </summary>
        /// <param name="id">The primary key of the entity.</param>
        public void Delete(int id)
        {
            var entity = Context.Set<T>().Where(e => e.DeletedDate == null).SingleOrDefault(e => e.Id == id);
            entity.StateOfEntity = StateOfEntity.Deleted;
            Context.Set<T>().Remove(entity);
        }

        /// <summary>
        /// Saves the transaction.
        /// </summary>
        public void Save()
        {
            Context.SaveChanges();
        }

        /// <summary>
        /// Disposes the Repository.
        /// </summary>
        public void Dispose() 
        {
            Context.Dispose();
        }
    }
}

InjectConditionVisitor类：

using System;
using System.Linq;
using System.Linq.Expressions;

namespace DataLayer
{
    public class InjectConditionVisitor : ExpressionVisitor
    {
        private QueryConditional queryCondition;

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

        public InjectConditionVisitor()
        {
            queryCondition = new QueryConditional(x => x.DeletedDate == null);
        }

        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.Conditional.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类：

using DomainClasses;
using System;
using System.Linq.Expressions;

namespace DataLayer
{
    public class QueryConditional
    {
        public QueryConditional(Expression<Func<BaseClass, bool>> ex)
        {
            Conditional = ex;
        }

        public Expression<Func<BaseClass, bool>> Conditional { get; set; }
    }
}

ParameterRebinder类：

using System.Collections.Generic;
using System.Linq.Expressions;

namespace DataLayer
{
    public class ParameterRebinder : ExpressionVisitor
    {
        private readonly Dictionary<ParameterExpression, ParameterExpression> map;

        public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
        {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {
            return new ParameterRebinder(map).Visit(exp);
        }

        protected override Expression VisitParameter(ParameterExpression node)
        {
            ParameterExpression replacement;

            if (map.TryGetValue(node, out replacement))
                node = replacement;

            return base.VisitParameter(node);
        }
    }
}

IEntityRepository接口：

using System;
using System.Linq;
using System.Linq.Expressions;

namespace DataLayer
{
    public interface IEntityRepository<T> : IDisposable
    {
        IQueryable<T> All { get; }
        IQueryable<T> AllIncluding(params Expression<Func<T, object>>[] includeProperties);
        T Find(int id);
        void InsertOrUpdate(T entity);
        void Delete(int id);
        void Save();
    }
}

IDemoContext接口：

using DomainClasses;
using System;
using System.Data.Entity;

namespace DataLayer
{
    public interface IDemoContext : IDisposable
    {
        ActiveSession ActiveSession { get; }

        IDbSet<Child> Children { get; }
        IDbSet<Parent> Parents { get; }

        int SaveChanges();

        IDbSet<T> Set<T>() where T : BaseClass;
    }
}

Answer 1

我从来没能找出访客的表达方式，而且我已经花了足够的时间在上面。因此，如果DeletedDate不是null，我将删除记录，从而在表触发器中处理这个问题。

软删除的最初目的是跟踪谁删除了应用程序中的记录。我在保存更改上下文中设置了Mod用户，但是在删除时它不会被更新，所以没有对是谁做的删除进行审计。

对于我正在审核的每个表，我已经有了一个“后更新”和“后删除”触发器，并为每个表设置了一个相关的审计表。触发器基本上是在有更新或删除的时候将旧记录插入审计表。审计表和触发器是通过存储过程创建的：

CREATE PROCEDURE [dbo].[CreateAuditTable](
    @TableName NVARCHAR(100),
    @SchemaName NVARCHAR(50)
)
as
/*
-----------------------------------------------------------------------------------------------------
 * Procedure Name   : dbo.CreateAuditTable
 * Author           : Josh Jay
 * Date             : 03/15/2013
 * Description      : Creates an Audit table from an existing table.
-----------------------------------------------------------------------------------------------------
 Sl No      Date Modified       Modified By         Changes 
-------     -------------       -----------------   -------------------------------------------------
  1         07/01/2013          Josh Jay            Removed the table alias parameter and replaced usage with table name.
  2         08/28/2013          Josh Jay            Modified the Update Statement to Delete the Row if it is a Soft Delete.
-----------------------------------------------------------------------------------------------------

Ex:
EXEC dbo.CreateAuditTable
    @TableName = 'Product',
    @SchemaName = 'dbo'

*/
BEGIN
DECLARE @IssueCount INT = 0,
        @IssueList NVARCHAR(MAX) = NULL,
        @LineBreak NVARCHAR(50) = REPLICATE('-',50),
        @CreateTableScript NVARCHAR(MAX) = NULL,
        @CreateDeleteScript NVARCHAR(MAX) = NULL,
        @CreateUpdateScript NVARCHAR(MAX) = NULL,
        @ColumnNamesSection NVARCHAR(MAX) = NULL,
        @TableObjectId INT,
        @msg varchar(1024);

--1) Check if table exists
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName)
        BEGIN
            SET @IssueCount = @IssueCount + 1;
            SET @IssueList = ISNULL(@IssueList + CHAR(10),'') + CONVERT(VARCHAR,@IssueCount) + ') The table ' + @SchemaName + '.' + @Tablename + ' does not exist.';
        END;

--2) Check if audit table exists
    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName + '_Audit')
        BEGIN
            SET @IssueCount = @IssueCount + 1;
            SET @IssueList = ISNULL(@IssueList + CHAR(10),'') + CONVERT(VARCHAR,@IssueCount) + ') The audit table ' + @SchemaName + '.' + @Tablename + '_Audit already exists. To recreate the audit table, please drop the existing audit table and try again.';
        END;

--3) Check for existing triggers
    IF EXISTS (SELECT 1 FROM sys.triggers tr INNER JOIN sys.tables t on tr.parent_id = t.object_id
                WHERE t.schema_id = SCHEMA_ID(@SchemaName) AND t.name = @TableName AND tr.name LIKE 'tg_%Audit_%')
        BEGIN
            SET @IssueCount = @IssueCount + 1;
            SET @IssueList = ISNULL(@IssueList + CHAR(10),'') + CONVERT(VARCHAR,@IssueCount) + ') At least one audit trigger exists on the ' + @SchemaName + '.' + @Tablename + ' table. To recreate the audit table, please drop the audit triggers.';
        END;

--4) Print errors if there are any
    IF @IssueCount > 0
        BEGIN
            PRINT('There were ' + CONVERT(VARCHAR,@IssueCount) + ' issues found when attempting to create the audit table. Please correct the issues below before trying again.');
            PRINT(@LineBreak);
            PRINT(@IssueList);
            RETURN;
        END;

--5) Build Scripts
    select
        @CreateTableScript = 
            'CREATE TABLE [' + SS.name + '].[' + ST.name + '_Audit]' + CHAR(10) +
            '(' + CHAR(10) +
            CHAR(9) + '[AuditId] INT IDENTITY(1,1) NOT NULL CONSTRAINT [pk_' + @SchemaName + '.' + @Tablename + '_Audit_AuditId] PRIMARY KEY,' + CHAR(10) +
            CHAR(9) + '[AuditDate] DATETIME NOT NULL CONSTRAINT [df_' + @SchemaName + '.' + @Tablename + '_Audit_AuditDate] DEFAULT (getutcdate()),' + CHAR(10) +
            CHAR(9) + '[AuditIsDelete] BIT NOT NULL CONSTRAINT [df_' + @SchemaName + '.' + @Tablename + '_Audit_AuditIsDelete] DEFAULT ((0))',
        @CreateDeleteScript = 
            'CREATE TRIGGER [dbo].[tg_' + @SchemaName + '.' + @Tablename + '_Audit_Delete]' + CHAR(10) +
            'ON [' + SS.name + '].[' + ST.name + ']' + CHAR(10) +
            'After Delete' + CHAR(10) +
            'As Begin' + CHAR(10) +
            CHAR(9) + 'IF TRIGGER_NESTLEVEL() > 1' + CHAR(10) +
            CHAR(9) + CHAR(9) + 'Return' + CHAR(10) +
            CHAR(10) +
            CHAR(9) + 'INSERT INTO' + CHAR(10) +
            CHAR(9) + CHAR(9) + '[' + SS.name + '].[' + ST.name + '_Audit] (' + CHAR(10) +
            CHAR(9) + CHAR(9) + CHAR(9) + '[AuditIsDelete]',
        @CreateUpdateScript = 
            'CREATE TRIGGER [dbo].[tg_' + @SchemaName + '.' + @Tablename + '_Audit_Update]' + CHAR(10) +
            'ON [' + SS.name + '].[' + ST.name + ']' + CHAR(10) +
            'After Update' + CHAR(10) +
            'As Begin' + CHAR(10) +
            CHAR(9) + 'IF TRIGGER_NESTLEVEL() > 1' + CHAR(10) +
            CHAR(9) + CHAR(9) + 'Return' + CHAR(10) +
            CHAR(10) +
            CHAR(9) + 'INSERT INTO' + CHAR(10) +
            CHAR(9) + CHAR(9) + '[' + SS.name + '].[' + ST.name + '_Audit] (' + CHAR(10) +
            CHAR(9) + CHAR(9) + CHAR(9) + '[AuditIsDelete]'
    from
        sys.tables ST
        INNER JOIN
        sys.schemas SS ON ST.schema_id = SS.schema_id
    WHERE
        ST.name = @TableName AND
        ST.type = 'U' AND
        SS.name = @SchemaName

    SELECT
        @CreateTableScript = @CreateTableScript + ',' + CHAR(10) + CHAR(9) + '[' + ISC.COLUMN_NAME + '] ' + ISC.DATA_TYPE + CASE WHEN ISC.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND ISC.DATA_TYPE <> 'xml' THEN '(' + CASE WHEN ISC.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CONVERT(varchar,ISC.CHARACTER_MAXIMUM_LENGTH) END + ')' ELSE '' END + ' NULL',
        @ColumnNamesSection = ISNULL(@ColumnNamesSection,'') + ',' + CHAR(10) + CHAR(9) + CHAR(9) + CHAR(9) + '[' + ISC.COLUMN_NAME + ']'
    FROM
        INFORMATION_SCHEMA.COLUMNS ISC
    WHERE
        ISC.TABLE_NAME = @TableName AND
        ISC.TABLE_SCHEMA = @SchemaName
    ORDER BY
        ISC.ORDINAL_POSITION ASC

    SET @CreateTableScript = @CreateTableScript + CHAR(10) + ');'

    SET @CreateDeleteScript = @CreateDeleteScript + @ColumnNamesSection + CHAR(10) +
        CHAR(9) + CHAR(9) + ')' + CHAR(10) +
        CHAR(9) + CHAR(9) + 'SELECT' + CHAR(10) +
        CHAR(9) + CHAR(9) + CHAR(9) + '1 as [AuditIsDelete]' +
        @ColumnNamesSection + CHAR(10) +
        CHAR(9) + CHAR(9) + 'FROM' + CHAR(10) +
        CHAR(9) + CHAR(9) + CHAR(9) + 'deleted' + CHAR(10) +
        'End;'

    SET @CreateUpdateScript = @CreateUpdateScript + @ColumnNamesSection + CHAR(10) +
        CHAR(9) + CHAR(9) + ')' + CHAR(10) +
        CHAR(9) + CHAR(9) + 'SELECT' + CHAR(10) +
        CHAR(9) + CHAR(9) + CHAR(9) + '0 as [AuditIsDelete]' +
        @ColumnNamesSection + CHAR(10) +
        CHAR(9) + CHAR(9) + 'FROM' + CHAR(10) +
        CHAR(9) + CHAR(9) + CHAR(9) + 'deleted' + CHAR(10) +
        'declare @SoftDelete bit,
            @Id int

    select
        @SoftDelete = case when i.DeletedDate is not null then 1 else 0 end,
        @Id = i.Id
    from
        inserted i;

    if @SoftDelete = 1
        begin
            INSERT INTO
                [' + @SchemaName + '].[' + @TableName + '_Audit] (
                    [AuditIsDelete]
                    ' + @ColumnNamesSection + '
                )
                SELECT
                    1 as [AuditIsDelete]
                    ' + @ColumnNamesSection + '
                FROM
                    inserted

            delete from ' + @SchemaName + '.' + @TableName + ' where Id = @Id
        end;' + CHAR(10) +

        'End;'

--6) Print and Run Scripts
    BEGIN TRY
        BEGIN TRANSACTION;

        EXEC(@CreateTableScript);

        EXEC(@CreateDeleteScript);

        EXEC(@CreateUpdateScript);

        --Test Try Catch:
        --SELECT 1/0

        COMMIT TRANSACTION;

        PRINT('The audit table was successfully created.')
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;

        set @msg = 
            'db_name()=' + isnull( db_name(), 'NULL' ) + '; ERROR_MESSAGE()=' +
            isnull( ERROR_MESSAGE(), 'NULL' ) +
            '; ERROR_PROCEDURE()=' + isnull( ERROR_PROCEDURE(), 'NULL' ) +
            '; ERROR_LINE()=' + isnull( CONVERT( varchar(10), ERROR_LINE() ), 'NULL' ) +    
            '; ERROR_NUMBER()=' + isnull( CONVERT( varchar(10), ERROR_NUMBER() ), 'NULL' ) +
            '; ERROR_SEVERITY()=' + isnull( CONVERT( varchar(10), ERROR_SEVERITY() ), 'NULL' ) +
            '; ERROR_STATE()=' + isnull( CONVERT( varchar(10), ERROR_STATE() ), 'NULL' );

        PRINT(CHAR(10) + 'Create Audit Table Script:');
        PRINT(@LineBreak);
        PRINT(@CreateTableScript);
        PRINT(@LineBreak);

        PRINT(CHAR(10) + 'Create Audit Delete Trigger Script:');
        PRINT(@LineBreak);
        PRINT(@CreateDeleteScript);
        PRINT(@LineBreak);

        PRINT(CHAR(10) + 'Create Audit Update Trigger Script:');
        PRINT(@LineBreak);
        PRINT(@CreateUpdateScript);
        PRINT(@LineBreak);

        raiserror ( @msg, 18, 1 );
    END CATCH
END;

虽然触发器并不理想，但它们完成了审核已删除用户的目标，我不再需要担心软删除记录。

Answer 2

问题在于，您希望使用AllIncluding方法中的Include()语句添加条件。Query拦截器包不支持Include()方法。实现此工作的唯一解决方案是不使用Include语句。

当您执行如下操作时，一切都正常：

Articles.Select(x => new {
Vat = x.VatTypes
})
.InterceptWith(Visitor);

因此，当将上述内容转换为sql时，您将看到在查询中添加了一个Where VatTypes.IsDeleted =0。

是否真的有必要有一个includeAll方法，从性能角度看，这对我来说是一个巨大的开销，因为您正在从数据库加载所有的东西。

编辑:在再次阅读了一些旧的帖子之后，看起来应该可以使用包含()语句的InterceptWith方法了。可能是ExpressionVisitor对Include()有问题。如果我有时间的话，我会试试的，然后再给你回电。

Answer 3

就我个人而言，我讨厌将"IsDeleted“列添加到表中的设计模式。原因很多。

1. 该模式生成一个内部平台，在数据库中有一个数据库。
2. 访问内部数据库(select * from table where IsDeleted = 0)和(delete from table becomes update table set IsDeleted = 1 )所需的自定义API
3. 表中的额外数据会降低性能。
4. 额外的数据对于审计目的并不有用，如果您想要审核，请正确地执行它。

您遇到的痛点是2.自定义API.实体框架的创建是为了对付SQL数据库，而不是SQL数据库中存在的一些奇怪的数据存储。

解决此问题的解决方案是使用Server视图。支持视图，您可以使用软删除打开对行进行筛选。然后，我将在视图中添加一个TRIGGER INSTEAD OF INSERT,UPDATE, DELETE，将插入/更新/删除映射到数据库上的正确操作。

然而，当使用任何形式的抽象时，您会发现性能下降。在这种情况下，主要的权衡是SELECT。使用，可以在视图中添加索引(并让Server自动使用索引)，从而以牺牲写访问为代价，加快所有选择。这就是第3点。

至于第4点，我更喜欢使用以下模式而不是IsDeleted列.

• ValidFrom DateTime NOT NULL
• ValidTo DateTime NULL
• EditedBy VARCHAR NOT NULL

创建新行时，可以将ValidFrom设置为UTCNOW()，将EditedBy设置为CURRENTUSER()。更新一行时，可以将旧行的ValidTo设置为UTCNOW()，并创建具有正确值的新行。删除时，可以将旧行的ValidTo设置为UTCNOW()。

此架构允许您在任何时间点对您的表拥有完整的历史视图。全面审计。:)