前言 資料庫併發，數據審計和軟刪除一直是數據持久化方面的經典問題。早些時候，這些工作需要手寫複雜的SQL或者通過存儲過程和觸發器實現。手寫複雜SQL對軟體可維護性構成了相當大的挑戰，隨著SQL字數的變多，用到的嵌套和複雜語法增加，可讀性和可維護性的難度是幾何級暴漲。因此如何在實現功能的同時控制這些S ...
前言
資料庫併發，數據審計和軟刪除一直是數據持久化方面的經典問題。早些時候，這些工作需要手寫複雜的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(
DbContextE
