前言 資料庫併發，數據審計和軟刪除一直是數據持久化方面的經典問題。早些時候，這些工作需要手寫複雜的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 開發從入門到實踐》上市，作者親自來打廣告了！
正文
由於這些功能設計的代碼量和知識點較多，為控制篇幅，本文介紹樹形查詢功能。
SqlServer原生支持分層數據，EF Core也提供了相應的支持，但是很遺憾，這又是一個獨占功能。為了相容其他資料庫只能單獨處理。由於EF Core的導航修複功能，使用ParentId的自關聯結構能得到原生支持。這也是描述一棵樹最簡單且不會破壞數據完整性的方式（即這種描述方式永遠滿足樹結構的所有判定約束）。但是在查詢方面，這種結構確並不方便，因此為了簡化查詢，出現了其他存儲樹的設計方式，常見的有左右值編碼、路徑描述和額外的關係描述表等。這些描述方式能在一定程度上簡化查詢，但是確無法在物理上確保數據完整性，這就對數據維護提出了嚴峻的挑戰。
在中篇我們用視圖實現了全自動的級聯軟刪除模擬，那麼是否同樣可以用視圖來解決樹形結構的查詢問題呢？答案是肯定的，而這隻有一個小小的前提條件——支持公用表表達式（SQL中的遞歸）。這樣就能實現物理表中使用ParentId的自關聯確保數據完整性，同時自動相容EF Core的導航修複。而用於簡化查詢的其他信息則由視圖自動計算生成。
生成樹的視圖功能其實已經在本文宣傳的書中實現了，不過這次新增自動軟刪除後，樹視圖也需要考慮如何相容軟刪除。一開始筆者想過在一個視圖定義中實現，後來發現這種方式開發難度比較大，而且不利於復用已有的研究成果。最終決定使用獨立的視圖，這就涉及到視圖數據源的選擇，因為EF Core只能映射一個視圖。經過一番思考發現樹形視圖永遠只依賴其自身的表或視圖，因此EF Core映射到樹形視圖，屬性視圖依賴軟刪除視圖是最簡單方便的。在之前介紹軟刪除的文章中已經出現了和樹有關的代碼，這些代碼的一部分用處就是選擇映射目標。
代碼實現
基礎介面
/// <summary>
/// 樹形數據介面
/// </summary>
/// <typeparam name="T">節點數據類型</typeparam>
public interface ITree<T>
{
/// <summary>
/// 父節點
/// </summary>
T? Parent { get; set; }
/// <summary>
/// 子節點集合
/// </summary>
IList<T> Children { get; set; }
/// <summary>
/// 節點深度，根的深度為0
/// </summary>
int Depth { get; }
/// <summary>
/// 是否是根節點
/// </summary>
bool IsRoot { get; }
/// <summary>
/// 是否是葉節點
/// </summary>
bool IsLeaf { get; }
/// <summary>
/// 是否有子節點
/// </summary>
bool HasChildren { get; }
/// <summary>
/// 節點路徑（UNIX路徑格式，以“/”分隔）
/// </summary>
string? Path { get; }
}
/// <summary>
/// 樹形實體介面
/// </summary>
/// <typeparam name="T">實體類型</typeparam>
public interface ITreeEntity<T> : IEntity, ITree<T>
{
}
/// <summary>
/// 樹形實體介面
/// </summary>
/// <typeparam name="TKey">主鍵類型</typeparam>
/// <typeparam name="TEntity">實體類型</typeparam>
public interface ITreeEntity<TKey, TEntity> : ITreeEntity<TEntity>, IEntity<TKey>
where TKey : struct, IEquatable<TKey>
where TEntity : ITreeEntity<TKey, TEntity>
{
/// <summary>
/// 父節點Id
/// </summary>
TKey? ParentId { get; set; }
}
/// <summary>
/// 實體介面
/// </summary>
public interface IEntity;
/// <summary>
/// 實體介面
/// </summary>
/// <typeparam name="TKey">唯一標識的類型</typeparam>
public interface IEntity<TKey> : IEntity
where TKey : struct, IEquatable<TKey>
{
/// <summary>
/// 實體的唯一標識
/// </summary>
TKey Id { get; set; }
}
本文的ITree<T>介面就是從前文軟刪除視圖操作排序用的介面簡化而來。
模型配置擴展
/// <summary>
/// 樹形實體模型配置擴展
/// </summary>
public static class TreeEntityModelBuilderExtensions
{
private const string _queryViewAnnotationName = EntityModelBuilderExtensions._queryViewAnnotationName;
/// <summary>
/// 配置樹形實體介面
/// </summary>
/// <typeparam name="TKey">主鍵類型</typeparam>
/// <typeparam name="TEntity">樹形實體類型</typeparam>
/// <param name="builder">實體類型構造器</param>
/// <param name="dummyValueSql">表用計算列的虛假值生成Sql</param>
/// <returns>實體類型構造器</returns>
public static EntityTypeBuilder<TEntity> ConfigureForITreeEntity<TKey, TEntity>(
this EntityTypeBuilder<TEntity> builder,
ITreeEntityDummyValueSql dummyValueSql
)
where TKey : struct, IEquatable<TKey>
where TEntity : class, ITreeEntity<TKey, TEntity>
{
ArgumentNullException.ThrowIfNull(builder);
builder.HasOne(e => e.Parent)
.WithMany(pe => pe.Children)
.HasForeignKey(e => e.ParentId);
builder.Property(e => e.Depth)
.HasComputedColumnSql(dummyValueSql.DepthSql);
builder.Property(e => e.HasChildren)
.HasComputedColumnSql(dummyValueSql.HasChildrenSql);
builder.Property(e => e.Path)
.HasComputedColumnSql(dummyValueSql.PathSql);
ConfigQueryViewAnnotationForTreeEntity<TKey, TEntity>(builder);
return builder;
}
/// <summary>
/// 配置樹形實體介面
/// </summary>
/// <param name="modelBuilder">模型構造器</param>
/// <param name="dummyValueSql">表用計算列的虛假值生成Sql</param>
/// <returns>模型構造器</returns>
public static ModelBuilder ConfigureForITreeEntity(this ModelBuilder modelBuilder, ITreeEntityDummyValueSql dummyValueSql)
{
ArgumentNullException.ThrowIfNull(modelBuilder);
ArgumentNullException.ThrowIfNull(dummyValueSql);
foreach (var entity
in modelBuilder.Model.GetEntityTypes()
.Where(static e => e.ClrType.IsDerivedFrom(typeof(ITreeEntity<,>))))
{
var entityTypeBuilderMethod = GetEntityTypeBuilderMethod(entity);
var treeEntityMethod = GetEntityTypeConfigurationMethod(
nameof(ConfigureForITreeEntity),
2,
entity.FindProperty(nameof(TreeType.Id))!.ClrType,
entity.ClrType);
treeEntityMethod.Invoke(null, [entityTypeBuilderMethod.Invoke(modelBuilder, null), dummyValueSql]);
}
return modelBuilder;
}
/// <summary>
/// 配置樹形實體的查詢視圖註解
/// </summary>
/// <typeparam name="TKey">實體主鍵類型</typeparam>
/// <typeparam name="TEntity">實體類型</typeparam>
/// <param name="builder">實體類型構造器</param>
private static void ConfigQueryViewAnnotationForTreeEntity<TKey, TEntity>(EntityTypeBuilder<TEntity> builder)
where TKey : struct, IEquatable<TKey>
where TEntity : class, ITreeEntity<TKey, TEntity>
{
var annotationValue = builder.Metadata.FindAnnotation(_queryViewAnnotationName)?.Value;
if (annotationValue is null)
{
builder.HasAnnotation(_queryViewAnnotationName, new List<Type>() { typeof(ITreeEntity<,>) });
}
else
{
var stringListAnnotationValue = annotationValue as List<Type>;
if (stringListAnnotationValue is not null && stringListAnnotationValue.Find(static x => x == typeof(ITreeEntity<,>)) is null)
{
stringListAnnotationValue.Add(typeof(ITreeEntity<,>));
}
}
}
}
/// <summary>
/// 僅用於內部輔助，無實際作用
/// </summary>
file sealed class TreeType : ITreeEntity<int, TreeType>
{
public TreeType()
{
throw new NotImplementedException();
}
public int? ParentId { get => throw new NotImplementedException(); set => throw new NotImplementedException(); }
public TreeType? Parent { get => throw new NotImplementedException(); set => throw new NotImplementedException(); }
public IList<TreeType> Children { get => throw new NotImplementedException(); set => throw new NotImplementedException(); }
public int Depth => throw new NotImplementedException();
public bool IsRoot => throw new NotImplementedException();
public bool IsLeaf => throw new NotImplementedException();
public bool HasChildren => throw new NotImplementedException();
public string? Path => throw new NotImplementedException();
public int Id { get => throw new NotImplementedException(); set => throw new NotImplementedException(); }
}
Sql模版（以SqlServer為例）
/// <summary>
/// 樹形實體的視圖列在表中的臨時值映射
/// <para>EF Core目前還不支持多重映射時分別配置表和視圖的映射，因此需要在表中映射一個同名計算列</para>
/// </summary>
public interface ITreeEntityDummyValueSql
{
/// <summary>
/// 節點深度的SQL
/// </summary>
string DepthSql { get; }
/// <summary>
/// 節點是否有子樹的SQL
/// </summary>
string HasChildrenSql { get; }
/// <summary>
/// 節點路徑的SQL
/// </summary>
string PathSql { get; }
}
public class DefaultSqlServerTreeEntityDummyValueSql : ITreeEntityDummyValueSql
{
public static DefaultSqlServerTreeEntityDummyValueSql Instance => new();
private const string _depthSql = "-1";
private const string _hasChildrenSql = "cast(0 as bit)";
private const string _pathSql = "''";
public string DepthSql => _depthSql;
public string HasChildrenSql => _hasChildrenSql;
public string PathSql => _pathSql;
private DefaultSqlServerTreeEntityDummyValueSql() { }
}
/// <summary>
/// 樹形實體的視圖SQL模板
/// </summary>
public interface ITreeEntityDatabaseViewSqlTemplate : ITableOrColumnNameFormattable
{
/// <summary>
/// 創建視圖的模板
/// </summary>
string CreateSqlTemplate { get; }
/// <summary>
/// 刪除視圖的模板
/// </summary>
string DropSqlTemplate { get; }
}
public class DefaultSqlServerTreeEntityViewSqlTemplate : ITreeEntityDatabaseViewSqlTemplate
{
public static DefaultSqlServerTreeEntityViewSqlTemplate Instance => new();
private const string _viewNameTemplate = $$"""{{EntityModelBuilderExtensions._treeQueryViewNamePrefixes}}{tableName}""";
private const string _createSqlTemplate =
$$"""
--創建或重建樹形實體查詢視圖
{{_dropSqlTemplate}}
CREATE VIEW {{_viewNameTemplate}} --創建視圖
AS
WITH [temp]({columns}, [Depth], [Path], [HasChildren]) AS
(
--初始查詢（這裡的 [ParentId] IS NULL 在數據中是最底層的根節點）
SELECT {columns},
0 AS [Depth],
'/' + CAST([Id] AS nvarchar(max)) + '/' AS [Path], --如果Id使用Guid類型，可能會導致層數太深時出問題（大概100層左右，超過4000字之後的字元串會被砍掉,sqlserver 2005以後用 nvarchar(max)可以突破限制），Guid的字數太多了
(CASE WHEN EXISTS(SELECT 1 FROM [{dataSourceName}] WHERE [{dataSourceName}].[ParentId] = [Root].[Id]) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END) AS [HasChildren]
FROM [{dataSourceName}] AS [Root]
WHERE [Root].[ParentId] IS NULL
UNION ALL
--遞歸條件
SELECT {child.columns},
[Parent].[Depth] + 1,
[Parent].[Path] + CAST([Child].[Id] AS nvarchar(max)) + '/' AS [Path],
(CASE WHEN EXISTS(SELECT 1 FROM [{dataSourceName}] WHERE [{dataSourceName}].[ParentId] = [Child].[Id]) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END) AS [HasChildren]
FROM [{dataSourceName}] AS [Child] --3：這裡的臨時表和原始數據表都必須使用別名不然遞歸的時候不知道查詢的是哪個表的列
INNER JOIN [temp] AS [Parent]
ON ([Child].[ParentId] = [Parent].[Id]) --這個關聯關係很重要，一定要理解一下誰是誰的父節點
)
--4：遞歸完成後 一定不要少了這句查詢語句 否則會報錯
SELECT *
FROM [temp];
GO
""";
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? FormatTableOrColumnName(string? name)
{
if(name is null) return null;
return $"[{name}]";
}
private DefaultSqlServerTreeEntityViewSqlTemplate() { }
}
遷移擴展
/// <summary>
/// 樹形實體視圖遷移擴展
/// </summary>
public static class TreeEntityMigrationBuilderExtensions
{
private static readonly ImmutableArray<string> _properties = ["Depth", "Path", "HasChildren"];
/// <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 ApplyTreeEntityQueryView(
this MigrationBuilder migrationBuilder,
Migration thisVersion,
Migration? previousVersion,
bool isUp,
ITreeEntityDatabaseViewSqlTemplate 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(ITreeEntity<,>)) is true
);
var previousVersionEntityTypes = previousVersion?.TargetModel.GetEntityTypes()
.Where(static et =>
(et.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
?.Any(x => x == typeof(ITreeEntity<,>)) 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(en => en.GetTableName() == alterTable.OldTable.Name) is true)
{
pendingViewOperations.Add((null, alterTable.OldTable.Name, false));
}
if (thisVersionEntityTypes!.Any(en => en.GetTableName() == alterTable.Name))
{
var entity = thisVersionEntityTypes!.Single(en => en.GetTableName() == alterTable.Name);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
}
// 回滾遷移用上一個版本的模型重建視圖
else
{
// 如果當前版本這個實體是樹形實體，刪除舊視圖
if (thisVersionEntityTypes.Any(en => en.GetTableName() == alterTable.OldTable.Name))
{
pendingViewOperations.Add((null, alterTable.OldTable.Name, false));
}
EnsureMigrationOfPreviousVersion(previousVersion);
if (previousVersionEntityTypes!.Any(en => en.GetTableName() == 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 columnOperation in
migrationBuilder.Operations.Where(static op =>
{
var opType = op.GetType();
return opType.IsDerivedFrom<ColumnOperation>() || opType.IsDerivedFrom<DropColumnOperation>();
}))
{
if (columnOperation is AddColumnOperation addColumn)
{
if (isUp && thisVersionEntityTypes!.Any(en => en.GetTableName() == addColumn.Table))
{
var entity = thisVersionEntityTypes!.Single(en => en.GetTableName() == addColumn.Table);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
if (!isUp)
{
EnsureMigrationOfPreviousVersion(previousVersion);
if (previousVersionEntityTypes!.Any(en => en.GetTableName() == addColumn.Table))
{
var entity = previousVersionEntityTypes!.Single(en => en.GetTableName() == addColumn.Table);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
}
}
else if (columnOperation is AlterColumnOperation alterColumn/* && alterColumn.OldColumn.Name is not null && alterColumn.Name != alterColumn.OldColumn.Name*/)
{
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(en => en.GetTableName() == alterColumn.Table))
{
var entity = previousVersionEntityTypes!.Single(en => en.GetTableName() == alterColumn.Table);
AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
}
}
}
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));
if (isUp)
{
// 如果當前版本的實體確實是樹形實體，選擇創建視圖的命令
if ((thisVersionEntityTypes
?.SingleOrDefault(et => et.GetTableName() == entityViewOperations.Key)
?.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
?.Any(x => x == typeof(ITreeEntity<,>)) is true)
{
pendingViewOperations.Add(entityViewOperations.Value.First(o => o.entity is not null && o.isCreate));
}
else
{
pendingViewOperations.Add(entityViewOperations.Value.First(o => !o.isCreate));
}
}
else
{
// 當前遷移就是第一版，選擇刪除視圖命令
if (previousVersion is null)
{
pendingViewOperations.Add(entityViewOperations.Value.First(o => !o.isCreate));
}
// 如果上一版本的實體確實是樹形實體，選擇創建視圖的命令
else if ((previousVersionEntityTypes
?.Single(et => et.GetTableName() == entityViewOperations.Key)
.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
?.Any(x => x == typeof(IDependencyLogicallyDeletable)) is true)
{
pendingViewOperations.Add(entityViewOperations.Value.First(o => o.entity is not null && o.isCreate));
}
else
{
pendingViewOperations.Add(entityViewOperations.Value.First(o => !o.isCreate));
}
}
}
foreach (var (entity, tableName, isCreate) in pendingViewOperations)
{
if (isCreate) migrationBuilder.CreateTreeEntityQueryView(entity!, sqlTemplate);
else if (entity is not null) migrationBuilder.DropTreeEntityQueryView(entity, sqlTemplate);
else if (tableName is not null) migrationBuilder.DropTreeEntityQueryView(tableName, sqlTemplate);
else throw new InvalidOperationException("遷移實體類型和遷移表名不能同時為 null。");
}
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 CreateTreeEntityQueryView(
this MigrationBuilder migrationBuilder,
IEntityType entityType,
ITreeEntityDatabaseViewSqlTemplate sqlTemplate)
{
ArgumentNullException.ThrowIfNull(migrationBuilder);
ArgumentNullException.ThrowIfNull(entityType);
ArgumentNullException.ThrowIfNull(sqlTemplate);
var isTreeEntity = (entityType
.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
?.Any(static x => x == typeof(ITreeEntity<,>)) is true;
if (!isTreeEntity) throw new InvalidOperationException($"{entityType.Name}不是樹形實體或未配置視圖生成。");
var isDependencyLogicallyDeletableEntity = (entityType
.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
?.Any(static x => x == typeof(IDependencyLogicallyDeletable)) is true;
var tableName = entityType.GetTableName()!;
var dataSourceName = isDependencyLogicallyDeletableEntity
? $"{EntityModelBuilderExtensions._queryViewNamePrefixes}{tableName}"
: tableName;
var tableIdentifier = StoreObjectIdentifier.Table(tableName);
var columnNames = entityType.GetProperties()
.Where(static c => !_properties.Contains(c.Name))
.Select(pro => sqlTemplate.FormatTableOrColumnName(pro.GetColumnName(tableIdentifier)));
var childColumnNames = columnNames.Select(c => $@"{sqlTemplate.FormatTableOrColumnName("Child")}.{c}");
migrationBuilder.Sql(sqlTemplate.CreateSqlTemplate
.Replace("{tableName}", tableName)
.Replace("{dataSourceName}", dataSourceName)
.Replace("{columns}", string.Join(", ", columnNames))
.Replace("{child.columns}", string.Join(", ", childColumnNames))
);
return migrationBuilder;
}
/// <summary>
/// 刪除樹形實體查詢視圖
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="entityType">實體類型</param>
/// <param name="sqlTemplate">Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder DropTreeEntityQueryView(
this MigrationBuilder migrationBuilder,
IEntityType entityType,
ITreeEntityDatabaseViewSqlTemplate sqlTemplate)
{
ArgumentNullException.ThrowIfNull(migrationBuilder);
ArgumentNullException.ThrowIfNull(entityType);
ArgumentNullException.ThrowIfNull(sqlTemplate);
return migrationBuilder.DropTreeEntityQueryView(entityType.GetTableName()!, sqlTemplate);
}
/// <summary>
/// 刪除樹形實體查詢視圖
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="tableName">視圖對應的表名</param>
/// <param name="sqlTemplate">Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder DropTreeEntityQueryView(
this MigrationBuilder migrationBuilder,
string tableName,
ITreeEntityDatabaseViewSqlTemplate 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;
}
}
public static class EntityMigrationBuilderExtensions
{
/// <summary>
/// 自動掃描遷移模型並配置實體查詢視圖
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="thisVersion">當前版本的遷移</param>
/// <param name="previousVersion">上一個版本的遷移</param>
/// <param name="isUp">是否為升級遷移</param>
/// <param name="dependencyLogicallyDeletableEntityViewSqlTemplate">依賴項邏輯刪除實體視圖Sql模板</param>
/// <param name="treeEntityViewSqlTemplate">樹形實體視圖Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder ApplyEntityQueryView(
this MigrationBuilder migrationBuilder,
Migration thisVersion,
Migration? previousVersion,
bool isUp,
IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate dependencyLogicallyDeletableEntityViewSqlTemplate,
ITreeEntityDatabaseViewSqlTemplate treeEntityViewSqlTemplate)
{
ArgumentNullException.ThrowIfNull(migrationBuilder);
ArgumentNullException.ThrowIfNull(thisVersion);
ArgumentNullException.ThrowIfNull(dependencyLogicallyDeletableEntityViewSqlTemplate);
ArgumentNullException.ThrowIfNull(treeEntityViewSqlTemplate);
migrationBuilder.ApplyDependencyLogicallyDeletableEntityQueryView(
thisVersion,
previousVersion,
isUp,
dependencyLogicallyDeletableEntityViewSqlTemplate);
migrationBuilder.ApplyTreeEntityQueryView(
thisVersion,
previousVersion,
isUp,
treeEntityViewSqlTemplate);
return migrationBuilder;
}
/// <summary>
/// 創建樹形實體查詢視圖
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="entityType">實體類型</param>
/// <param name="dependencyLogicallyDeletableEntityViewSqlTemplate">依賴項邏輯刪除實體視圖Sql模板</param>
/// <param name="treeEntityViewSqlTemplate">樹形實體視圖Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder CreateEntityQueryView(
this MigrationBuilder migrationBuilder,
IEntityType entityType,
IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate dependencyLogicallyDeletableEntityViewSqlTemplate,
ITreeEntityDatabaseViewSqlTemplate treeEntityViewSqlTemplate)
{
ArgumentNullException.ThrowIfNull(migrationBuilder);
ArgumentNullException.ThrowIfNull(entityType);
ArgumentNullException.ThrowIfNull(dependencyLogicallyDeletableEntityViewSqlTemplate);
ArgumentNullException.ThrowIfNull(treeEntityViewSqlTemplate);
migrationBuilder.CreateDependencyLogicallyDeletableEntityQueryView(entityType, dependencyLogicallyDeletableEntityViewSqlTemplate);
migrationBuilder.CreateTreeEntityQueryView(entityType, treeEntityViewSqlTemplate);
return migrationBuilder;
}
/// <summary>
/// 刪除實體查詢視圖
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="entityType">實體類型</param>
/// <param name="dependencyLogicallyDeletableEntityViewSqlTemplate">依賴項邏輯刪除實體視圖Sql模板</param>
/// <param name="treeEntityViewSqlTemplate">樹形實體視圖Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder DropEntityQueryView(
this MigrationBuilder migrationBuilder,
IEntityType entityType,
IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate dependencyLogicallyDeletableEntityViewSqlTemplate,
ITreeEntityDatabaseViewSqlTemplate treeEntityViewSqlTemplate)
{
ArgumentNullException.ThrowIfNull(migrationBuilder);
ArgumentNullException.ThrowIfNull(entityType);
ArgumentNullException.ThrowIfNull(dependencyLogicallyDeletableEntityViewSqlTemplate);
ArgumentNullException.ThrowIfNull(treeEntityViewSqlTemplate);
return migrationBuilder.DropEntityQueryView(
entityType.GetTableName()!,
dependencyLogicallyDeletableEntityViewSqlTemplate,
treeEntityViewSqlTemplate);
}
/// <summary>
/// 刪除實體查詢視圖
/// </summary>
/// <param name="migrationBuilder">遷移構造器</param>
/// <param name="tableName">視圖對應的表名</param>
/// <param name="dependencyLogicallyDeletableEntityViewSqlTemplate">依賴項邏輯刪除實體視圖Sql模板</param>
/// <param name="treeEntityViewSqlTemplate">樹形實體視圖Sql模板</param>
/// <returns>遷移構造器</returns>
public static MigrationBuilder DropEntityQueryView(
this MigrationBuilder migrationBuilder,
string tableName,
IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate dependencyLogicallyDeletableEntityViewSqlTemplate,
ITreeEntityDatabaseViewSqlTemplate treeEntityViewSqlTemplate)
{
ArgumentNullException.ThrowIfNull(migrationBuilder);
ArgumentNullException.ThrowIfNull(dependencyLogicallyDeletableEntityViewSqlTemplate);
ArgumentNullException.ThrowIfNull(treeEntityViewSqlTemplate);
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentException($"“{nameof(tableName)}”不能為 null 或空。", nameof(tableName));
}
migrationBuilder.DropDependencyLogicallyDeletableEntityQueryView(tableName, dependencyLogicallyDeletableEntityViewSqlTemplate);
migrationBuilder.DropTreeEntityQueryView(tableName, treeEntityViewSqlTemplate);
return migrationBuilder;
}
}
遷移腳本預覽（節選）
CREATE VIEW QueryView_Tree_Entity2_1s --創建視圖
AS
WITH [temp]([Id], [DeletedAt], [DependencyDeletedAt], [Entity1_1_1Id], [Entity2Id], [Entity2_0Id], [IsLeaf], [IsRoot], [ParentId], [Text2_1], [Depth], [Path], [HasChildren]) AS
(
--初始查詢（這裡的 [ParentId] IS NULL 在數據中是最底層的根節點）
SELECT [Id], [DeletedAt], [DependencyDeletedAt], [Entity1_1_1Id], [Entity2Id], [Entity2_0Id], [IsLeaf], [IsRoot], [ParentId], [Text2_1],
0 AS [Depth],
'/' + CAST([Id] AS nvarchar(max)) + '/' AS [Path], --如果Id使用Guid類型，可能會導致層數太深時出問題（大概100層左右，超過4000字之後的字元串會被砍掉,sqlserver 2005以後用 nvarchar(max)可以突破限制），Guid的字數太多了
(CASE WHEN EXISTS(SELECT 1 FROM [QueryView_Entity2_1s] WHERE [QueryView_Entity2_1s].[ParentId] = [Root].[Id]) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END) AS [HasChildren]
FROM [QueryView_Entity2_1s] AS [Root]
WHERE [Root].[ParentId] IS NULL
UNION ALL
--遞歸條件
SELECT [Child].[Id], [Child].[DeletedAt], [Child].[DependencyDeletedAt], [Child].[Entity1_1_1Id], [Child].[Entity2Id], [Child].[Entity2_0Id], [Child].[IsLeaf], [Child].[IsRoot], [Child].[ParentId], [Child].[Text2_1],
[Parent].[Depth] + 1,
[Parent].[Path] + CAST([Child].[Id] AS nvarchar(max)) + '/' AS [Path],
(CASE WHEN EXISTS(SELECT 1 FROM [QueryView_Entity2_1s] WHERE [QueryView_Entity2_1s].[ParentId] = [Child].[Id]) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END) AS [HasChildren]
FROM [QueryView_Entity2_1s] AS [Child] --3：這裡的臨時表和原始數據表都必須使用別名不然遞歸的時候不知道查詢的是哪個表的列
INNER JOIN [temp] AS [Parent]
ON ([Child].[ParentId] = [Parent].[Id]) --這個關聯關係很重要，一定要理解一下誰是誰的父節點
)
--4：遞歸完成後 一定不要少了這句查詢語句 否則會報錯
SELECT *
FROM [temp];
MySql 8.0和Sqlite 3支持查詢所需功能，其他資料庫請自行驗證。
Tips
開發測試時發現，如果用命令行工具會導致無法下斷點單步調試遷移擴展，這一度讓筆者很難受。經過一番折騰，發現可以使用以下代碼在程式中調用遷移生成。
var modelInitializer = appDbContext.GetService<IModelRuntimeInitializer>();
var migrationsAssembly = appDbContext.GetService<IMigrationsAssembly>();
var modelDiffer = appDbContext.GetService<IMigrationsModelDiffer>();
var migrator = appDbContext.GetService<IMigrator>();
var firstModel = modelInitializer.Initialize(migrationsAssembly.CreateMigration(migrationsAssembly.Migrations.First().Value, appDbContext.Database.ProviderName!).TargetModel);
var snapshotModel = modelInitializer.Initialize(migrationsAssembly.ModelSnapshot!.Model);
var differences = modelDiffer.GetDifferences(
migrationsAssembly.ModelSnapshot!.Model.GetRelationalModel(),
firstModel.GetRelationalModel());
var script = migrator.GenerateScript(migrationsAssembly.Migrations.LastOrDefault().Key, "0"/*, migrationsAssembly.Migrations.FirstOrDefault().Key*/);
結語
經過3篇系列文，一個僅依賴EF Core，對業務代碼0入侵，完全確保數據完整性的全自動審計、軟刪除和樹形查詢表就大功告成了！
本系列文的所需代碼從構思到測試基本可用前後過了將近一個月，基本上可以說已經成為了項目這碟醋包了這個系列的一盤餃子了。包括之前的基於EF Core存儲的國際化服務和基於EF Core存儲的Serilog持久化服務其實也是項目的一部分。不過經過這一系列折騰，以後可以直接拿來用了，也不虧。
示例代碼：SoftDeleteDemo.rar。主頁顯示異常請在libman.json上右鍵恢復前端包。
QQ群
讀者交流QQ群：540719365
歡迎讀者和廣大朋友一起交流，如發現本書錯誤也歡迎通過博客園、QQ群等方式告知筆者。
本文地址：https://www.cnblogs.com/coredx/p/18305284.html
