寫在開頭
從最早期入門時的單表操作,
到後來接觸了 left join、right join、inner join 查詢,
因為經費有限,需要不斷在多表查詢中折騰解決實際需求,不知道是否有過這樣的經歷?
本文從實際開發需求講解導航屬性(ManyToOne、OneToMany、ManyToMany)的設計思路,和到底解決了什麼問題。提示:以下示例程式碼使用了 FreeSql 語法,和一些虛擬碼。
入戲準備
FreeSql 是 .Net ORM,能支援 .NetFramework4.0+、.NetCore、Xamarin、XAUI、Blazor、以及還有說不出來的執行平臺,因為程式碼綠色無依賴,支援新平臺非常簡單。目前單元測試數量:5000+,Nuget下載數量:180K+,原始碼幾乎每天都有提交。值得高興的是 FreeSql 加入了 ncc 開源社群:https://github.com/dotnetcore/FreeSql,加入組織之後社群責任感更大,需要更努力做好品質,為開源社群出一份力。
QQ群:4336577(已滿)、8578575(線上)、52508226(線上)
為什麼要重複造輪子?
FreeSql 主要優勢在於易用性上,基本是開箱即用,在不同資料庫之間切換相容性比較好。作者花了大量的時間精力在這個專案,肯請您花半小時瞭解下專案,謝謝。功能特性如下:
- 支援 CodeFirst 對比結構變化遷移;
- 支援 DbFirst 從資料庫匯入實體類;
- 支援 豐富的表示式函式,自定義解析;
- 支援 批量新增、批量更新、BulkCopy;
- 支援 導航屬性,貪婪載入、延時載入、級聯儲存;
- 支援 讀寫分離、分表分庫,租戶設計;
- 支援 MySql/SqlServer/PostgreSQL/Oracle/Sqlite/達夢/神通/人大金倉/MsAccess;
FreeSql 使用非常簡單,只需要定義一個 IFreeSql 物件即可:
static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, connectionString)
.UseAutoSyncStructure(true) //自動同步實體結構到資料庫
.Build(); //請務必定義成 Singleton 單例模式
ManyToOne 多對一
left join、right join、inner join 從表的外來鍵看來,主要是針對一對一、多對一的查詢,比如 Topic、Type 兩個表,一個 Topic 只能屬於一個 Type:
select
topic.*, type.name
from topic
inner join type on type.id = topic.typeid
查詢 topic 把 type.name 一起返回,一個 type 可以對應 N 個 topic,對於 topic 來講是 N對1,所以我命名為 ManyToOne
在 c# 中使用實體查詢的時候,N對1 場景查詢容易,但是接收物件不方便,如下:
fsql.Select<Topic, Type>()
.LeftJoin((a,b) => a.typeid == b.Id)
.ToList((a,b) => new { a, b })
這樣只能返回匿名型別,除非自己再去建一個 TopicDto,但是查詢場景真的太多了,幾乎無法窮舉 TopicDto,隨著需求的變化,後面這個 Dto 會很氾濫越來越多。
於是聰明的人類想到了導航屬性,在 Topic 實體內增加 Type 屬性接收返回的資料。
fsql.Select<Topic>()
.LeftJoin((a,b) => a.Type.id == a.typeid)
.ToList();
返回資料後,可以使用 [0].Type.name 得到分類名稱。
經過一段時間的使用,發現 InnerJoin 的條件總是在重複編寫,每次都要用大腦回憶這個條件(論頭髮怎麼掉光的)。
進化一次之後,我們把 join 的條件做成了配置:
class Topic
{
public int typeid { get; set; }
[Navigate(nameof(typeid))]
public Type Type { get; set; }
}
class Type
{
public int id { get; set; }
public string name { get; set; }
}
查詢的時候變成了這樣:
fsql.Select<Topic>()
.Include(a => a.Type)
.ToList();
返回資料後,同樣可以使用 [0].Type.name 得到分類名稱。
-
[Navigate(nameof(typeid))] 理解成,Topic.typeid 與 Type.id 關聯,這裡省略了 Type.id 的配置,因為 Type.id 是主鍵(已知條件無須配置),從而達到簡化配置的效果
-
.Include(a => a.Type) 查詢的時候會自動轉化為:.LeftJoin(a => a.Type.id == a.typeid)
思考:ToList 預設返回 topic.* 和 type.* 不對,因為當 Topic 下面的導航屬性有很多的時候,每次都返回所有導航屬性?
於是:ToList 的時候只會返回 Include 過的,或者使用過的 N對1 導航屬性欄位。
-
fsql.Select<Topic>().ToList(); 返回 topic.*
-
fsql.Select<Topic>().Include(a => a.Type).ToList(); 返回 topic.* 和 type.*
-
fsql.Select<Topic>().Where(a => a.Type.name == "c#").ToList(); 返回 topic.* 和 type.*,此時不需要顯式使用 Include(a => a.Type)
-
fsql.Select
().ToList(a => new { Topic = a, TypeName = a.Type.name }); 返回 topic.* 和 type.name
有了這些機制,各種複雜的 N對1,就很好查詢了,比如這樣的查詢:
fsql.Select<Tag>().Where(a => a.Parent.Parent.name == "粵語").ToList();
//該程式碼產生三個 tag 表 left join 查詢。
class Tag {
public int id { get; set; }
public string name { get; set; }
public int? parentid { get; set; }
public Tag Parent { get; set; }
}
是不是比自己使用 left join/inner join/right join 方便多了?
OneToOne 一對一
一對一 和 N對1 解決目的是一樣的,都是為了簡化多表 join 查詢。
比如 order, order_detail 兩個表,一對一場景:
fsql.Select<order>().Include(a => a.detail).ToList();
fsql.Select<order_detail>().Include(a => a.order).ToList();
查詢的資料一樣的,只是返回的 c# 型別不一樣。
一對一,只是配置上有點不同,使用方式跟 N對1 一樣。
一對一,要求兩邊都存在目標實體屬性,並且兩邊都是使用主鍵做 Navigate。
class order
{
public int id { get; set; }
[Navigate(nameof(id))]
public order_detail detail { get; set; }
}
class order_detail
{
public int orderid { get; set; }
[Navigate(nameof(orderid))]
public order order { get; set; }
}
OneToMany 一對多
1對N,和 N對1 是反過來看
topic 相對於 type 是 N對1
type 相對於 topic 是 1對N
所以,我們在 Type 實體類中可以定義 List<Topic> Topics { get; set; } 導航屬性
class Type
{
public int id { get; set; }
public List<Topic> Topics { get; set; }
}
1對N 導航屬性的主要優勢:
- 查詢 Type 的時候可以把 topic 一起查詢出來,並且還是用 Type 作為返回型別。
- 新增 Type 的時候,把 Topics 一起新增
- 更新 Type 的時候,把 Topics 一起更新
- 刪除 Type 的時候,沒動作( ef 那邊是用資料庫外來鍵功能刪除子表記錄的)
OneToMany 級聯查詢
把 Type.name 為 c# java php,以及它們的 topic 查詢出來:
方法一:
fsql.Select<Type>()
.IncludeMany(a => a.Topics)
.Where(a => new { "c#", "java", "php" }.Contains(a.name))
.ToList();
[
{
name : "c#",
Topics: [ 文章列表 ]
}
...
]
這種方法是從 Type 方向查詢的,非常符合使用方的資料格式要求。
最終是分兩次 SQL 查詢資料回來的,大概是:
select * from type where name in ('c#', 'java', 'php')
select * from topics where typeid in (上一條SQL返回的id)
方法二:從 Topic 方向也可以查詢出來:
fsql.Select<Topic>()
.Where(a => new { "c#", "java", "php" }.Contains(a.Type.name)
.ToList();
一次 SQL 查詢返回所有資料的,大概是:
select * from topic
left join type on type.id = topic.typeid
where type.name in ('c#', 'java', 'php')
解釋:方法一 IncludeMany 雖然是分開兩次查詢的,但是 IO 效能遠高於 方法二。方法二查詢簡單資料還行,複雜一點很容易產生大量重複 IO 資料。並且方法二返回的資料結構 List<Topic>,一般不符合使用方要求。
IncludeMany 第二次查詢 topic 的時候,如何把記錄分配到 c# java php 對應的 Type.Topics 中?
所以這個時候,配置一下導航關係就行了。
N對1,這樣配置的(從自己身上找一個欄位,與目標型別主鍵關聯):
class Topic
{
public int typeid { get; set; }
[Navigate(nameof(typeid))]
public Type Type { get; set; }
}
1對N,這樣配置的(從目標型別上找欄位,與自己的主鍵關聯):
class Type
{
public int id { get; set; }
[Navigate(nameof(Topic.typeid))]
public List<Topic> Topics { get; set; }
}
舉一反三:
IncludeMany 級聯查詢,在實際開發中,還可以 IncludeMany(a => a.Topics, then => then.IncludeMany(b => b.Comments))
假設,還需要把 topic 對應的 comments 也查詢出來。最多會產生三條SQL查詢:
select * from type where name in ('c#', 'java', 'php')
select * from topic where typeid in (上一條SQL返回的id)
select * from comment where topicid in (上一條SQL返回的id)
思考:這樣級聯查詢其實是有缺點的,比如 c# 下面有1000篇文章,那不是都返回了?
IncludeMany(a => a.Topics.Take(10))
這樣就能解決每個分類只返回 10 條資料了,這個功能 ef/efcore 目前做不到,直到 efcore 5.0 才支援,這可能是很多人忌諱 ef 導航屬性的原因之一吧。幾個月前我測試了 efcore 5.0 sqlite 該功能是報錯的,也許只支援 sqlserver。而 FreeSql 沒有資料庫種類限制,還是那句話:都是親兒子!
關於 IncludeMany 還有更多功能請到 github wiki 文件中瞭解。
OneToMany 級聯儲存
實踐中發現,N對1 不適合做級聯儲存。儲存 Topic 的時候把 Type 資訊也儲存?我個人認為自下向上儲存的功能太不可控了,FreeSql 目前不支援自下向上儲存。
FreeSql 支援的級聯儲存,是自上向下。例如儲存 Type 的時候,也同時能儲存他的 Topic。
級聯儲存,建議用在不太重要的功能,或者測試資料新增:
var repo = fsql.GetRepository<Type>();
repo.DbContextOptions.EnableAddOrUpdateNavigateList = true;
repo.DbContextOptions.NoneParameter = true;
repo.Insert(new Type
{
name = "c#",
Topics = new List<Topic>(new[] {
new Topic
{
...
}
})
});
先新增 Type,如果他是自增,拿到自增值,向下賦給 Topics 再插入 topic。
ManyToMany 多對多
多對多是很常見的一種設計,如:Topic, Tag, TopicTag
class Topic
{
public int id { get; set; }
public string title { get; set; }
[Navigate(ManyToMany = typeof(TopicTag))]
public List<Tag> Tags { get; set; }
}
public Tag
{
public int id { get; set; }
public string name { get; set; }
[Navigate(ManyToMany = typeof(TopicTag))]
public List<Topic> Topics { get; set; }
}
public TopicTag
{
public int topicid { get; set; }
public int tagid { get; set; }
[Navigate(nameof(topicid))]
public Topic Topic { get; set; }
[Navigate(nameof(tagid))]
public Tag Tag { get; set; }
}
看著覺得複雜??看完後面查詢多麼簡單的時候,真的什麼都值了!
N對N 導航屬性的主要優勢:
- 查詢 Topic 的時候可以把 Tag 一起查詢出來,並且還是用 Topic 作為返回型別。
- 新增 Topic 的時候,把 Tags 一起新增
- 更新 Topic 的時候,把 Tags 一起更新
- 刪除 Topic 的時候,沒動作( ef 那邊是用資料庫外來鍵功能刪除子表記錄的)
ManyToMany 級聯查詢
把 Tag.name 為 c# java php,以及它們的 topic 查詢出來:
fsql.Select<Tag>()
.IncludeMany(a => a.Topics)
.Where(a => new { "c#", "java", "php" }.Contains(a.name))
.ToList();
[
{
name : "c#",
Topics: [ 文章列表 ]
}
...
]
最終是分兩次 SQL 查詢資料回來的,大概是:
select * from tag where name in ('c#', 'java', 'php')
select * from topic where id in (select topicid from topictag where tagid in(上一條SQL返回的id))
如果 Tag.name = "c#" 下面的 Topic 記錄太多,只想返回 top 10:
.IncludeMany(a => a.Topics.Take(10))
也可以反過來查,把 Topic.Type.name 為 c# java php 的 topic,以及它們的 Tag 查詢出來:
fsql.Select<Topic>()
.IncludeMany(a => a.Tags)
.Where(a => new { "c#", "java", "php" }.Contains(a.Type.name))
.ToList();
[
{
title : "FreeSql 1.8.1 正式釋出",
Type: { name: "c#" }
Tags: [ 標籤列表 ]
}
...
]
N對N 級聯查詢,跟 1對N 一樣,都是用 IncludeMany,N對N IncludeMany 也可以繼續向下 then。
查詢 Tag.name = "c#" 的所有 topic:
fsql.Select<Topic>()
.Where(a => a.Tags.AsSelect().Any(b => b.name = "c#"))
.ToList();
產生的 SQL 大概是這樣的:
select * from topic
where id in (
select topicid from topictag
where tagid in ( select id from tag where name = 'c#' )
)
ManyToMany 級聯儲存
級聯儲存,建議用在不太重要的功能,或者測試資料新增:
var repo = fsql.GetRepository<Topic>();
repo.DbContextOptions.EnableAddOrUpdateNavigateList = true;
repo.DbContextOptions.NoneParameter = true;
repo.Insert(new Topic
{
title = "FreeSql 1.8.1 正式釋出",
Tags = new List<Tag>(new[] {
new Tag { name = "c#" }
})
});
插入 topic,再判斷 Tag 是否存在(如果不存在則插入 tag)。
得到 topic.id 和 tag.id 再插入 TopicTag。
另外提供的方法 repo.SaveMany(topic實體, "Tags") 完整儲存 TopicTag 資料。比如當 topic實體.Tags 屬性為 Empty 時,刪除 topic實體 存在於 TopicTag 所有表資料。
SaveMany機制:完整儲存,對比 TopicTag 表已存在的資料,計算出新增、修改、刪除執行。
父子關係
父子關係,其實是 ManyToOne、OneToMany 的綜合體,自己指向自己,常用於樹形結構表設計。
父子關係,除了能使用 ManyToOne、OneToMany 的使用方法外,還提供了 CTE遞迴查詢、記憶體遞迴組裝資料 功能。
public class Area
{
[Column(IsPrimary = true)]
public string Code { get; set; }
public string Name { get; set; }
public string ParentCode { get; set; }
[Navigate(nameof(ParentCode))]
public Area Parent { get; set; }
[Navigate(nameof(ParentCode))]
public List<Area> Childs { get; set; }
}
var repo = fsql.GetRepository<Area>();
repo.DbContextOptions.EnableAddOrUpdateNavigateList = true;
repo.DbContextOptions.NoneParameter = true;
repo.Insert(new Area
{
Code = "100000",
Name = "中國",
Childs = new List<Area>(new[] {
new Area
{
Code = "110000",
Name = "北京",
Childs = new List<Area>(new[] {
new Area{ Code="110100", Name = "北京市" },
new Area{ Code="110101", Name = "東城區" },
})
}
})
});
遞迴資料
配置好父子屬性之後,就可以這樣用了:
var t1 = fsql.Select<Area>().ToTreeList();
Assert.Single(t1);
Assert.Equal("100000", t1[0].Code);
Assert.Single(t1[0].Childs);
Assert.Equal("110000", t1[0].Childs[0].Code);
Assert.Equal(2, t1[0].Childs[0].Childs.Count);
Assert.Equal("110100", t1[0].Childs[0].Childs[0].Code);
Assert.Equal("110101", t1[0].Childs[0].Childs[1].Code);
查詢資料本來是平面的,ToTreeList 方法將返回的平面資料在記憶體中加工為樹型 List 返回。
CTE遞迴刪除
很常見的無限級分類表功能,刪除樹節點時,把子節點也處理一下。
fsql.Select<Area>()
.Where(a => a.Name == "中國")
.AsTreeCte()
.ToDelete()
.ExecuteAffrows(); //刪除 中國 下的所有記錄
如果軟刪除:
fsql.Select<Area>()
.Where(a => a.Name == "中國")
.AsTreeCte()
.ToUpdate()
.Set(a => a.IsDeleted, true)
.ExecuteAffrows(); //軟刪除 中國 下的所有記錄
CTE遞迴查詢
若不做資料冗餘的無限級分類表設計,遞迴查詢少不了,AsTreeCte 正是解決遞迴查詢的封裝,方法引數說明:
引數 | 描述 |
---|---|
(可選) pathSelector | 路徑內容選擇,可以設定查詢返回:中國 -> 北京 -> 東城區 |
(可選) up | false(預設):由父級向子級的遞迴查詢,true:由子級向父級的遞迴查詢 |
(可選) pathSeparator | 設定 pathSelector 的連線符,預設:-> |
(可選) level | 設定遞迴層級 |
通過測試的資料庫:MySql8.0、SqlServer、PostgreSQL、Oracle、Sqlite、達夢、人大金倉
姿勢一:AsTreeCte() + ToTreeList
var t2 = fsql.Select<Area>()
.Where(a => a.Name == "中國")
.AsTreeCte() //查詢 中國 下的所有記錄
.OrderBy(a => a.Code)
.ToTreeList(); //非必須,也可以使用 ToList(見姿勢二)
Assert.Single(t2);
Assert.Equal("100000", t2[0].Code);
Assert.Single(t2[0].Childs);
Assert.Equal("110000", t2[0].Childs[0].Code);
Assert.Equal(2, t2[0].Childs[0].Childs.Count);
Assert.Equal("110100", t2[0].Childs[0].Childs[0].Code);
Assert.Equal("110101", t2[0].Childs[0].Childs[1].Code);
// WITH "as_tree_cte"
// as
// (
// SELECT 0 as cte_level, a."Code", a."Name", a."ParentCode"
// FROM "Area" a
// WHERE (a."Name" = '中國')
// union all
// SELECT wct1.cte_level + 1 as cte_level, wct2."Code", wct2."Name", wct2."ParentCode"
// FROM "as_tree_cte" wct1
// INNER JOIN "Area" wct2 ON wct2."ParentCode" = wct1."Code"
// )
// SELECT a."Code", a."Name", a."ParentCode"
// FROM "as_tree_cte" a
// ORDER BY a."Code"
姿勢二:AsTreeCte() + ToList
var t3 = fsql.Select<Area>()
.Where(a => a.Name == "中國")
.AsTreeCte()
.OrderBy(a => a.Code)
.ToList();
Assert.Equal(4, t3.Count);
Assert.Equal("100000", t3[0].Code);
Assert.Equal("110000", t3[1].Code);
Assert.Equal("110100", t3[2].Code);
Assert.Equal("110101", t3[3].Code);
//執行的 SQL 與姿勢一相同
姿勢三:AsTreeCte(pathSelector) + ToList
設定 pathSelector 引數後,如何返回隱藏欄位?
var t4 = fsql.Select<Area>()
.Where(a => a.Name == "中國")
.AsTreeCte(a => a.Name + "[" + a.Code + "]")
.OrderBy(a => a.Code)
.ToList(a => new {
item = a,
level = Convert.ToInt32("a.cte_level"),
path = "a.cte_path"
});
Assert.Equal(4, t4.Count);
Assert.Equal("100000", t4[0].item.Code);
Assert.Equal("110000", t4[1].item.Code);
Assert.Equal("110100", t4[2].item.Code);
Assert.Equal("110101", t4[3].item.Code);
Assert.Equal("中國[100000]", t4[0].path);
Assert.Equal("中國[100000] -> 北京[110000]", t4[1].path);
Assert.Equal("中國[100000] -> 北京[110000] -> 北京市[110100]", t4[2].path);
Assert.Equal("中國[100000] -> 北京[110000] -> 東城區[110101]", t4[3].path);
// WITH "as_tree_cte"
// as
// (
// SELECT 0 as cte_level, a."Name" || '[' || a."Code" || ']' as cte_path, a."Code", a."Name", a."ParentCode"
// FROM "Area" a
// WHERE (a."Name" = '中國')
// union all
// SELECT wct1.cte_level + 1 as cte_level, wct1.cte_path || ' -> ' || wct2."Name" || '[' || wct2."Code" || ']' as cte_path, wct2."Code", wct2."Name", wct2."ParentCode"
// FROM "as_tree_cte" wct1
// INNER JOIN "Area" wct2 ON wct2."ParentCode" = wct1."Code"
// )
// SELECT a."Code" as1, a."Name" as2, a."ParentCode" as5, a.cte_level as6, a.cte_path as7
// FROM "as_tree_cte" a
// ORDER BY a."Code"
總結
微軟製造了優秀的語言 c#,利用語言特性可以做一些非常好用的功能,在 ORM 中使用導航屬性非常適合。
-
ManyToOne(N對1) 提供了簡單的多表 join 查詢;
-
OneToMany(1對N) 提供了簡單可控的級聯查詢、級聯儲存功能;
-
ManyToMany(多對多) 提供了簡單的多對多過濾查詢、級聯查詢、級聯儲存功能;
-
父子關係 提供了常用的 CTE查詢、刪除、遞迴功能;
希望正在使用的、善良的您能動一動小手指,把文章轉發一下,讓更多人知道 .NET 有這樣一個好用的 ORM 存在。謝謝了!!
FreeSql 開源協議 MIT https://github.com/dotnetcore/FreeSql,可以商用,文件齊全。QQ群:4336577(已滿)、8578575(線上)、52508226(線上)
如果你有好的 ORM 實現想法,歡迎給作者留言討論,謝謝觀看!