.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: [ 文章列表 ]
}
...
]

最终是分两次 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 实现想法，欢迎给作者留言讨论，谢谢观看！

posted @ 2020-08-28 03:53 nicye 阅读(7426) 评论(21) 编辑收藏举报

刷新页面返回顶部

FreeSql & CSRedis

为 .NET 开源事业添砖加瓦，https://github.com/2881099