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

本文关键字:删除 记录 排除 ExpressionVisitor 使用 | 更新日期: 2023-09-27 18:21:27

我有一个在数据库中实现软删除的框架(可为Null的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的实体正在使用存储库,该实体具有名为Children的导航属性,AllInclude方法将正确地过滤掉软删除的Parent记录,但软删除的Children记录仍将被包括在内。

查看发送到数据库的查询,似乎只需要将其添加到sql联接子句"AND Children.DeletedDate is NULL"中,查询就会返回正确的结果

在我的研究过程中,我发现了这个帖子,这似乎正是我所需要的,但我的实现并没有得到与海报相同的结果。在代码中,查询的Children部分似乎没有发生任何变化。

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

基本类:

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

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

问题是您希望在AllInclude方法中使用Include()语句添加条件。queryinterceptor包不支持Include()方法。唯一的解决方案是不使用Include语句。

当你做以下事情时,一切都会起作用:

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

因此,当以上内容转换为sql时,您将看到Where VatTypes.IsDeleted=0被添加到查询中。

真的有必要使用includeAll方法吗?从性能的角度来看,这看起来像是一个巨大的开销,因为你要从数据库加载所有内容。

编辑:在再次阅读了一些旧的帖子后,看起来实际上应该可以将InterceptWith方法与Include()语句一起使用。也许是ExpressionVisitor与Include()有问题。如果我有时间,我会试试这个,然后给你回复。

我一直无法计算出表达式访问者,并且已经花了足够的时间。因此,如果DeletedDate不为空,我最终只是在表触发器中删除记录来处理这一问题。

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

对于我正在审核的每个表,我已经有了一个"更新后"answers"删除后"触发器,并且每个表都有一个关联的审核表。触发器基本上在任何更新或删除时都会将旧记录插入到审核表中。审核表和触发器是通过存储过程创建的:

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;

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

就我个人而言,我讨厌在表中添加"IsDeleted"列的设计模式。原因有很多。

  1. 该模式产生了一个内部平台,在数据库中有一个数据库
  2. 访问内部数据库(select * from table where IsDeleted = 0)所需的自定义API和(delete from table becomes update table set IsDeleted = 1
  3. 表中的额外数据会降低性能
  4. 额外的数据对审计没有用处,如果你想进行审计,请正确进行

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

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

然而,当使用任何形式的抽象时,您都会发现性能会降低。在这种情况下,主要的权衡是SELECT。使用SQL Server Enterprise Edition,可以在视图中添加索引(并让SQL Server自动使用该索引),以加快所有选择的速度,但要牺牲写访问权限。这就解决了第3点。

关于第4点。我更喜欢使用以下模式而不是IsDeleted列。。。

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

创建新行时,将ValidFrom设置为UTCNOW(),将EditedBy设置为CURRENTUSER()。更新行时,将旧行的ValidTo设置为UTCNOW(),并使用正确的值创建新行。删除时,将旧行的ValidTo设置为UTCNOW()

此模式允许您在任何时间点都可以获得表的完整历史视图。全面审计。:)