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 的子集合查詢；

系列文章導航

• （一）什麼是倉儲
• （二）如何使用倉儲
• （三）實體特性
• （四）工作單元
• （五）狀态管理
• （六）導航屬性
• （七）多表查詢
• （八）級聯加載
• （九）級聯儲存
• （十）動态實體類型
• （十一）分表
• （十二）如何擴充