.NET ORM 導航屬性【到底】可以解決什麼問題?

nicye發表於2020-08-28

寫在開頭

從最早期入門時的單表操作,

到後來接觸了 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 再插入 topics。


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實體 存在的 Tag 所有表資料。

SaveMany機制:完整儲存,對比 TopicTag 表已存在的資料,計算出新增、修改、刪除執行。

父子關係

父子關係,其實是 ManyToOne、OneToMany 的綜合體,自己指向自己,常用於樹形結構表設計。

父子關係,除了能使用 ManyToOne、OneToMany 的使用方法外,還提供了 CTE遞迴查詢、記憶體遞迴組裝資料 功能。

public class Area
{
  [Column(IsPrimary = true)]
  public string Code { get; set; }

  public string Name { get; set; }
  public virtual 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 實現想法,歡迎給作者留言討論,謝謝觀看!

相關文章