乐趣区

关于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 正是解决递归查问的封装,办法参数阐明:

参数 形容
(可选) 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 实现想法,欢送给作者留言探讨,谢谢观看!

退出移动版