Loading

FreeSql.Repository (七)多表查询

欢迎来到《FreeSql.Repository 仓储模式》系列文档,本系列文档专注介绍 【仓储+工作单元】 的使用方式。完整文档请前往 wiki 中心:https://github.com/dotnetcore/FreeSql/wiki

上文说到,仓储模式推荐使用导航属性,本文将讲解配置导航属性之后的多表查询。最终搞定单表、多表大部分使用场景。

联表查询

仓储本身功能支持不限数量、不限深级的导航属性多表查询,以下用两个表举例:

class Topic
{
    [Column(IsIdentity = true)]
    public int id { get; set; }

    public int typeid { get; set; }
    [Navigate(nameof(typeid))]
    public Type Type { get; set; }
}
class Type
{
    [Column(IsIdentity = true)]
    public int id { get; set; }
    public string name { get; set; }
}

var repo = fsql.GetRepository<Topic>();
var topics = repo.Select.Include(a => a.Type).ToList();
string typeName = topics[0].Type.Name;

执行的SQL:

select
topic.*, type.name
from topic
inner join type on type.id = topic.typeid

思考:ToList 默认返回 topic.* 和 type.* 不对,因为当 Topic 下面的导航属性有很多的时候,每次都返回所有导航属性?

于是:ToList 的时候只会返回 Include 过的,或者使用过的 N对1 导航属性字段。

  • repo.Select.ToList(); 返回 topic.*
  • repo.Select.Include(a => a.Type).ToList(); 返回 topic.* 和 type.*
  • repo.Where(a => a.Type.name == "c#").ToList(); 返回 topic.* 和 type.*,此时不需要显式使用 Include(a => a.Type)
  • repo.Select.ToList(a => new { Topic = a, TypeName = a.Type.name }); 返回 topic.* 和 type.name

各种复杂的 N对1 很好查询,比如这样:

var repo = fsql.GetRepository<Tag>();
var tags = repo.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 方便多了?

子查询

子查询主要针对 OneToMany、ManyToMany 的导航属性查询,如下定义:

class Topic
{
    [Column(IsIdentity = true)]
    public int id { get; set; }
    public int typeid { get; set; }
    public int clicks { get; set; }
}
class Type
{
    [Column(IsIdentity = true)]
    public int id { get; set; }
    public string name { get; set; }

    [Navigate(nameof(Topic.typeid))]
    public List<Topic> Topics { get; set; }
}

var repo = fsql.GetRepository<Type>();

1、子表Exists

var types = repo
    .Where(a => a.Topics.AsSelect().Any(b => b.id > 100))
    .ToList();
//SELECT a.`id`, a.`name`, a.`clicks` 
//FROM `Type` a
//WHERE (exists(SELECT 1
//    FROM `Topic` b
//    WHERE (b.`id` > 100)
//    limit 0,1))

2、子表In

var types = repo
    .Where(a => a.Topics.AsSelect().Where(b => b.id > 100).ToList(b => b.id).Contains(a.id))
    .ToList();
//SELECT a.`id`, a.`name`, a.`clicks` 
//FROM `Type` a
//WHERE ((a.`id`) in (SELECT b.`id`
//    FROM `Topic` b
//    WHERE (b.`id` > 100)))

3、子表Join

string.Join + ToList 实现将子查询的多行结果,拼接为一个字符串,如:"1,2,3,4"

var types = repo.ToList(a => new {
    id = a.id,
    concat = string.Join(",", a.Topics.AsSelect().ToList(b => b.id))
});
//SELECT a.`id`, (SELECT group_concat(b.`id` separator ',') 
//    FROM `Topic` b) 
//FROM `Type` a

4、子表First/Count/Sum/Max/Min/Avg

var types = repo.ToList(a => new  {
    all = a,
    first = a.Topics.AsSelect().First(b => b.id),
    count = a.Topics.AsSelect().Count(),
    sum = a.Topics.AsSelect().Sum(b => b.clicks),
    max = a.Topics.AsSelect().Max(b => b.clicks),
    min = a.Topics.AsSelect().Min(b => b.clicks),
    avg = a.Topics.AsSelect().Avg(b => b.clicks)
});

5、WhereCascade

多表查询时,像isdeleted每个表都给条件,挺麻烦的。WhereCascade使用后生成sql时,所有表都附上这个条件。

如:

var repo = fsql.GetRepository<T1>();
repo.Select.Include(a => a.T2)
  .WhereCascade(a => a.IsDeleted == false)
  .ToList();

得到的 SQL:

SELECT ...
FROM t1
LEFT JOIN t2 on ... AND (t2.IsDeleted = 0) 
WHERE t1.IsDeleted = 0

实体可附加表达式时才生效,支持子表查询。单次查询使用的表数目越多收益越大。

可应用范围:

  • 子查询,一对多、多对多、自定义的子查询;
  • Join 查询,导航属性、自定义的Join查询;
  • Include/IncludeMany 的子集合查询;

系列文章导航

posted @ 2020-12-11 12:10  FreeSql  阅读(1080)  评论(0编辑  收藏  举报