写在开头

从最早期入门时的单表操作,

到后来接触了 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(在线)

为什么要重复造轮子?

.NET ORM 导航属性【到底】可以解决什么问题?-LMLPHP

  • 支持 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 会很泛滥越来越多。

.NET ORM 导航属性【到底】可以解决什么问题?-LMLPHP

于是聪明的人类想到了导航属性,在 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; }
}

.NET ORM 导航属性【到底】可以解决什么问题?-LMLPHP

OneToMany 一对多

1对N,和 N对1 是反过来看

topic 相对于 type 是 N对1

type 相对于 topic 是 1对N

.NET ORM 导航属性【到底】可以解决什么问题?-LMLPHP

所以,我们在 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')

.NET ORM 导航属性【到底】可以解决什么问题?-LMLPHP

解释:方法一 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 文档中了解。

.NET ORM 导航属性【到底】可以解决什么问题?-LMLPHP

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: [ 标签列表 ]
}
...
]

查询 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 表已存在的数据,计算出添加、修改、删除执行。

.NET ORM 导航属性【到底】可以解决什么问题?-LMLPHP

父子关系

父子关系,其实是 ManyToOne、OneToMany 的综合体,自己指向自己,常用于树形结构表设计。

父子关系,除了能使用 ManyToOne、OneToMany 的使用方法外,还提供了 CTE递归查询、内存递归组装数据 功能。

.NET ORM 导航属性【到底】可以解决什么问题?-LMLPHP

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 正是解决递归查询的封装,方法参数说明:

姿势一: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"

.NET ORM 导航属性【到底】可以解决什么问题?-LMLPHP

总结

微软制造了优秀的语言 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 实现想法,欢迎给作者留言讨论,谢谢观看!

08-28 23:54