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

寫在開頭

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

到後來接觸了 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: [ 文章清單 ]
}
...
]
           

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 實作想法，歡迎給作者留言讨論，謝謝觀看！