关于mysql: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<Topic>().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 正是解决递归查问的封装，办法参数阐明：

通过测试的数据库：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 实现想法，欢送给作者留言探讨，谢谢观看！