前言
資料庫併發，資料審計和軟刪除一直是資料持久化方面的經典問題。早些時候，這些工作需要手寫複雜的SQL或者透過儲存過程和觸發器實現。手寫複雜SQL對軟體可維護性構成了相當大的挑戰，隨著SQL字數的變多，用到的巢狀和複雜語法增加，可讀性和可維護性的難度是幾何級暴漲。因此如何在實現功能的同時控制這些SQL的複雜度是一個很有價值的問題。而且這個問題同時涉及應用軟體和資料庫兩個相對獨立的體系，平行共管也是產生混亂的一大因素。
EF Core作為 .NET平臺的高階ORM框架，可以託管和資料庫的互動，同時提供了大量擴充套件點方便自定義。以此為基點把對資料庫的操作託管後便可以解決平行共管所產生的混亂，利用LINQ則可以最大程度上降低軟體程式碼的維護難度。
由於專案需要，筆者先後開發併發布了通用的基於EF Core儲存的國際化服務和基於EF Core儲存的Serilog持久化服務，不過這兩個功能包並沒有深度利用EF Core，雖然主要是因為沒什麼必要。但是專案還需要提供常用的資料審計和軟刪除功能，因此對EF Core進行了一些更深入的研究。
起初有考慮過是否使用現成的ABP框架來處理這些功能，但是在其他專案的使用體驗來說並不算好，其中充斥著大量上下文依賴的功能，而且這些依賴資訊能輕易藏到和最終業務程式碼相距十萬八千里的地方（特別是程式碼還是別人寫的時候），然後在不經意間給你一個大驚喜。對於以程式碼正交性、非誤導性，純函式化為追求的一介碼農（看過我釋出的那兩個功能包的朋友應該有感覺，一個功能筆者也要根據用途劃分為不同的包，確保解決方案中的各個專案都能按需引用，不會殘留無用的程式碼），實在是喜歡不起來ABP這種全家桶。
鑑於專案規模不大，筆者決定針對這些需求做一個專用功能，目標是儘可能減少依賴，方便將來複用到其他專案，降低和其他功能功能衝突的風險。現在筆者將用一系列部落格做成果展示。由於這些功能沒有經過大範圍測試，不確定是否存在未知缺陷，因此暫不打包釋出。
新書宣傳
有關新書的更多介紹歡迎檢視《C#與.NET6 開發從入門到實踐》上市，作者親自來打廣告了！
正文
由於這些功能設計的程式碼量和知識點較多，為控制篇幅，本文介紹軟刪除功能。
引言
多年以前就聽說過軟刪除也進行過一些瞭解，也思考過如何才能優雅地實現，但都因為知識儲備不足和需求不緊急而擱置了，這次箭在弦上不得不發了，所幸這些年的積累也為解決這個問題鋪平了道路。
關聯式資料庫的一大功能就是確保資料完整性，畢竟關聯式資料庫一開始就是為金融等對資料有嚴格要求的場景而設計。但是這種功能在某些時候也會帶來一些麻煩，例如經典的部落格評論關係中，評論一定是要屬於某個部落格的，這種強依賴會導致如果刪除部落格，評論的存在意義會同時消失。為保障資料完整性，這些孤兒評論應該同時刪除或者至少把外來鍵設定為null以明確表示評論所對應的部落格已經不存在。但這種過於強硬的要求會導致沒有後悔藥可以吃，為了不把事做絕，軟刪除方案應運而生。透過額外的欄位標記資料已被刪除，然後在查詢時用條件排除此類記錄。
看上去一切都很美好，如果不出意外的話就該出意外了。查詢語句複雜度陡然上升、唯一約束錯亂、級聯刪除的恢復等讓人頭痛欲裂的問題接踵而至。為了研究這些問題的解決方案也是翻閱了大量資料，聊的相對深入的一些例如軟刪除之痛、在EF Core中使用攔截器實現自動軟刪除、深入理解 EF Core：使用查詢過濾器實現資料軟刪除 。但是這些資料都沒能同時解決這些問題，或者解決方式存在缺陷，當年擱置軟刪除問題的研究大抵也是出於這些原因。
解決方案
這次藉助EF Core的一些功能，總算是用一種比較優雅的方式基本解決這些痛點。
- 唯一約束錯亂：問題的根源在於資料庫中的Null值特殊的相等性判定。因此筆者的解決方案是首先禁止刪除標記為Null，然後用精確到微秒的時間型別基本解決不可重複刪除問題。
- 軟刪除的級聯和恢復錯亂：上文提到的文章使用刪除計數來識別記錄被刪除過幾次，但是這種方案對於計數為1的記錄依然存在這個1究竟是主動刪除導致的還是被級聯刪除導致的無法準確區分。其次，這種計數方式對事務性的要求較高，需要把主表和可能相關的子表一併鎖定避免計數錯亂，還可能把刪除子表資料的程式碼蔓延到業務程式碼中產生大量程式碼噪音。如果使用觸發器還需要解決潛在的遞迴巢狀溢位和架構遷移維護問題，無論如何都不是個優雅的方案。再次，如果需要直接運算元據庫進行資料維護，一旦腦子不清醒忘記更新部分子表的計數器，資料完整性會遭到致命破壞而且很可能無法恢復，因為這種修改很難溯源，無法知道某個計數器更新是何原因也就無法正確回滾。
筆者的解決方案則是使用獨立的刪除標記和傳遞刪除標記來實現軟刪除的自動級聯，分離後將不再需要計數器，因此事務問題和架構維護問題自然消失，主動刪除和傳遞刪除的恢復問題也一併消失。現在問題也變成了傳遞刪除標記的值要如何產生。對此，筆者的方法是使用檢視自動計算，雖然這樣可能導致一些查詢效能損失，但是這是不可避免的代價，魚與熊掌不可兼得。隨之而來的下一個問題就是檢視的查詢SQL如何維護，幸好EF Core本身就包含遷移功能來管理資料庫架構的變更，那隻要把檢視定義SQL的維護放到遷移中即可。最後問題就變成如何實現檢視定義SQL的自動化維護，否則整個方案都會很難用，而這就是本文的重要內容之一。 - 查詢複雜度陡然上升和已刪除資料被意外包含在結果中：查詢複雜度的飛速上升主要是因為需要大量增加對傳遞刪除的查詢，隨著上一個問題的解決，這個問題自然消失，因為這些複雜的查詢都被封裝在檢視定義中了。EF Core新版的表檢視多重對映和全域性查詢過濾器功能更是能在業務層徹底遮蔽軟刪除的查詢問題。而且就算是需要手寫SQL查詢的地方也可以極大減輕心智負擔，因為檢視存在於資料庫中，就算脫離應用程式依然可以使用。
程式碼實現
基礎介面
/// <summary>
/// 邏輯刪除介面
/// </summary>
public interface ILogicallyDeletable
{
/// <summary>
/// 邏輯刪除標記
/// </summary>
/// <remarks>推薦存活的記錄使用<see cref="DateTimeOffset.MinValue"/>標記</remarks>
DateTimeOffset DeletedAt { get; set; }
}
/// <summary>
/// 依賴項邏輯刪除介面
/// </summary>
public interface IDependencyLogicallyDeletable
{
/// <summary>
/// 依賴項邏輯刪除標記
/// </summary>
/// <remarks>
/// <para>用於模擬邏輯刪除的級聯刪除或置Null。</para>
/// <para>對於必選關係，應該引用主記錄的邏輯刪除和依賴項邏輯刪除標記值中較大的一方，確保級聯刪除模擬可以自動傳播。</para>
/// <para>推薦使用EF Core的查詢檢視對映把屬性值對映到查詢檢視並配置全域性查詢過濾器簡化級聯邏輯刪除的查詢。</para>
/// <para>EF Core實現中表對映到Null值計算列，由查詢檢視計算實際值。</para>
/// </remarks>
DateTimeOffset? DependencyDeletedAt { get; set; }
}
傳遞刪除標記由檢視計算，但是EF Core無法配置某個屬性只存在於檢視中，因此只能使用計算列在表中生成一個同名列以符合EF Core的對映要求。
模型配置擴充套件
/// <summary>
/// 實體模型配置擴充套件
/// </summary>
public static class EntityModelBuilderExtensions
{
internal const string _logicallyDeleteableAnnotationName = "CoreDX.Entity.Property:LogicallyDeleteable";
internal const string _dependencyLogicallyDeleteableAnnotationName = "CoreDX.Entity.Property:DependencyLogicallyDeleteable";
internal const string _queryViewAnnotationName = "CoreDX.Relational:QueryView";
internal const string _queryViewNamePrefixes = "QueryView_";
internal const string _treeQueryViewNamePrefixes = $"{_queryViewNamePrefixes}Tree_";
/// <summary>
/// 使用<see langword="default" />（<see cref="DateTimeOffset.MinValue"/>）
/// </summary>
private static readonly DateTimeOffset _aliveEntityTime = default;
/// <summary>
/// 配置可邏輯刪除實體的查詢過濾器讓EF Core自動新增查詢條件過濾已被邏輯刪除的記錄。<br/>存活的記錄使用<see cref="DateTimeOffset.MinValue"/>標記。
/// </summary>
/// <typeparam name="TEntity">實體型別</typeparam>
/// <param name="builder">實體型別構造器</param>
/// <returns>實體型別構造器</returns>
public static EntityTypeBuilder<TEntity> ConfigureQueryFilterForILogicallyDelete<TEntity>(this EntityTypeBuilder<TEntity> builder)
where TEntity : class, ILogicallyDeletable
{
ArgumentNullException.ThrowIfNull(builder);
// 配置資料庫預設值和EF Core哨兵值
builder.Property(e => e.DeletedAt)
.HasDefaultValue(_aliveEntityTime)
.HasSentinel(_aliveEntityTime)
.HasAnnotation(_logicallyDeleteableAnnotationName, true);
ConfigQueryViewAnnotationForLogicallyDeletable(builder);
return builder.HasQueryFilter(e => e.DeletedAt == EF.Constant(_aliveEntityTime));
}
/// <summary>
/// 配置依賴項邏輯刪除實體的查詢過濾器讓EF Core自動新增查詢條件過濾已被邏輯刪除的記錄。
/// </summary>
/// <typeparam name="TEntity">實體型別</typeparam>
/// <param name="builder">實體型別構造器</param>
/// <param name="nullValueSql">依賴項邏輯刪除在表中的計算列Null值生成Sql</param>
/// <returns>實體型別構造器</returns>
public static EntityTypeBuilder<TEntity> ConfigureQueryFilterForIDependencyLogicallyDelete<TEntity>(
this EntityTypeBuilder<TEntity> builder,
IDependencyLogicallyDeletableNullValueSql nullValueSql)
where TEntity : class, IDependencyLogicallyDeletable
{
ArgumentNullException.ThrowIfNull(builder);
ArgumentNullException.ThrowIfNull(nullValueSql);
// 配置表的依賴項邏輯刪除標記列為值永遠為NULL的計算列
builder.Property(e => e.DependencyDeletedAt)
.HasComputedColumnSql(nullValueSql.DependencyDeleteAtNullComputedValueSql)
.HasAnnotation(_dependencyLogicallyDeleteableAnnotationName, true);
ConfigQueryViewAnnotationForDependencyLogicallyDeletable(builder);
return builder.HasQueryFilter(e => e.DependencyDeletedAt == null || e.DependencyDeletedAt == EF.Constant(_aliveEntityTime));
}
/// <summary>
/// 配置可邏輯刪除和依賴項邏輯刪除實體的查詢過濾器讓EF Core自動新增查詢條件過濾已被邏輯刪除的記錄。
/// </summary>
/// <typeparam name="TEntity">實體型別</typeparam>
/// <param name="builder">實體型別構造器</param>
/// <param name="nullValueSql">依賴項邏輯刪除在表中的計算列Null值生成Sql</param>
/// <returns>實體型別構造器</returns>
public static EntityTypeBuilder<TEntity> ConfigureQueryFilterForILogicallyAndIDependencyLogicallyDelete<TEntity>(
this EntityTypeBuilder<TEntity> builder,
IDependencyLogicallyDeletableNullValueSql nullValueSql)
where TEntity : class, ILogicallyDeletable, IDependencyLogicallyDeletable
{
ArgumentNullException.ThrowIfNull(builder);
ArgumentNullException.ThrowIfNull(nullValueSql);
// 配置資料庫預設值和EF Core哨兵值
builder.Property(e => e.DeletedAt)
.HasDefaultValue(_aliveEntityTime)
.HasSentinel(_aliveEntityTime)
.HasAnnotation(_logicallyDeleteableAnnotationName, true);
// 配置表的依賴項邏輯刪除標記列為值永遠為NULL的計算列
builder.Property(e => e.DependencyDeletedAt)
.HasComputedColumnSql(nullValueSql.DependencyDeleteAtNullComputedValueSql)
.HasAnnotation(_dependencyLogicallyDeleteableAnnotationName, true);
ConfigQueryViewAnnotationForLogicallyDeletable(builder);
ConfigQueryViewAnnotationForDependencyLogicallyDeletable(builder);
return builder.HasQueryFilter(e => e.DeletedAt == EF.Constant(_aliveEntityTime) && (e.DependencyDeletedAt == null || e.DependencyDeletedAt == EF.Constant(_aliveEntityTime)));
}
/// <summary>
/// 批次配置可邏輯刪除和依賴項邏輯刪除實體的查詢過濾器讓EF Core自動新增查詢條件過濾已被邏輯刪除或傳遞依賴刪除的記錄
/// </summary>
/// <param name="modelBuilder">模型構造器</param>
/// <param name="nullValueSql">依賴項邏輯刪除在表中的計算列Null值生成Sql</param>
/// <returns>模型構造器</returns>
public static ModelBuilder ConfigureQueryFilterForILogicallyDeleteOrIDependencyLogicallyDeletable(
this ModelBuilder modelBuilder,
IDependencyLogicallyDeletableNullValueSql nullValueSql)
{
ArgumentNullException.ThrowIfNull(modelBuilder);
ArgumentNullException.ThrowIfNull(nullValueSql);
foreach (var entity
in modelBuilder.Model.GetEntityTypes()
.Where(e => e.ClrType.IsDerivedFrom<ILogicallyDeletable>() || e.ClrType.IsDerivedFrom<IDependencyLogicallyDeletable>()))
{
var entityTypeBuilderMethod = GetEntityTypeBuilderMethod(entity);
var isILogicallyDeletable = entity.ClrType.IsDerivedFrom<ILogicallyDeletable>();
var isIDependencyLogicallyDeletable = entity.ClrType.IsDerivedFrom<IDependencyLogicallyDeletable>();
var logicallyDeleteQueryFilterMethod = (isILogicallyDeletable, isIDependencyLogicallyDeletable) switch
{
(true, false) => GetEntityTypeConfigurationMethod(
nameof(ConfigureQueryFilterForILogicallyDelete),
1,
entity.ClrType),
(false, true) => GetEntityTypeConfigurationMethod(
nameof(ConfigureQueryFilterForIDependencyLogicallyDelete),
2,
entity.ClrType),
(true, true) => GetEntityTypeConfigurationMethod(
nameof(ConfigureQueryFilterForILogicallyAndIDependencyLogicallyDelete),
2,
entity.ClrType),
// 不可能進入此分支
(false, false) => throw new InvalidOperationException()
};
if (isIDependencyLogicallyDeletable) logicallyDeleteQueryFilterMethod.Invoke(null, [entityTypeBuilderMethod.Invoke(modelBuilder, null), nullValueSql]);
else logicallyDeleteQueryFilterMethod.Invoke(null, [entityTypeBuilderMethod.Invoke(modelBuilder, null)]);
}
return modelBuilder;
}
/// <summary>
/// 配置實體查詢檢視。
/// </summary>
/// <typeparam name="TEntity">實體型別</typeparam>
/// <param name="builder">實體型別構造器</param>
/// <returns>實體型別構造器</returns>
/// <remarks>
/// <para>如果實體型別實現<see cref="IDependencyLogicallyDeletable"/>，但不實現<see cref="ITreeEntity{TKey, TEntity}"/>，生成並對映查詢到以<see cref="_queryViewNamePrefixes"/>開頭的檢視。</para>
/// <para>如果實體型別實現<see cref="ITreeEntity{TKey, TEntity}"/>，但不實現<see cref="IDependencyLogicallyDeletable"/>，生成並對映查詢到以<see cref="_treeQueryViewNamePrefixes"/>開頭的檢視。</para>
/// <para>如果實體型別同時實現<see cref="IDependencyLogicallyDeletable"/>和<see cref="ITreeEntity{TKey, TEntity}"/>，同時生成以<see cref="_queryViewNamePrefixes"/>和<see cref="_treeQueryViewNamePrefixes"/>開頭的檢視。<br/>
/// 實體查詢對映到以<see cref="_treeQueryViewNamePrefixes"/>開頭的檢視，並且<see cref="_treeQueryViewNamePrefixes"/>開頭的檢視以<see cref="_queryViewNamePrefixes"/>開頭的檢視為資料來源。</para>
/// </remarks>
public static EntityTypeBuilder<TEntity> ConfigEntityQueryView<TEntity>(this EntityTypeBuilder<TEntity> builder)
where TEntity : class
{
ArgumentNullException.ThrowIfNull(builder);
if (builder.Metadata.FindAnnotation(_queryViewAnnotationName)?.Value is List<Type> annotationValue)
{
var isDependencyLogicallyDeletableEntity = annotationValue.Any(static x => x == typeof(IDependencyLogicallyDeletable));
var isTreeEntity = annotationValue.Any(static x => x == typeof(ITreeEntity<,>));
var tableName = builder.Metadata.GetTableName();
builder.ToTable(tableName);
if(isTreeEntity || isDependencyLogicallyDeletableEntity)
{
builder.ToView($"{(isTreeEntity ? _treeQueryViewNamePrefixes : _queryViewNamePrefixes)}{tableName}");
}
}
return builder;
}
/// <summary>
/// 批次配置實體查詢檢視
/// </summary>
/// <param name="modelBuilder">模型構造器</param>
/// <returns>模型構造器</returns>
/// <remarks>配置規則同<see cref="ConfigEntityQueryView{TEntity}"/></remarks>
public static ModelBuilder ConfigEntityQueryView(this ModelBuilder modelBuilder)
{
ArgumentNullException.ThrowIfNull(modelBuilder);
foreach (var entity
in modelBuilder.Model.GetEntityTypes()
.Where(static e => e.FindAnnotation(_queryViewAnnotationName) is not null))
{
var entityTypeBuilderMethod = GetEntityTypeBuilderMethod(entity);
var entityQueryViewMethod = GetEntityTypeConfigurationMethod(
nameof(ConfigEntityQueryView),
1,
entity.ClrType);
entityQueryViewMethod.Invoke(null, [entityTypeBuilderMethod.Invoke(modelBuilder, null)]);
}
return modelBuilder;
}
/// <summary>
/// 配置邏輯刪除實體的查詢檢視註解
/// </summary>
/// <typeparam name="TEntity">實體型別</typeparam>
/// <param name="builder">實體型別構造器</param>
private static void ConfigQueryViewAnnotationForLogicallyDeletable<TEntity>(EntityTypeBuilder<TEntity> builder)
where TEntity : class, ILogicallyDeletable
{
var annotationValue = builder.Metadata.FindAnnotation(_queryViewAnnotationName)?.Value;
var typedAnnotationValue = annotationValue as List<Type>;
if (annotationValue is not null && typedAnnotationValue is null)
{
throw new InvalidOperationException($@"模型註解名稱""{_queryViewAnnotationName}""已被佔用，請把佔用此名稱的註解修改為其他名稱。");
}
else if (typedAnnotationValue is null)
{
builder.HasAnnotation(_queryViewAnnotationName, new List<Type>() { typeof(ILogicallyDeletable) });
}
else if (typedAnnotationValue.Find(static x => x is ILogicallyDeletable) is null)
{
typedAnnotationValue.Add(typeof(ILogicallyDeletable));
}
}
/// <summary>
/// 配置依賴項邏輯刪除實體的查詢檢視註解
/// </summary>
/// <typeparam name="TEntity">實體型別</typeparam>
/// <param name="builder">實體型別構造器</param>
private static void ConfigQueryViewAnnotationForDependencyLogicallyDeletable<TEntity>(EntityTypeBuilder<TEntity> builder)
where TEntity : class, IDependencyLogicallyDeletable
{
var annotationValue = builder.Metadata.FindAnnotation(_queryViewAnnotationName)?.Value;
var typedAnnotationValue = annotationValue as List<Type>;
if (annotationValue is not null && typedAnnotationValue is null)
{
throw new InvalidOperationException($@"模型註解名稱""{_queryViewAnnotationName}""已被佔用，請把佔用此名稱的註解修改為其他名稱。");
}
else if (typedAnnotationValue is null)
{
builder.HasAnnotation(_queryViewAnnotationName, new List<Type>() { typeof(IDependencyLogicallyDeletable) });
}
else if (typedAnnotationValue.Find(static x => x is IDependencyLogicallyDeletable) is null)
{
typedAnnotationValue.Add(typeof(IDependencyLogicallyDeletable));
}
}
}
SQL模版
相關介面
/// <summary>
/// 依賴項邏輯刪除實體的檢視列在表中的Null值計算列對映
/// </summary>
public interface IDependencyLogicallyDeletableNullValueSql
{
string DependencyDeleteAtNullComputedValueSql { get; }
}
public interface ITableOrColumnNameFormattable
{
/// <summary>
/// 格式化表或列名
/// <para>例如為SqlServer列名包裹方括號、MySql包裹反引號</para>
/// </summary>
/// <param name="name">表或列名</param>
/// <returns>格式化的表或列名</returns>
[return: NotNullIfNotNull(nameof(name))]
string? FormatTableOrColumnName(string? name);
}
/// <summary>
/// 依賴項邏輯刪除實體的檢視SQL模板
/// </summary>
public interface IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate : ITableOrColumnNameFormattable
{
/// <summary>
/// 建立檢視的模板
/// </summary>
string CreateSqlTemplate { get; }
/// <summary>
/// 刪除檢視的模板
/// </summary>
string DropSqlTemplate { get; }
/// <summary>
/// 連線資料來源模版
/// </summary>
/// <remarks>LEFT JOIN {principalDataSource} ON {tableName}.{foreignKey} = {principalDataSource}.{principalKey}</remarks>
string JoinTargetTemplate { get; }
string PrincipalLogicallyDeleteColumnTemplate { get; }
}
介面實現（以SqlServer為例）
public class DefaultSqlServerDependencyLogicallyDeletableNullValueSql : IDependencyLogicallyDeletableNullValueSql
{
public static DefaultSqlServerDependencyLogicallyDeletableNullValueSql Instance => new();
private const string _dependencyDeleteAtNullComputedValueSql = "CAST(NULL AS datetimeoffset)";
public string DependencyDeleteAtNullComputedValueSql => _dependencyDeleteAtNullComputedValueSql;
private DefaultSqlServerDependencyLogicallyDeletableNullValueSql() { }
}
public class DefaultSqlServerDependencyLogicallyDeletableEntityViewSqlTemplate : IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate
{
public static DefaultSqlServerDependencyLogicallyDeletableEntityViewSqlTemplate Instance => new();
private const string _viewNameTemplate = $$"""{{EntityModelBuilderExtensions._queryViewNamePrefixes}}{tableName}""";
// SqlServer 2022起才支援GREATEST函式
private const string _createSqlTemplate =
$$"""
--建立或重建依賴項邏輯刪除實體查詢檢視
{{_dropSqlTemplate}}
CREATE VIEW {{_viewNameTemplate}}
AS
SELECT {columns},
(SELECT MAX([DeleteTimeTable].[DeletedAt])
FROM (VALUES {principalLogicallyDeleteColumns}) AS DeleteTimeTable([DeletedAt])) AS {dependencyDeletedAtColumn}
FROM [{tableName}]
{joinTargets};
GO
""";
private const string _principalLogicallyDeleteColumnTemplate = "({principalLogicallyDeleteColumn})";
private const string _joinTargetTemplate =
$$"""
LEFT JOIN {principalDataSource}
ON {joinCondition}
""";
private const string _dropSqlTemplate =
$"""
--刪除可能存在的過時依賴項邏輯刪除實體查詢檢視
IF EXISTS(SELECT * FROM [sysobjects] WHERE [id] = OBJECT_ID(N'{_viewNameTemplate}') AND objectproperty(id, N'IsView') = 1)
BEGIN
DROP VIEW [{_viewNameTemplate}]
END
GO
""";
public string CreateSqlTemplate => _createSqlTemplate;
public string DropSqlTemplate => _dropSqlTemplate;
public string PrincipalLogicallyDeleteColumnTemplate => _principalLogicallyDeleteColumnTemplate;
public string JoinTargetTemplate => _joinTargetTemplate;
public string? FormatTableOrColumnName(string? name)
{
if (name is null) return null;
return $"[{name}]";
}
private DefaultSqlServerDependencyLogicallyDeletableEntityViewSqlTemplate() { }
}
SqlServer 2022才支援取最大值函式，因此這裡使用相容性較高的表值生成函式配合MAX聚合實現取最大值。
資料庫遷移擴充套件
/// <summary>
/// 依賴項邏輯刪除實體檢視遷移擴充套件
/// </summary>
public static class DependencyLogicallyDeletableEntityMigrationBuilderExtensions
{
/// <summary>
/// 自動掃描遷移模型並配置依賴項邏輯刪除實體查詢檢視
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="thisVersion">當前版本的遷移</param>
/// <param name="previousVersion">上一個版本的遷移</param>
/// <param name="isUp">是否為升級遷移</param>
/// <param name="sqlTemplate">Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder ApplyDependencyLogicallyDeletableEntityQueryView(
this MigrationBuilder migrationBuilder,
Migration thisVersion,
Migration? previousVersion,
bool isUp,
IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
{
ArgumentNullException.ThrowIfNull(migrationBuilder);
ArgumentNullException.ThrowIfNull(thisVersion);
ArgumentNullException.ThrowIfNull(sqlTemplate);
var thisVersionEntityTypes = thisVersion.TargetModel.GetEntityTypes()
.Where(static et =>
(et.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
?.Any(x => x == typeof(IDependencyLogicallyDeletable)) is true
);
var previousVersionEntityTypes = previousVersion?.TargetModel.GetEntityTypes()
.Where(static et =>
(et.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
?.Any(x => x == typeof(IDependencyLogicallyDeletable)) is true
);
var pendingViewOperations = new List<(IEntityType? entity, string? tableName, bool isCreate)>();
var tempViewOperationsDict = new Dictionary<string, List<(IEntityType? entity, string? tableName, bool isCreate)>>();
// 表操作
foreach (var tableOperation in
migrationBuilder.Operations.Where(static op =>
{
var opType = op.GetType();
return opType.IsDerivedFrom<TableOperation>() || opType.IsDerivedFrom<DropTableOperation>();
}))
{
if (tableOperation is CreateTableOperation createTable)
{
// 升級建立表，建立檢視
if (isUp && thisVersionEntityTypes.Any(et => et.GetTableName() == createTable.Name))
{
var entity = thisVersionEntityTypes.Single(en => en.GetTableName() == createTable.Name);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
// 回滾建立表，說明必然存在上一版遷移，以上一個版本的模型建立檢視
if (!isUp)
{
EnsureMigrationOfPreviousVersion(previousVersion);
if (previousVersionEntityTypes?.Any(et => et.GetTableName() == createTable.Name) is true)
{
var entity = previousVersionEntityTypes.Single(en => en.GetTableName() == createTable.Name);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
}
}
else if (tableOperation is AlterTableOperation alterTable)
{
// 升級修改表（僅當修改表名時），重建檢視
// 因為修改表操作可能是修改表註釋
if (isUp)
{
// 如果上一版本中這個實體是依賴項邏輯刪除實體，刪除舊檢視
if (previousVersionEntityTypes?.Any(et => et.GetTableName() == alterTable.OldTable.Name) is true)
{
// 由於升級修改表名需要同時完成刪除基於舊錶名的檢視和建立基於新表名的檢視兩個操作
// 刪除舊檢視的操作直接新增到掛起操作列表，修改表名的操作也不會在遷移中重複出現，沒有重複新增相同操作的問題
pendingViewOperations.Add((null, alterTable.OldTable.Name, false));
}
if (thisVersionEntityTypes.Any(et => et.GetTableName() == alterTable.Name))
{
var entity = thisVersionEntityTypes.Single(en => en.GetTableName() == alterTable.Name);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
}
// 回滾修改表（僅當修改表名時），說明必然存在上一版遷移，以上一個版本的模型重建檢視
else
{
// 如果當前版本中這個實體也是依賴項邏輯刪除實體，刪除舊檢視
if (thisVersionEntityTypes.Any(et => et.GetTableName() == alterTable.OldTable.Name))
{
// 由於回滾修改表名需要同時完成刪除基於新表名的檢視和建立基於舊錶名的檢視兩個操作
// 刪除舊檢視的操作直接新增到掛起操作列表，修改表名的操作也不會在遷移中重複出現，沒有重複新增相同操作的問題
pendingViewOperations.Add((null, alterTable.OldTable.Name, false));
}
EnsureMigrationOfPreviousVersion(previousVersion);
if (previousVersionEntityTypes!.Any(et => et.GetTableName() == alterTable.Name) && alterTable.OldTable.Name != alterTable.Name)
{
var entity = previousVersionEntityTypes!.Single(en => en.GetTableName() == alterTable.Name);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
}
}
// 遷移操作需要刪除表，則代表同樣需要刪除檢視
else if (tableOperation is DropTableOperation dropTable)
{
if (isUp)
{
EnsureMigrationOfPreviousVersion(previousVersion);
if (previousVersionEntityTypes!.Any(en => en.GetTableName() == dropTable.Name))
{
AddTableDropTableViewToTempDict(tempViewOperationsDict, dropTable.Name);
}
}
else if (thisVersionEntityTypes.Any(en => en.GetTableName() == dropTable.Name))
{
AddTableDropTableViewToTempDict(tempViewOperationsDict, dropTable.Name);
}
}
}
// 列操作，每個表可能操作多個列，需要聚合處理
foreach (var tableColumnOperationsGrouping in
migrationBuilder.Operations
.Where(static op =>
{
var opType = op.GetType();
return opType.IsDerivedFrom<ColumnOperation>() || opType.IsDerivedFrom<DropColumnOperation>();
})
.GroupBy(static op => (op as ColumnOperation)?.Table ?? (op as DropColumnOperation)!.Table))
{
foreach (var columnOperation in tableColumnOperationsGrouping)
{
if (columnOperation is AddColumnOperation addColumn)
{
// 升級新增列，如果是依賴項邏輯刪除，重建檢視
if (isUp && thisVersionEntityTypes?.Any(et => et.GetTableName() == addColumn.Table) is true)
{
var entity = thisVersionEntityTypes.Single(en => en.GetTableName() == addColumn.Table);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
// 回滾新增列，說明必然存在上一版遷移，如果上一版是依賴項邏輯刪除實體，重建檢視
if (!isUp)
{
EnsureMigrationOfPreviousVersion(previousVersion);
if (previousVersionEntityTypes?.Any(et => et.GetTableName() == addColumn.Table) is true)
{
var entity = previousVersionEntityTypes.Single(en => en.GetTableName() == addColumn.Table);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
}
}
else if (columnOperation is AlterColumnOperation alterColumn)
{
// 升級修改列，重建檢視
// 因為修改列操作可能是修改列註釋
if (isUp)
{
if (thisVersionEntityTypes!.Any(et => et.GetTableName() == alterColumn.Table))
{
var entity = thisVersionEntityTypes!.Single(en => en.GetTableName() == alterColumn.Table);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
else if (previousVersionEntityTypes?.Any(et => et.GetTableName() == alterColumn.Table) is true)
{
AddTableDropTableViewToTempDict(tempViewOperationsDict, alterColumn.Table);
}
}
// 回滾修改列（僅當修改列名時），說明必然存在上一版遷移，如果上一版是依賴項邏輯刪除實體，重建檢視
// 因為修改列操作可能是修改列註釋
else
{
EnsureMigrationOfPreviousVersion(previousVersion);
if (previousVersionEntityTypes?.Any(et => et.GetTableName() == alterColumn.Table) is true)
{
var entity = previousVersionEntityTypes.Single(en => en.GetTableName() == alterColumn.Table);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
else if (thisVersionEntityTypes!.Any(et => et.GetTableName() == alterColumn.Table))
{
AddTableDropTableViewToTempDict(tempViewOperationsDict, alterColumn.Table);
}
}
}
else if (columnOperation is DropColumnOperation dropColumn)
{
// 升級刪除列
if (isUp)
{
// 當前版本仍然是依賴項邏輯刪除實體，說明被刪除的列和依賴項邏輯刪除無關，重建檢視
if (thisVersionEntityTypes!.Any(et => et.GetTableName() == dropColumn.Table))
{
var entity = thisVersionEntityTypes!.Single(en => en.GetTableName() == dropColumn.Table);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
// 被刪除的列是依賴項邏輯刪除（上一版本的實體是依賴項邏輯刪除，但當前版本不是），刪除檢視
else if (previousVersionEntityTypes?.Any(et => et.GetTableName() == dropColumn.Table) is true)
{
AddTableDropTableViewToTempDict(tempViewOperationsDict, dropColumn.Table);
}
}
// 回滾刪除列
if (!isUp)
{
EnsureMigrationOfPreviousVersion(previousVersion);
// 上一版本是依賴項邏輯刪除實體，說明被刪除的列和依賴項邏輯刪除無關，重建檢視
if (previousVersionEntityTypes?.Any(et => et.GetTableName() == dropColumn.Table) is true)
{
var entity = previousVersionEntityTypes.Single(en => en.GetTableName() == dropColumn.Table);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
// 被刪除的列是依賴項邏輯刪除（上一版本的實體不是依賴項邏輯刪除，但當前版本是），刪除檢視
else if (thisVersionEntityTypes?.Any(et => et.GetTableName() == dropColumn.Table) is true)
{
AddTableDropTableViewToTempDict(tempViewOperationsDict, dropColumn.Table);
}
}
}
}
}
// 聚合所有操作，然後選擇其中合理的一個作為最終操作
foreach (var entityViewOperations in tempViewOperationsDict)
{
// 理論上來說如果上面的程式碼沒有問題，針對同一張表的操作應該不會出現矛盾
Debug.Assert(entityViewOperations.Value.All(x => x.isCreate == entityViewOperations.Value.First().isCreate));
pendingViewOperations.Add(entityViewOperations.Value.First());
}
// 檢查是依賴項邏輯刪除但沒有出現在操作列表中的實體
// 這種實體可能由於依賴的主實體存在遷移操作，導致現有檢視過時，需要追加到重建檢視的操作列表中
// 這種情況只會出現在所依賴的主實體從同時是邏輯刪除和依賴項邏輯刪除實體變成只是其中一種或者從原來是其中一種變成另外一種
// 或者邏輯刪除和依賴項邏輯刪除列被改名
// 主實體的其他列改動與當前實體無關，當前實體的檢視對主實體的依賴僅限於主實體的邏輯刪除（來自表或檢視）和依賴項邏輯刪除（一定是檢視）（如果主實體也依賴於它的主實體）
// 主實體從都不是變成至少是其中一種需要在從實體上新增依賴項邏輯刪除介面以實現功能，會導致遷移至少會增加一列，因此從實體自身必然會出現在新增列操作中
// 主實體從至少是其中一種變成都不是，模型構建階段從實體上的依賴項邏輯刪除介面就會丟擲異常提示取消對介面的實現，會導致遷移至少會刪除一列，因此從實體自身必然會出現在刪除列操作中
// 收集所有新增、刪除和改名列操作並按照表分組備用
var tableColumnOperationsGroupingforTransitiveDependencyCheck = migrationBuilder.Operations
.Where(static op =>
{
var opType = op.GetType();
return opType.IsDerivedFrom<AddColumnOperation>()
// 如果是修改列名，也可能需要重建檢視
// 在模型上單獨配置介面屬性的列名對映時需要重建檢視
// 其他無關列的改名無關緊要，但是因為在這裡沒有對應的實體屬性資訊，改名後無法確定哪個是邏輯刪除屬性對應的列名，只能全部收集後在之後匹配
|| opType.IsDerivedFrom<AlterColumnOperation>()
|| opType.IsDerivedFrom<DropColumnOperation>();
});
// 收集存在檢視操作的實體
var pendingViewOperationEntities = pendingViewOperations
.Select(pop =>
pop.entity ?? (isUp ? thisVersion.TargetModel : previousVersion?.TargetModel)
?.GetEntityTypes()
.SingleOrDefault(et => et.GetTableName()! == (pop.tableName ?? pop.entity!.GetTableName())))
.Where(static pop => pop is not null)
.ToList();
// 收集自身不在操作列表中，但依賴的主實體在操作列表中的實體
// 升級遷移收集當前版本的遷移實體，回滾遷移收集上一版本的遷移實體
var principalInPendingViewOperationEntities = (isUp ? thisVersionEntityTypes : previousVersionEntityTypes)
?.Where(et => !pendingViewOperationEntities.Contains(et))
.Where(et =>
et.GetForeignKeys()
.Select(static fk => fk.PrincipalEntityType)
.Any(pet => pendingViewOperationEntities.Contains(pet))
)
.ToList() ?? [];
// 把這些實體加入檢視重建列表
pendingViewOperations.AddRange(principalInPendingViewOperationEntities?.Select(et => ((IEntityType?)et, (string?)null, true)) ?? []);
foreach (var (entity, tableName, _) in pendingViewOperations.Where(op => !op.isCreate))
{
if (entity is not null) migrationBuilder.DropDependencyLogicallyDeletableEntityQueryView(entity, sqlTemplate);
else if (tableName is not null) migrationBuilder.DropDependencyLogicallyDeletableEntityQueryView(tableName, sqlTemplate);
else throw new InvalidOperationException("遷移實體型別和遷移表名不能同時為 null。");
}
migrationBuilder.CreateDependencyLogicallyDeletableEntityQueryViewsByDataSourceDependency(
pendingViewOperations.Where(op => op.isCreate).Select(op => op.entity!),
sqlTemplate);
return migrationBuilder;
/// <summary>
/// 確保提供了上一版本的遷移
/// </summary>
static void EnsureMigrationOfPreviousVersion(Migration? previousVersion)
{
if (previousVersion is null) throw new InvalidOperationException($"回滾操作指出存在更早版本的遷移，但未提供上一版本的遷移。");
}
/// <summary>
/// 向按表分組的臨時操作存放字典新增建立實體檢視命令
/// </summary>
static void AddEntityCreateEntityViewToTempDict(Dictionary<string, List<(IEntityType? entity, string? tableName, bool isCreate)>> tempViewOperationsDict, IEntityType entity)
{
if (!tempViewOperationsDict.TryGetValue(entity.GetTableName()!, out var result))
{
result ??= [];
tempViewOperationsDict.Add(entity.GetTableName()!, result);
}
result.Add((entity, null, true));
}
/// <summary>
/// 向按表分組的臨時操作存放字典新增刪除表檢視命令
/// </summary>
static void AddTableDropTableViewToTempDict(Dictionary<string, List<(IEntityType? entity, string? tableName, bool isCreate)>> tempViewOperationsDict, string tableName)
{
if (!tempViewOperationsDict.TryGetValue(tableName, out var result))
{
result ??= [];
tempViewOperationsDict.Add(tableName, result);
}
result.Add((null, tableName, false));
}
}
/// <summary>
/// 建立依賴項邏輯刪除實體查詢檢視
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="entityType">實體型別</param>
/// <param name="sqlTemplate">Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder CreateDependencyLogicallyDeletableEntityQueryView(
this MigrationBuilder migrationBuilder,
IEntityType entityType,
IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
{
ArgumentNullException.ThrowIfNull(migrationBuilder);
ArgumentNullException.ThrowIfNull(entityType);
ArgumentNullException.ThrowIfNull(sqlTemplate);
var (sql, _, _) = CreateDependencyLogicallyDeletableEntityQueryViewSql(entityType, sqlTemplate);
migrationBuilder.Sql(sql);
return migrationBuilder;
}
/// <summary>
/// 建立依賴項邏輯刪除檢視並對檢視建立進行排序，確保被依賴的主實體檢視優先建立
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="entityTypes">實體型別集合</param>
/// <param name="sqlTemplate">Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder CreateDependencyLogicallyDeletableEntityQueryViewsByDataSourceDependency(
this MigrationBuilder migrationBuilder,
IEnumerable<IEntityType> entityTypes,
IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
{
var viewSqls = entityTypes
.Select(e => CreateDependencyLogicallyDeletableEntityQueryViewSql(e, sqlTemplate))
.ToImmutableList();
var viewNames = viewSqls
.Select(vs => vs.viewName)
.ToImmutableList();
var rootViewSqls = viewSqls.Where(x =>
x.principalDataSourceNames
.All(ds => !ds.isViewDependency || !viewNames.Contains(ds.principalDataSourceName)));
var viewSqlTrees = rootViewSqls.Select(rv =>
rv.AsHierarchical(v =>
viewSqls.Where(vs =>
vs.principalDataSourceNames
.Select(static dsn => dsn.principalDataSourceName)
.Contains(v.viewName))));
var orderedViewSqls = viewSqlTrees
.SelectMany(tr => tr.AsEnumerable())
.GroupBy(h => h.Current.viewName)
.Select(hg => hg.OrderByDescending(h => h.Level).First())
.OrderBy(h => h.Level)
.Select(h => h.Current);
foreach (var (sql, _, _) in orderedViewSqls) migrationBuilder.Sql(sql);
return migrationBuilder;
}
/// <summary>
/// 刪除依賴項邏輯刪除實體查詢檢視
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="entityType">實體型別</param>
/// <param name="sqlTemplate">Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder DropDependencyLogicallyDeletableEntityQueryView(
this MigrationBuilder migrationBuilder,
IEntityType entityType,
IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
{
ArgumentNullException.ThrowIfNull(migrationBuilder);
ArgumentNullException.ThrowIfNull(entityType);
ArgumentNullException.ThrowIfNull(sqlTemplate);
return migrationBuilder.DropDependencyLogicallyDeletableEntityQueryView(entityType.GetTableName()!, sqlTemplate);
}
/// <summary>
/// 刪除依賴項邏輯刪除實體查詢檢視
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="tableName">檢視對應的表名</param>
/// <param name="sqlTemplate">Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder DropDependencyLogicallyDeletableEntityQueryView(
this MigrationBuilder migrationBuilder,
string tableName,
IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
{
ArgumentNullException.ThrowIfNull(migrationBuilder);
ArgumentNullException.ThrowIfNull(sqlTemplate);
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentException($"“{nameof(tableName)}”不能為 null 或空。", nameof(tableName));
}
migrationBuilder.Sql(sqlTemplate.DropSqlTemplate.Replace("{tableName}", tableName));
return migrationBuilder;
}
/// <summary>
/// 建立依賴項邏輯刪除檢視
/// </summary>
/// <param name="entityType">實體型別</param>
/// <param name="sqlTemplate">Sql模板</param>
/// <returns>檢視建立Sql、檢視名稱、依賴資料來源名稱以及依賴型別的集合，為檢視建立排序提供線索</returns>
private static (string sql, string viewName, IReadOnlyList<(string principalDataSourceName, bool isViewDependency)> principalDataSourceNames) CreateDependencyLogicallyDeletableEntityQueryViewSql(
IEntityType entityType,
IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
{
ArgumentNullException.ThrowIfNull(entityType);
ArgumentNullException.ThrowIfNull(sqlTemplate);
var isDependencyLogicallyDeletableEntity = (entityType
.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
?.Any(static x => x == typeof(IDependencyLogicallyDeletable)) is true;
if (!isDependencyLogicallyDeletableEntity) throw new InvalidOperationException($"{entityType.Name}不是依賴項邏輯刪除實體或未配置檢視生成。");
var tableName = entityType.GetTableName()!;
var formatTableName = sqlTemplate.FormatTableOrColumnName(tableName)!;
var tableIdentifier = StoreObjectIdentifier.Table(tableName)!;
var columnEnumerable = entityType.GetProperties()
.Where(static prop => prop.FindAnnotation(EntityModelBuilderExtensions._dependencyLogicallyDeleteableAnnotationName)?.Value is not true)
.Select(prop => $"{formatTableName}.{sqlTemplate.FormatTableOrColumnName(prop.GetColumnName(tableIdentifier))}");
var columns = string.Join(", ", columnEnumerable);
var foreignKeys = entityType.GetForeignKeys()
.Where(static fk => fk.DeleteBehavior is DeleteBehavior.Cascade or DeleteBehavior.SetNull)
.Where(static fk =>
{
var annotationValue = fk.PrincipalEntityType
.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>;
return annotationValue
?.Any(static t =>
t == typeof(ILogicallyDeletable) || t == typeof(IDependencyLogicallyDeletable)) is true;
})
.Select(fk => BuildJoinInfo(entityType, fk))
.ToList();
if (foreignKeys.Count == 0) throw new InvalidOperationException($"實體{entityType.Name}沒有支援邏輯刪除或依賴項邏輯刪除的級聯外來鍵，無需實現{nameof(IDependencyLogicallyDeletable)}介面。");
var principalLogicallyDeleteColumns = string.Join(
", ",
foreignKeys
.SelectMany(p => BuildPrincipalDataSources(p.Key))
.Select(x => sqlTemplate.PrincipalLogicallyDeleteColumnTemplate.Replace("{principalLogicallyDeleteColumn}", x))
);
var joinTargetEnumerable = foreignKeys.Select(p =>
{
var formatPrincipalDataSourceName = sqlTemplate.FormatTableOrColumnName(p.Key.principalDataSourceName)!;
var conditions = p.Value.Select(x => $"{formatTableName}.{sqlTemplate.FormatTableOrColumnName(x.foreignKeyName)} = {formatPrincipalDataSourceName}.{sqlTemplate.FormatTableOrColumnName(x.principalKeyName)}");
return sqlTemplate.JoinTargetTemplate
.Replace("{principalDataSource}", formatPrincipalDataSourceName)
.Replace("{joinCondition}", string.Join(" AND ", conditions));
});
var joinTargets = string.Join("\r\n", joinTargetEnumerable);
var dependencyDeletedAtProperty = entityType.GetProperties()
.Single(static prop => prop.FindAnnotation(EntityModelBuilderExtensions._dependencyLogicallyDeleteableAnnotationName)?.Value is true);
var sql = sqlTemplate.CreateSqlTemplate
.Replace("{tableName}", tableName)
.Replace("{columns}", columns)
.Replace("{dependencyDeletedAtColumn}", sqlTemplate.FormatTableOrColumnName(dependencyDeletedAtProperty.GetColumnName(tableIdentifier)))
.Replace("{principalLogicallyDeleteColumns}", principalLogicallyDeleteColumns)
.Replace("{joinTargets}", joinTargets);
return (sql, entityType.GetViewName()!, foreignKeys.Select(x => (x.Key.principalDataSourceName, x.Key.isViewDependency)).ToImmutableList());
static KeyValuePair<(string principalDataSourceName, string? principalLogicallyDeletableColumnName, string? principalDependencyLogicallyDeletableColumnName, bool isViewDependency), IReadOnlyList<(string foreignKeyName, string principalKeyName)>> BuildJoinInfo(IEntityType entityType, IForeignKey foreignKey)
{
if (foreignKey.Properties.Count != foreignKey.PrincipalKey.Properties.Count) throw new InvalidOperationException($"外來鍵和主鍵欄位數量不一致。外來鍵實體：{entityType.Name}；主實體：{foreignKey.PrincipalEntityType.Name}");
var principalEntityType = foreignKey.PrincipalEntityType;
var principalIsDependencyLogicallyDeletableEntity = (principalEntityType
.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
?.Any(static t => t == typeof(IDependencyLogicallyDeletable)) is true;
var principalDataSourceName = principalIsDependencyLogicallyDeletableEntity
? principalEntityType.GetViewName()!
: principalEntityType.GetTableName()!;
var principalDataSourceIdentifier = principalIsDependencyLogicallyDeletableEntity
? StoreObjectIdentifier.View(principalDataSourceName)
: StoreObjectIdentifier.Table(principalDataSourceName);
var foreignTableName = entityType.GetTableName()!;
var foreignTableIdentifier = StoreObjectIdentifier.Table(foreignTableName);
var foreignKeyNames = foreignKey.Properties.Select(prop => prop.GetColumnName(foreignTableIdentifier)!);
var principalKeyNames = foreignKey.PrincipalKey.Properties.Select(prop => prop.GetColumnName(principalDataSourceIdentifier)!);
var joinConditionPairs = foreignKeyNames.Zip(principalKeyNames, (fk, pk) => (foreignKeyName: fk, principalKeyName: pk));
return KeyValuePair.Create(
(
principalDataSourceName,
principalEntityType.GetProperties()
.SingleOrDefault(prop => prop.FindAnnotation(EntityModelBuilderExtensions._logicallyDeleteableAnnotationName)?.Value is true)
?.GetColumnName(principalDataSourceIdentifier),
principalEntityType.GetProperties()
.SingleOrDefault(prop => prop.FindAnnotation(EntityModelBuilderExtensions._dependencyLogicallyDeleteableAnnotationName)?.Value is true)
?.GetColumnName(principalDataSourceIdentifier),
principalIsDependencyLogicallyDeletableEntity
),
joinConditionPairs.ToImmutableList() as IReadOnlyList<(string, string)>
);
}
IEnumerable<string> BuildPrincipalDataSources((string principalDataSourceName, string? principalLogicallyDeletableColumnName, string? principalDependencyLogicallyDeletableColumnName, bool _) val)
{
if (val.principalLogicallyDeletableColumnName is not null)
yield return $"{sqlTemplate.FormatTableOrColumnName(val.principalDataSourceName)}.{sqlTemplate.FormatTableOrColumnName(val.principalLogicallyDeletableColumnName)}";
if (val.principalDependencyLogicallyDeletableColumnName is not null)
yield return $"{sqlTemplate.FormatTableOrColumnName(val.principalDataSourceName)}.{sqlTemplate.FormatTableOrColumnName(val.principalDependencyLogicallyDeletableColumnName)}";
}
}
}
遷移擴充套件中對檢視操作的排序用到了樹形結構，感興趣的朋友可以檢視筆者的早期部落格C# 通用樹形資料結構瞭解詳細資訊。
資料庫上下文
public class ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: ApplicationIdentityDbContext<
ApplicationUser,
ApplicationRole,
IdentityKey,
ApplicationUserClaim,
ApplicationUserRole,
ApplicationUserLogin,
ApplicationRoleClaim,
ApplicationUserToken>(options)
{
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// 其他無關程式碼
// 自動根據資料庫型別進行資料庫相關的模型配置
switch (Database.ProviderName)
{
case _msSqlServerProvider:
modelBuilder.ApplyConfigurationsFromAssembly(
typeof(LogRecordEntityTypeConfiguration).Assembly,
type => type.GetCustomAttributes<DatabaseProviderAttribute>().Any(a => a.ProviderName is _msSqlServerProvider));
modelBuilder.ConfigureQueryFilterForILogicallyDeleteOrIDependencyLogicallyDeletable(DefaultSqlServerDependencyLogicallyDeletableNullValueSql.Instance);
break;
case _pomeloMySqlProvider:
modelBuilder.ApplyConfigurationsFromAssembly(
typeof(LogRecordEntityTypeConfiguration).Assembly,
type => type.GetCustomAttributes<DatabaseProviderAttribute>().Any(a => a.ProviderName is _pomeloMySqlProvider));
modelBuilder.ConfigureForTimeAuditable(DefaultMySqlTimeAuditableDefaultValueSql.Instance);
modelBuilder.ConfigureQueryFilterForILogicallyDeleteOrIDependencyLogicallyDeletable(DefaultMySqlDependencyLogicallyDeletableNullValueSql.Instance);
break;
case _msSqliteProvider:
goto default;
default:
throw new NotSupportedException(Database.ProviderName);
}
modelBuilder.ConfigEntityQueryView();
}
}
攔截器
/// <summary>
/// 把邏輯刪除實體的刪除變更為編輯，設定刪除時間，然後使用<see cref="LogicallyDeletedRuntimeAnnotation"/>標記執行時註釋便於區分普通的已編輯實體
/// </summary>
public class LogicallyDeletableSaveChangesInterceptor : SaveChangesInterceptor
{
/// <summary>
/// 邏輯刪除實體的執行時註釋名，註釋的值為<see langword="true"/>
/// </summary>
public const string LogicallyDeletedRuntimeAnnotation = "Runtime:LogicallyDeleted";
protected IServiceScopeFactory ScopeFactory { get; }
public LogicallyDeletableSaveChangesInterceptor(IServiceScopeFactory scopeFactory)
{
ArgumentNullException.ThrowIfNull(scopeFactory);
ScopeFactory = scopeFactory;
}
public override InterceptionResult<int> SavingChanges(
DbContextEventData eventData,
InterceptionResult<int> result)
{
OnSavingChanges(eventData);
return base.SavingChanges(eventData, result);
}
public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
DbContextEventData eventData,
InterceptionResult<int> result,
CancellationToken cancellationToken = default)
{
OnSavingChanges(eventData);
return base.SavingChangesAsync(eventData, result, cancellationToken);
}
public override int SavedChanges(SaveChangesCompletedEventData eventData, int result)
{
OnSavedChanges(eventData);
return base.SavedChanges(eventData, result);
}
public override ValueTask<int> SavedChangesAsync(
SaveChangesCompletedEventData eventData,
int result,
CancellationToken cancellationToken = default)
{
OnSavedChanges(eventData);
return base.SavedChangesAsync(eventData, result, cancellationToken);
}
public override void SaveChangesCanceled(DbContextEventData eventData)
{
OnSaveChangesCanceled(eventData);
base.SaveChangesCanceled(eventData);
}
public override Task SaveChangesCanceledAsync(
DbContextEventData eventData,
CancellationToken cancellationToken = default)
{
OnSaveChangesCanceled(eventData);
return base.SaveChangesCanceledAsync(eventData, cancellationToken);
}
public override void SaveChangesFailed(DbContextErrorEventData eventData)
{
OnSaveChangesFailed(eventData);
base.SaveChangesFailed(eventData);
}
public override Task SaveChangesFailedAsync(
DbContextErrorEventData eventData,
CancellationToken cancellationToken = default)
{
OnSaveChangesFailed(eventData);
return base.SaveChangesFailedAsync(eventData, cancellationToken);
}
protected virtual void OnSavingChanges(DbContextEventData eventData)
{
ArgumentNullException.ThrowIfNull(eventData.Context);
using var scope = ScopeFactory.CreateScope();
var timeProvider = scope.ServiceProvider.GetRequiredService<TimeProvider>();
eventData.Context.ChangeTracker.DetectChanges();
// 按實體後設資料分組
var typedEntries = eventData.Context.ChangeTracker.Entries()
.Where(static entry => entry.State is EntityState.Deleted)
.GroupBy(static entry => entry.Metadata);
foreach (var entries in typedEntries)
{
// 相同後設資料的不同上下文物件使用不同的邏輯刪除快取
// 實體模型的執行時後設資料會透過邏輯刪除快取持有上下文物件的引用，需要在所有儲存攔截方法中處理快取並刪除引用
var logicalDeletedCacheDictionary = entries.Key
.GetOrAddRuntimeAnnotationValue<ConcurrentDictionary<DbContext, HashSet<EntityEntry>>, object?>(
LogicallyDeletedRuntimeAnnotation,
static dbContext => [],
null);
var logicalDeletedCache = logicalDeletedCacheDictionary.GetOrAdd(eventData.Context, []);
foreach (var entry in entries)
{
if (entry.Entity is ILogicallyDeletable logicallyDeletable)
{
entry.State = EntityState.Modified;
logicallyDeletable.DeletedAt = timeProvider.GetLocalNow();
// 使用執行時註釋快取邏輯刪除的實體
logicalDeletedCache.Add(entry);
}
}
}
}
protected virtual void OnSavedChanges(SaveChangesCompletedEventData eventData)
{
// 儲存成功時需要解除對實體的跟蹤，其他情況無需處理
PostProcessEntriesAndCleanLogicalDeletedCache(eventData, static entry => entry.State = EntityState.Detached);
}
protected virtual void OnSaveChangesCanceled(DbContextEventData eventData)
{
PostProcessEntriesAndCleanLogicalDeletedCache(eventData);
}
protected virtual void OnSaveChangesFailed(DbContextEventData eventData)
{
PostProcessEntriesAndCleanLogicalDeletedCache(eventData);
}
/// <summary>
/// 獲取已邏輯刪除的實體
/// </summary>
/// <typeparam name="TEntity">實體型別</typeparam>
/// <param name="eventData"></param>
/// <returns>已邏輯刪除的實體集合</returns>
protected static IReadOnlyList<EntityEntry> GetLogicallyDeletedEntries<TEntity>(DbContextEventData eventData)
where TEntity : class
{
var dict = eventData.Context?.Model
.FindEntityType(typeof(TEntity))
?.FindRuntimeAnnotationValue(LogicallyDeletedRuntimeAnnotation) as ConcurrentDictionary<DbContext, HashSet<EntityEntry>>;
var entities = dict
?.GetValueOrDefault(eventData.Context!)
?.Where(static e => e.Entity is TEntity)
?.ToImmutableArray() ?? [];
return entities;
}
/// <summary>
/// 獲取已邏輯刪除的實體
/// </summary>
/// <param name="eventData"></param>
/// <returns>已邏輯刪除的實體集合</returns>
protected static IReadOnlyList<EntityEntry> GetLogicallyDeletedEntries(DbContextEventData eventData)
{
var entities = eventData.Context?.Model
.GetEntityTypes()
.Select(static et => et.FindRuntimeAnnotationValue(LogicallyDeletedRuntimeAnnotation) as ConcurrentDictionary<DbContext, HashSet<EntityEntry>>)
.Where(static dict => dict is not null)
.Select(dict => dict!.GetValueOrDefault(eventData.Context!))
.Where(static hs => hs is { Count: > 0 })
.SelectMany(static hs => hs!)
.ToImmutableArray() ?? [];
return entities;
}
/// <summary>
/// 在儲存後事件呼叫，執行自定義實體處理，然後清除執行時後設資料註釋的快取，避免記憶體洩漏
/// </summary>
/// <param name="eventData">事件資料</param>
/// <param name="action">自定義處理委託</param>
protected static void PostProcessEntriesAndCleanLogicalDeletedCache(DbContextEventData eventData, Action<EntityEntry>? action = null)
{
ArgumentNullException.ThrowIfNull(eventData.Context);
var entrySetDict = eventData.Context.ChangeTracker.Entries()
.GroupBy(static e => e.Metadata)
.Select(static group => group.Key.FindRuntimeAnnotationValue(LogicallyDeletedRuntimeAnnotation) as ConcurrentDictionary<DbContext, HashSet<EntityEntry>>)
.Where(static dict => dict is not null)
.ToList();
var entrySets = entrySetDict
.Select(dict => dict!.GetValueOrDefault(eventData.Context))
.Where(static set => set is not null);
foreach (var set in entrySets)
{
foreach (var entry in set!)
{
action?.Invoke(entry);
}
set.Clear();
}
// 清空當前上下文的邏輯刪除快取避免記憶體洩漏
foreach (var dict in entrySetDict)
{
dict!.TryRemove(eventData.Context, out var _);
}
}
}
/// <summary><inheritdoc cref="LogicallyDeletableSaveChangesInterceptor"/></summary>
/// <typeparam name="TUser">使用者實體型別</typeparam>
/// <typeparam name="TKey">使用者實體主鍵型別</typeparam>
/// <param name="scopeFactory"></param>
/// <remarks>為 Identity 實體的唯一索引屬性設定特別刪除標記</remarks>
public class IdentityLogicallyDeletableSaveChangesInterceptor<TUser, TKey>(IServiceScopeFactory scopeFactory)
: LogicallyDeletableSaveChangesInterceptor(scopeFactory)
where TUser : IdentityUser<TKey>, ILogicallyDeletable
where TKey : IEquatable<TKey>
{
private const string _delMark = "!del";
/// <summary>
/// 把已刪除的使用者的特殊屬性增加標記，避免無法建立同名使用者和查詢出已刪除的同名使用者
/// </summary>
/// <param name="eventData"></param>
protected override void OnSavingChanges(DbContextEventData eventData)
{
// 此處會把應該邏輯刪除的已刪除實體調整為已修改，不能透過ChangeTracker找到已邏輯刪除的實體
base.OnSavingChanges(eventData);
var entityEntries = GetLogicallyDeletedEntries<TUser>(eventData);
foreach (var entry in entityEntries)
{
var entity = entry.Entity as TUser;
entity!.Email += _delMark + entity.DeletedAt.Ticks;
entity.NormalizedEmail += _delMark.ToUpperInvariant() + entity.DeletedAt.Ticks;
entity.UserName += _delMark + entity.DeletedAt.Ticks;
entity.NormalizedUserName += _delMark.ToUpperInvariant() + entity.DeletedAt.Ticks;
}
}
}
服務配置
services.AddPooledDbContextFactory<ApplicationDbContext>((sp, options) =>
{
if (sp.GetRequiredService<IWebHostEnvironment>().IsDevelopment())
{
options.EnableSensitiveDataLogging();
options.EnableDetailedErrors();
}
// 註冊攔截器
var scopeFactory = sp.GetRequiredService<IServiceScopeFactory>();
options.AddInterceptors(new IdentityLogicallyDeletableSaveChangesInterceptor<ApplicationUser, IdentityKey>(scopeFactory));
ConfigureDbConnection(options, sp);
});
遷移
/// <inheritdoc />
public partial class V0002 : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
// 其他遷移工具生成的程式碼
migrationBuilder.ApplyEntityQueryView(
this, // 當前遷移
new V0001() { ActiveProvider = this.ActiveProvider }, // 上一個遷移
true, // 是升級遷移
DefaultSqlServerDependencyLogicallyDeletableEntityViewSqlTemplate.Instance,
DefaultSqlServerTreeEntityViewSqlTemplate.Instance); // 下篇會詳細說明
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
// 其他遷移工具生成的程式碼
migrationBuilder.ApplyEntityQueryView(
this,
new V0001() { ActiveProvider = this.ActiveProvider },
false, // 是回滾遷移
DefaultSqlServerDependencyLogicallyDeletableEntityViewSqlTemplate.Instance,
DefaultSqlServerTreeEntityViewSqlTemplate.Instance);
}
}
此處以第二版遷移為例，方便演示回滾遷移所需的引數。只需要呼叫這一個擴充套件方法就可以完成檢視的自動遷移，生成的遷移指令碼也是完整的，可以直接使用。
唯一的遺憾是模型差異比較需要在遷移處現場完成，為此需要手動提供上一個遷移模型。如果要完全在框架中完成比較和程式碼生成，需要改動的東西太多也很麻煩，權衡之後決定自行在遷移中實現。如果哪天EF Core把這方面的功能做成類似攔截器那種不需要繼承現有型別就能插入遷移程式碼生成流程的話能方便不少。
由於EF Core支援很多複雜的模型對映，遷移擴充套件可能還有沒覆蓋到的情況，目前只能說常規對映基本正常。
這個實現套路也是參考了筆者之前的一篇舊文EntityFramework Core 2.x/3.x （ef core） 在遷移中自動生成資料庫表和列說明。不過現在註釋已經是內建遷移功能的一部分，因此無需手動實現。參考專案中保留了相關程式碼，只不過把實現方案換成了掛接到內建實現，到模型構造的部分依然不變，但不再需要手動調整遷移程式碼，變成真正的一次編寫永久使用。
遷移指令碼預覽（節選）
-- 同時依賴多個主實體的情況
CREATE VIEW QueryView_Entity2_1s
AS
SELECT [Entity2_1s].[Id], [Entity2_1s].[DeletedAt], [Entity2_1s].[Depth], [Entity2_1s].[Entity1_1_1Id], [Entity2_1s].[Entity2Id], [Entity2_1s].[Entity2_0Id], [Entity2_1s].[HasChildren], [Entity2_1s].[IsLeaf], [Entity2_1s].[IsRoot], [Entity2_1s].[ParentId], [Entity2_1s].[Path], [Entity2_1s].[Text2_1],
(SELECT MAX([DeleteTimeTable].[DeletedAt])
FROM (VALUES ([Entity1_1_1s].[DeletedAt]), ([Entity2_0s].[DeletedAt])) AS DeleteTimeTable([DeletedAt])) AS [DependencyDeletedAt]
FROM [Entity2_1s]
LEFT JOIN [Entity1_1_1s]
ON [Entity2_1s].[Entity1_1_1Id] = [Entity1_1_1s].[Id]
LEFT JOIN [Entity2_0s]
ON [Entity2_1s].[Entity2_0Id] = [Entity2_0s].[Id];
GO
IF EXISTS(SELECT * FROM [sysobjects] WHERE [id] = OBJECT_ID(N'QueryView_Entity3s') AND objectproperty(id, N'IsView') = 1)
BEGIN
DROP VIEW [QueryView_Entity3s]
END
GO
-- 依賴的主實體也有檢視的情況
CREATE VIEW QueryView_Entity3s
AS
SELECT [Entity3s].[Id], [Entity3s].[Entity1_1Id], [Entity3s].[Text2],
(SELECT MAX([DeleteTimeTable].[DeletedAt])
FROM (VALUES ([QueryView_Entity1_1s].[DeletedAt]), ([QueryView_Entity1_1s].[MyDependencyDeletedAt])) AS DeleteTimeTable([DeletedAt])) AS [DependencyDeletedAt]
FROM [Entity3s]
LEFT JOIN [QueryView_Entity1_1s]
ON [Entity3s].[Entity1_1Id] = [QueryView_Entity1_1s].[Id];
GO
外來鍵和主實體是複合列的情況也是支援的，只是在示例中沒有用上，之前單獨的開發測試時驗證過，如果不出意外應該是不會壞掉。
結語
經過這一系列的操作，最終實現了軟刪除和級聯軟刪除的自動化且能有效模擬級聯刪除的行為，在資料庫端也能儘可能簡化查詢的編寫。利用EF Core的各種高階功能最終實現了對業務程式碼的0侵入。
示例程式碼：SoftDeleteDemo.rar。主頁顯示異常請在libman.json上右鍵恢復前端包。
QQ群
讀者交流QQ群：540719365
歡迎讀者和廣大朋友一起交流，如發現本書錯誤也歡迎透過部落格園、QQ群等方式告知筆者。
本文地址：https://www.cnblogs.com/coredx/p/18305274.html