使用ExpressionVisitor排除连接中的软删除记录 [英] Use ExpressionVisitor to Exclude Soft Deleted Records In Joins

问题描述

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

  ///< summary> 
 ///返回实体集合的Linq Queryable实例。 
 ///< / summary> 
 public IQueryable< T>所有
 {
 get {return Context.Set< T>（）。Where（e => e.DeletedDate == null）; } 
} 
  

这很好，但我遇到的问题是当你包括导航属性，以及如何确保仅查询活动记录。存储库方法开始如下：

  ///< summary> 
 ///返回实体集合的Linq Queryable实例，允许连接的对象被加载。 
 ///< / summary> 
 ///< param name =includeProperties>要包含在结果集中的连接对象。< / param> 
 ///< returns>实体的IQueryable集合< / returns> 
 public IQueryable< T> AllIncluding（params Expression< Func< T，object>> [] includeProperties）
 {
 IQueryable< T> query = Context.Set< T>（）。其中​​（e => e.DeletedDate == null）; 
 
 foreach（var includeProperty in includeProperties）
 {
 query = query.Include（includeProperty）; 
} 
 
返回查询; 
} 
  

所以如果一个名为Parent的实体使用Repository，该实体有一个导航属性称为儿童，AllIncluding方法将适当地过滤出软删除的父记录，但仍将包含软删除的儿童记录。

查看查询发送到数据库，似乎所有需要做的是添加到sql连接子句AND Children.DeletedDate IS NULL，查询将返回正确的结果。

在我的研究期间，我发现了这篇文章，其中似乎正是我需要的，但我的执行并不能得到相同的结果，海报。

这是我当前的相关代码（注意：从nuget使用QueryInterceptor）

/ em>：

BaseClass：

  using System; 
使用System.ComponentModel.DataAnnotations; 
使用System.ComponentModel.DataAnnotations.Schema; 
 
命名空间DomainClasses 
 {
 ///< summary> 
 ///作为所有数据模型类的基类
 ///< / summary> 
 public class BaseClass 
 {
 ///< summary> 
 ///默认构造函数，将EntityState设置为不变。 
 ///< / summary> 
 public BaseClass（）
 {
 this.StateOfEntity = DomainClasses.StateOfEntity.Unchanged; 
} 
 
 ///< summary> 
 ///表示实体的当前状态。未映射到数据库。 
 ///< / summary> 
 [NotMapped] 
 public StateOfEntity StateOfEntity {get;组; } 
 
 ///< summary> 
 ///实体主键。 
 ///< / summary> 
 [Key，Column（Order = 0），ScaffoldColumn（false）] 
 [DatabaseGeneratedAttribute（DatabaseGeneratedOption.Identity）] 
 public int Id {get;组; } 
 
 ///< summary> 
 ///创建实体记录的日期。在InsightDb.SaveChanges（）方法中更新
 ///< / summary> 
 [列（Order = 1，TypeName =datetime2），ScaffoldColumn（false）] 
 public DateTime AddDate {get;组; } 
 
 ///< summary> 
 ///创建实体记录的用户的UserName。在InsightDb.SaveChanges（）方法中更新
 ///< / summary> 
 [StringLength（56），Column（Order = 2），ScaffoldColumn（false）] 
 public string AddUser {get;组; } 
 
 ///< summary> 
 ///修改实体记录的日期。在InsightDb.SaveChanges（）方法中更新
 ///< / summary> 
 [列（Order = 3，TypeName =datetime2），ScaffoldColumn（false）] 
 public DateTime ModDate {get;组; } 
 
 ///< summary> 
 ///修改实体记录的用户的UserName。 
 ///< / summary> 
 [StringLength（56），Column（Order = 4），ScaffoldColumn（false）] 
 public string ModUser {get;组; } 
 
 ///< summary> 
 ///允许软删除记录。 
 ///< / summary> 
 [Column（Order = 5，TypeName =datetime2），ScaffoldColumn（false）] 
 public DateTime？ DeletedDate {get;组; 
} 
} 
  

父类：

 使用System.Collections.Generic; 
使用System.ComponentModel.DataAnnotations; 
 
命名空间DomainClasses 
 {
 ///< summary> 
 ///父实体。 
 ///< / summary> 
 public class Parent：BaseClass 
 {
 ///< summary> 
 ///实例化一个新的Parent实例，初始化虚拟集。 
 ///< / summary> 
 public Parent（）
 {
 this.Children = new HashSet< Child>（）; 
} 
 
 #region属性
 
 ///< summary> 
 ///父母的名字
 ///< / summary> 
 [StringLength（50），Required，Display（Name =Parent Name）] 
 public string Name {get;组; 
 
 #endregion 
 
 #region关系
 ///< summary> 
 ///与孩子的关系，1父母=许多孩子。 
 ///< / summary> 
 public virtual ICollection< Child>孩子{get;组; } 
 
 #endregion 
} 
} 
  

小孩类：

  using System.Collections.Generic; 
使用System.ComponentModel.DataAnnotations; 
使用System.ComponentModel.DataAnnotations.Schema; 
 
命名空间DomainClasses 
 {
 ///< summary> 
 ///孩子实体。 One Parent = Many Children 
 ///< / summary> 
 public class Child：BaseClass 
 {
 #region属性
 
 ///< summary> 
 ///子名称。 
 ///< / summary> 
 [必需，StringLength（50），显示（Name =Child Name）] 
 public string Name {get;组; 
 
 #endregion 
 
 #region关系
 ///< summary> 
 ///父关系。 1父母=许多孩子。 
 ///< / summary> 
 public virtual Parent Parent {get;组; } 
 
 #endregion 
} 
} 
  

上下文类：

 使用DomainClasses; 
使用系统; 
使用System.Data; 
使用System.Data.Entity; 
使用System.Linq; 
 
命名空间DataLayer 
 {
 public class DemoContext：DbContext，IDemoContext 
 {
 ///< summary> 
 ///执行该操作的用户的ActiveSession对象。 
 ///< / summary> 
 public ActiveSession ActiveSession {get;私人集合
 
 public DemoContext（ActiveSession activeSession）
：base（name = DemoDb）
 {
 ActiveSession = activeSession; 
 this.Configuration.LazyLoadingEnabled = false; 
} 
 
 #region Db Mappings 
 
 public IDbSet< Child>孩子{get;组; } 
 public IDbSet< Parent>父母{get;组; 
 
 #endregion 
 
 public override int SaveChanges（）
 {
 var changeSet = ChangeTracker.Entries< BaseClass>（）; 
 
 if（changeSet！= null）
 {
 foreach（varSet 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>设置< T>（）其中T：BaseClass 
 {
 return（（DbContext）this）.Set< T> 
} 
} 
} 
  

存储库类：

 使用DomainClasses; 
使用QueryInterceptor; 
使用系统; 
使用System.Data.Entity; 
使用System.Linq; 
使用System.Linq.Expressions; 
 
命名空间DataLayer 
 {
 ///< summary> 
 ///在业务层中使用的实体存储库。 
 ///< / summary> 
 public class EntityRepository< T> ：IEntityRepository< T>其中T：BaseClass 
 {
 public IDemoContext Context {get;私人集合} 
 
 ///< summary> 
 ///存储库的主要构造方法。创建一个DemoContext的实例（派生自DbContext）。 
 ///< / summary> 
 ///< param name =activeSession>执行操作的用户的UserName。< / param> 
 public EntityRepository（ActiveSession activeSession）
：this（new DemoContext（activeSession））
 {
} 
 
 ///< summary> 
 ///存储库的构造方法。允许上传（即FakeDemoContext）传入进行测试。 
 ///< / summary> 
 ///< param name =context>要在存储库中使用的IDemoContext。即FakeDemoContext< / PARAM> 
 public EntityRepository（IDemoContext context）
 {
 Context = context; 
} 
 
 ///< summary> 
 ///返回实体集合的Linq Queryable实例。 
 ///< / summary> 
 public IQueryable< T>所有
 {
 get {return Context.Set< T>（）。Where（e => e.DeletedDate == null）; } 
} 
 
 ///< summary> 
 ///返回实体集合的Linq Queryable实例，允许连接的对象被加载。 
 ///< / summary> 
 ///< param name =includeProperties>要包含在结果集中的连接对象。< / param> 
 ///< returns>实体的IQueryable集合< / returns> 
 public IQueryable< T> AllIncluding（params Expression< Func< T，object>> [] includeProperties）
 {
 IQueryable< T> query = Context.Set< T>（）。其中​​（e => e.DeletedDate == null）; 
 
 InjectConditionVisitor icv = new InjectConditionVisitor（）; 
 
 foreach（var includeProperty in includeProperties）
 {
 query = query.Include（includeProperty）; 
} 
 
 return query.InterceptWith（icv）; 
} 
 
 ///< summary> 
 ///通过Id查找实体的单个实例。 
 ///< / summary> 
 ///< param name =id>实体的主键。< / param> 
 ///< returns>实体的实例。< / returns> 
 public T Find（int id）
 {
 return Context.Set&T;（）。其中​​（e => e.DeletedDate == null）.SingleOrDefault（e => e.Id == id）; 
} 
 
 ///< summary> 
 ///获取单个实体或实体图形，并读取显式状态，然后将必要的状态更改应用于更新或添加实体。 
 ///< / summary> 
 ///< param name =entity>实体对象< / param> 
 public void InsertOrUpdate（T entity）
 {
 if（entity.StateOfEntity == StateOfEntity.Added）
 {
 Context.Set&T;（）。 （实体）; 
} 
 else 
 {
 Context.Set&T;（）。Add（entity）; 
 Context.ApplyStateChanges（）; 
} 
} 
 
 ///< summary> 
 ///删除实体的实例。 
 ///< / summary> 
 ///< param name =id>实体的主键。< / param> 
 public void Delete（int id）
 {
 var entity = Context.Set&T;（）。其中​​（e => e.DeletedDate == null）.SingleOrDefault（e = > e.Id == id）; 
 entity.StateOfEntity = StateOfEntity.Deleted; 
 Context.Set&T;（）。Remove（entity）; 
} 
 
 ///< summary> 
 ///保存事务。 
 ///< / summary> 
 public void Save（）
 {
 Context.SaveChanges（）; 
} 
 
 ///< summary> 
 ///配置存储库。 
 ///< / summary> 
 public void Dispose（）
 {
 Context.Dispose（）; 
} 
} 
} 
  

InjectConditionVisitor类：

  using System; 
使用System.Linq; 
使用System.Linq.Expressions; 
 
命名空间DataLayer 
 {
 public class InjectConditionVisitor：ExpressionVisitor 
 {
 private QueryConditional queryCondition; 
 
 public InjectConditionVisitor（QueryConditional条件）
 {
 queryCondition = condition; 
} 
 
 public InjectConditionVisitor（）
 {
 queryCondition = new QueryConditional（x => x.DeletedDate == null）; 
} 
 
 protected override表达式VisitMember（MemberExpression ex）
 {
 //仅更改泛型类型=导航属性
 //否则只需执行正常码。 
 return！ex.Type.IsGenericType？ base.VisitMember（ex）：CreateWhereExpression（queryCondition，ex）？ base.VisitMember（前）; 
} 
 
 ///< summary> 
 ///使用适应的QueryConditional 
 ///< / summary>创建where表达式
 ///< param name =condition>使用条件< / param> 
 ///< param name =ex>我们访问的MemberExpression< / param> 
 ///< returns>< / returns> 
 private Expression CreateWhereExpression（QueryConditional condition，Expression ex）
 {
 var type = ex.Type; //。GetGenericArguments（）。 
 var test = CreateExpression（condition，type）; 
 if（test == null）
返回null; 
 var listType = typeof（IQueryable&）; MakeGenericType（type）; 
 return Expression.Convert（Expression.Call（typeof（Enumerable），Where，new Type [] {type}，（Expression）ex，test），listType）; 
} 
 
 ///< summary> 
 ///将查询条件修改为我们正在访问的成员。 
 ///< / summary> 
 ///< param name =condition>适应条件< / param> 
 ///< param name =type>当前成员的类型（= Navigation属性）< / param> 
 ///< returns>适应的QueryConditional< / returns> 
 private LambdaExpression CreateExpression（QueryConditional条件，类型类型）
 {
 var lambda =（LambdaExpression）condition.Conditional; 
 var conditionType = condition.Conditional.GetType（）。GetGenericArguments（）。FirstOrDefault（）; 
 //只有当条件适用于成员的类型时，才继续
 if（conditionType == null）
 return null; 
 if（！conditionType.IsAssignableFrom（type））
 return null; 
 
 var newParams = new [] {Expression.Parameter（type，bo）}; 
 var paramMap = lambda.Parameters.Select（（ori​​ginal，i）=> new {original，replacement = newParams [i]}）。ToDictionary（p => p.original，p =>替代）; 
 var fixedBody = ParameterRebinder.ReplaceParameters（paramMap，lambda.Body）; 
 lambda = Expression.Lambda（fixedBody，newParams）; 
 
返回lambda; 
} 
} 
} 
  

QueryConditional类：

 使用DomainClasses; 
使用系统; 
使用System.Linq.Expressions; 
 
命名空间DataLayer 
 {
 public class QueryConditional 
 {
 public QueryConditional（Expression< Func< BaseClass，bool>> ex）
 {
条件= ex; 
} 
 
 public Expression< Func< BaseClass，bool>>条件{get;组; } 
} 
} 
  

ParameterRebinder类：

 使用System.Collections.Generic; 
使用System.Linq.Expressions; 
 
命名空间DataLayer 
 {
 public class ParameterRebinder：ExpressionVisitor 
 {
 private readonly Dictionary< ParameterExpression，ParameterExpression>地图; 
 
 public ParameterRebinder（Dictionary< ParameterExpression，ParameterExpression> map）
 {
 this.map = map？新字典< ParameterExpression，ParameterExpression>（）; 
 
 
 public static Expression ReplaceParameters（Dictionary< ParameterExpression，ParameterExpression> map，Expression exp）
 {
返回新的ParameterRebinder（map）.Visit（exp）; 
} 
 
 protected override表达式访问参数（ParameterExpression节点）
 {
 ParameterExpression替换; 
 
 if（map.TryGetValue（node，out replacement））
 node = replacement; 
 
返回base.VisitParameter（node）; 
} 
} 
} 
  

IEntityRepository界面：

  using System; 
使用System.Linq; 
使用System.Linq.Expressions; 
 
命名空间DataLayer 
 {
 public interface IEntityRepository< T> ：IDisposable 
 {
 IQueryable< T>全部{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界面：

 使用DomainClasses; 
使用系统; 
使用System.Data.Entity; 
 
命名空间DataLayer 
 {
 public interface IDemoContext：IDisposable 
 {
 ActiveSession ActiveSession {get; } 
 
 IDbSet< Child>孩子{get; } 
 IDbSet< Parent>父母{get; } 
 
 int SaveChanges（）; 
 
 IDbSet< T> Set< T>（）其中T：BaseClass; 
} 
} 
  

解决方案

从来没有能够弄清楚访客的表情，并已经花了足够的时间。所以我最终只是在表触发器中处理这个，如果DeletedDate不为null，删除记录。

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

对于正在审核的每个表以及每个表的相关审计表，我已经有一个After Update和After Delete触发器。只要有更新或删除，触发器基本上将旧记录插入审核表。审计表和触发器是通过存储过程创建的：

  CREATE PROCEDURE [dbo]。[CreateAuditTable]（
 @TableName NVARCHAR（100），
 @SchemaName NVARCHAR（50）
）
 as 
 / * 
 ------------- -------------------------------------------------- -------------------------------------- 
 *过程名称：dbo.CreateAuditTable 
 *作者：Josh Jay 
 *日期：03/15/2013 
 *说明：从现有表创建审核表。 
 ---------------------------------------------- -------------------------------------------------- ----- 
无修改日期修改
 ------- ------------- ---------- ------- ------------------------------------------- ------ 
 1 07/01/2013 Josh Jay删除了表别名参数，并用表名替换了用法。 
 2 08/28/2013 Josh Jay修改了删除行的更新语句（如果是软删除）。 
 ---------------------------------------------- -------------------------------------------------- ----- 
 
例如：
 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）检查表是否存在
 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）+'）表'+ @SchemaName +'。'+ @Tablename +'不存在'; 
 END; 
 
 --2）检查审计表是否存在
如果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）+'）审计表'+ @SchemaName +'。'+ @Tablename +'_Audit已经存在。要重新创建审核表，请删除现有的审核表，然后重试。 
 END; 
 
 --3）检查现有触发器
如果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）+'）'+ @SchemaName +'上至少存在一个审计触发器。 Tablename +'表。要重新创建审计表，请删除审计触发器。 
 END; 
 
 --4）如果有
，则打印错误IF @IssueCount> 0 
 BEGIN 
 PRINT（'尝试创建审核表时发现'+ CONVERT（VARCHAR，@ IssueCount）+'问题，请在重新尝试之前纠正以下问题。 
 PRINT（@LineBreak）; 
 PRINT（@IssueList）; 
返回; 
 END; 
 
 --5）构建脚本
选择
 @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）+ 
' ete'+ CHAR（10）+ 
'As Begin'+ CHAR（10）+ 
 CHAR（9）+'如果TRIGGER_NESTLEVEL（）> 1'+ CHAR（10）+ 
 CHAR（9）+ CHAR（9）+'返回'+ CHAR（10）+ 
 CHAR（10）+ 
 CHAR（9）+' INSERT INTO'+ CHAR（10）+ 
 CHAR（9）+ CHAR（9）+'['+ SS.name +'] ['+ ST.name +'_Audit]（'+ CHAR ）+ 
 CHAR（9）+ CHAR（9）+ CHAR（9）+'[AuditIsDelete]'，
 @CreateUpdateScript = 
'CREATE TRIGGER [dbo]。[tg_'+ @ （'）'+ CHAR（10）+ 
'ON ['+ SS.name +'] ['+ ST.name +']'+ CHAR（10） + 
'更新后'+ CHAR（10）+ 
'作为开始'+ CHAR（10）+ 
 CHAR（9）+'如果TRIGGER_NESTLEVEL（）> 1'+ CHAR 10）+ 
 CHAR（9）+ CHAR（9）+'返回+ CHAR（10）+ 
 CHAR（10）+ 
 CHAR（9）+'INSERT INTO' （10）+ 
 CHAR（9）+ CHAR（9）+'['+ SS.name +']。['+ ST.name +'_Audit]（' + CHAR（10）+ 
 CHAR（9）+ CHAR（9）+ CHAR（9）+'[AuditIsDelete]'
从
 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作为[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为[AuditIsDelete]'+ 
 @ColumnNamesSection + CHAR ）+ 
 CHAR（9）+ CHAR（9）+'FROM'+ CHAR（10）+ 
 CHAR（9）+ CHAR（9）+ CHAR（9）+' 10）+ 
'声明@SoftDelete位，
 @Id int 
 
选择
 @SoftDelete =当i.DeletedDate不为null然后1 else 0结束时， 
 @Id = i.Id 
从
插入i; 
 
如果@SoftDelete = 1 
 begin 
 INSERT INTO 
 ['+ @SchemaName +']。['+ @TableName +'_Audit]（
 [AuditIsDelete] 
'+ @ColumnNamesSection +'
）
 SELECT 
 1 as [AuditIsDelete] 
'+ @ColumnNamesSection +'
 FROM 
 insert 
 
从'+ @SchemaName +'删除'+ @TableName +'其中Id = @Id 
 end;'+ CHAR（10）+ 
 
'End;'
 
 --6）打印和运行脚本
 BEGIN TRY 
开始交易; 
 
 EXEC（@CreateTableScript）; 
 
 EXEC（@CreateDeleteScript）; 
 
 EXEC（@CreateUpdateScript）; 
 
 --Test Try Catch：
 --SELECT 1/0 
 
 COMMIT TRANSACTION; 
 
 PRINT（'审核表成功创建'）
 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）+'创建审核表脚本：'）; 
 PRINT（@LineBreak）; 
 PRINT（@CreateTableScript）; 
 PRINT（@LineBreak）; 
 
 PRINT（CHAR（10）+'创建审计删除触发器脚本：'）; 
 PRINT（@LineBreak）; 
 PRINT（@CreateDeleteScript）; 
 PRINT（@LineBreak）; 
 
 PRINT（CHAR（10）+'创建审计更新触发器脚本：'）; 
 PRINT（@LineBreak）; 
 PRINT（@CreateUpdateScript）; 
 PRINT（@LineBreak）; 
 
 raiserror（@msg，18，1）; 
 END CATCH 
 END; 
  

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

I have a framework which implements Soft Deletes in the Database (Nullable DateTime called DeletedDate). I am using a Repository to handle the main entity requests like so:

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

This works great, but the issue I'm having is when you include navigational properties, and how to make sure only Active records are queried. The repository method in question starts like this:

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

So if the Repository is being used by an entity called Parent which has a navigational property called Children, the AllIncluding method would properly filter out the soft deleted Parent records, but the soft deleted Children records would still be included.

Looking at the query sent to the database, it seems all that needs to be done is add to the sql join clause " AND Children.DeletedDate IS NULL " and the query would return the correct results.

During my research, I found the this post which appears to be exactly what I need, however my implementation doesn't get the same results the poster had. Stepping through the code, nothing seems to happen to the Children part of the query.

Here is my current relevant code (Note: Using QueryInterceptor from nuget):

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

Parent Class:

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

Child Class:

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

Context Class:

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

Repository Class:

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 Class:

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 Class:

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 Class:

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 Interface:

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 Interface:

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

解决方案

I never was able to figure out the expression visitor, and had already spent enough time on it. So I ended up just handling this in a Table Trigger by deleting the record if the DeletedDate was not null.

The original purpose of the soft delete was to track who deleted the record in the application. I was setting the Mod User in the save changes context, but on a deletion this doesn't get updated, so there isn't an audit of who did the deletion.

I already had an "After Update" and "After Delete" trigger for each table I was auditing and an associated audit table for each table. The triggers basically insert the old record into the audit table anytime there is an update or delete. The Audit tables and triggers are created through a stored procedure:

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;

While the Triggers are not ideal, they accomplish the goals of auditing the user who deleted and I no longer need to worry about the soft deleted records.

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