使用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;
}
}
问题是您希望在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"列的设计模式。原因有很多。
- 该模式产生了一个内部平台,在数据库中有一个数据库
- 访问内部数据库(
select * from table where IsDeleted = 0
)所需的自定义API和(delete from table becomes update table set IsDeleted = 1
) - 表中的额外数据会降低性能
- 额外的数据对审计没有用处,如果你想进行审计,请正确进行
你遇到的痛点是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()
。
此模式允许您在任何时间点都可以获得表的完整历史视图。全面审计。:)