Skip to content

Latest commit

 

History

History
259 lines (223 loc) · 12.4 KB

select.md

File metadata and controls

259 lines (223 loc) · 12.4 KB

查询数据

测试代码

IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=10")
    .Build();
ISelect<Topic> select => fsql.Select<Topic>();

[Table(Name = "tb_topic")]
class Topic {
    [Column(IsIdentity = true, IsPrimary = true)]
    public int Id { get; set; }
    public int Clicks { get; set; }
    public int TestTypeInfoGuid { get; set; }
    public TestTypeInfo Type { get; set; }
    public string Title { get; set; }
    public DateTime CreateTime { get; set; }
}
class TestTypeInfo {
    public int Guid { get; set; }
    public int ParentId { get; set; }
    public TestTypeParentInfo Parent { get; set; }
    public string Name { get; set; }
}
class TestTypeParentInfo {
    public int Id { get; set; }
    public string Name { get; set; }

    public List<TestTypeInfo> Types { get; set; }
}

Where

单表

var sql = select.Where(a => a.Id == 10).ToSql();
///SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, a.`Title`, a.`CreateTime` FROM `tb_topic` a WHERE (a.`Id` = 10)

sql = select.Where(a => a.Id == 10 && a.Id > 10 || a.Clicks > 100).ToSql();
///SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, a.`Title`, a.`CreateTime` FROM `tb_topic` a WHERE (a.`Id` = 10 AND a.`Id` > 10 OR a.`Clicks` > 100)

sql = select.Where(a => new []{1,2,3}.Contains(a.Id)).ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, a.`Title`, a.`CreateTime` FROM `tb_topic` a WHERE (a.`Id` in (1,2,3))

《Expression 表达式函数文档》

多表，使用导航属性

sql = select.Where(a => a.Type.Name == "typeTitle" && a.Type.Guid == a.TestTypeInfoGuid).ToSql();
///SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, a__Type.`Guid`, a__Type.`ParentId`, a__Type.`Name`, a.`Title`, a.`CreateTime` FROM `tb_topic` a, `TestTypeInfo` a__Type WHERE (a__Type.`Name` = 'typeTitle' AND a__Type.`Guid` = a.`TestTypeInfoGuid`)

sql = select.Where(a => a.Type.Parent.Name == "tparent").ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, a__Type.`Guid`, a__Type.`ParentId`, a__Type.`Name`, a.`Title`, a.`CreateTime` FROM `tb_topic` a, `TestTypeInfo` a__Type, `TestTypeParentInfo` a__Type__Parent WHERE (a__Type__Parent.`Name` = 'tparent')

多表，没有导航属性

sql = select.Where<TestTypeInfo>((a, b) => b.Guid == a.TestTypeInfoGuid && b.Name == "typeTitle").ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, b.`Guid`, b.`ParentId`, b.`Name`, a.`Title`, a.`CreateTime` FROM `tb_topic` a, `TestTypeInfo` b WHERE (b.`Guid` = a.`TestTypeInfoGuid` AND b.`Name` = 'typeTitle')

sql = select.Where<TestTypeInfo, TestTypeParentInfo>((a, b, c) => c.Name == "tparent").ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, a.`Title`, a.`CreateTime` FROM `tb_topic` a, `TestTypeParentInfo` c WHERE (c.`Name` = 'tparent')

多表，任意查

sql = select.From<TestTypeInfo, TestTypeParentInfo>((s, b, c) => s
    .Where(a => a.Id == 10 && c.Name == "xxx")
    .Where(a => b.ParentId == 20)).ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, b.`Guid`, b.`ParentId`, b.`Name`, a.`Title`, a.`CreateTime` FROM `tb_topic` a, `TestTypeParentInfo` c, `TestTypeInfo` b WHERE (a.`Id` = 10 AND c.`Name` = 'xxx') AND (b.`ParentId` = 20)

子表 Exists 查询

var sql2222 = select.Where(a => select.Where(b => b.Id == a.Id).Any()).ToList();
// SELECT a.`Id`, a.`TypeGuid`, a.`Title`, a.`CreateTime` 
// FROM `xxx` a 
// WHERE (exists(SELECT 1 
// FROM `xxx` b 
// WHERE (b.`Id` = a.`Id`)))

//两级相同的子表查询
sql2222 = select.Where(a =>
    select.Where(b => b.Id == a.Id && select.Where(c => c.Id == b.Id).Where(d => d.Id == a.Id).Where(e => e.Id == b.Id)
    .Offset(a.Id)
    .Any()
    ).Any()
).ToList();
// SELECT a.`Id`, a.`TypeGuid`, a.`Title`, a.`CreateTime` 
// FROM `xxx` a 
// WHERE (exists(SELECT 1 
// FROM `xxx` b 
// WHERE (b.`Id` = a.`Id` AND exists(SELECT 1 
// FROM `xxx` c 
// WHERE (c.`Id` = b.`Id`) AND (c.`Id` = a.`Id`) AND (c.`Id` = b.`Id`)
// limit 0,1))
// limit 0,1))

原生SQL

sql = select.Where("a.clicks > 100 && a.id = ?id", new { id = 10 }).ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, a.`Title`, a.`CreateTime` FROM `tb_topic` a WHERE (a.clicks > 100 && a.id = ?id)

以上条件查询，支持 WhereIf

联表

使用导航属性联表

sql = select.LeftJoin(a => a.Type.Guid == a.TestTypeInfoGuid).ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, a__Type.`Guid`, a__Type.`ParentId`, a__Type.`Name`, a.`Title`, a.`CreateTime` FROM `tb_topic` a LEFT JOIN `TestTypeInfo` a__Type ON a__Type.`Guid` = a.`TestTypeInfoGuid`

sql = select
    .LeftJoin(a => a.Type.Guid == a.TestTypeInfoGuid)
    .LeftJoin(a => a.Type.Parent.Id == a.Type.ParentId).ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, a__Type.`Guid`, a__Type.`ParentId`, a__Type.`Name`, a.`Title`, a.`CreateTime` FROM `tb_topic` a LEFT JOIN `TestTypeInfo` a__Type ON a__Type.`Guid` = a.`TestTypeInfoGuid` LEFT JOIN `TestTypeParentInfo` a__Type__Parent ON a__Type__Parent.`Id` = a__Type.`ParentId`

没有导航属性联表

sql = select.LeftJoin<TestTypeInfo>((a, b) => b.Guid == a.TestTypeInfoGuid).ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, b.`Guid`, b.`ParentId`, b.`Name`, a.`Title`, a.`CreateTime` FROM `tb_topic` a LEFT JOIN `TestTypeInfo` b ON b.`Guid` = a.`TestTypeInfoGuid`

sql = select
    .LeftJoin<TestTypeInfo>((a, b) => b.Guid == a.TestTypeInfoGuid)
    .LeftJoin<TestTypeParentInfo>((a, c) => c.Id == a.Type.ParentId).ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, b.`Guid`, b.`ParentId`, b.`Name`, a.`Title`, a.`CreateTime` FROM `tb_topic` a LEFT JOIN `TestTypeInfo` b ON b.`Guid` = a.`TestTypeInfoGuid` LEFT JOIN `TestTypeParentInfo` c ON c.`Id` = b.`ParentId`

联表任意查

sql = select.From<TestTypeInfo, TestTypeParentInfo>((s, b, c) => s
    .LeftJoin(a => a.TestTypeInfoGuid == b.Guid)
    .LeftJoin(a => b.ParentId == c.Id)).ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, b.`Guid`, b.`ParentId`, b.`Name`, a.`Title`, a.`CreateTime` FROM `tb_topic` a LEFT JOIN `TestTypeInfo` b ON a.`TestTypeInfoGuid` = b.`Guid` LEFT JOIN `TestTypeParentInfo` c ON b.`ParentId` = c.`Id`

原生SQL联表

sql = select.LeftJoin("TestTypeInfo b on b.Guid = a.TestTypeInfoGuid and b.Name = ?bname", new { bname = "xxx" }).ToSql();
//SELECT a.`Id`, a.`Clicks`, a.`TestTypeInfoGuid`, a.`Title`, a.`CreateTime` FROM `tb_topic` a LEFT JOIN TestTypeInfo b on b.Guid = a.TestTypeInfoGuid and b.Name = ?bname

查询数据

返回 List

List<Topic> t1 = select.Where(a => a.Id > 0).Skip(100).Limit(200).ToList();

返回 List + 导航属性的数据

List<Topic> t2 = select.LeftJoin(a => a.Type.Guid == a.TestTypeInfoGuid).ToList();
//此时会返回普通字段 + 导航对象 Type 的数据

指定字段返回

//返回一个字段
List<int> t3 = select.Where(a => a.Id > 0).Skip(100).Limit(200).ToList(a => a.Id);

//返回匿名类
List<匿名类> t4 = select.Where(a => a.Id > 0).Skip(100).Limit(200).ToList(a => new { a.Id, a.Title });

//返回元组
List<(int, string)> t5 = select.Where(a => a.Id > 0).Skip(100).Limit(200).ToList<(int, string)>("id, title");

//返回SQL字段
List<匿名类> t4 = select.Where(a => a.Id > 0).Skip(100).Limit(200)
    .ToList(a => new {
        a.Id,
        a.Title,
        cstitle = "substr(a.title, 0, 2)", //将 substr(a.title, 0, 2) 作为查询字段
        csnow = Convert.ToDateTime("now()"), //将 now() 作为查询字段
        //奇思妙想：怎么查询开窗函数的结果
    });

执行SQL返回数据

class xxx {
    public int Id { get; set; }
    public string Path { get; set; }
    public string Title2 { get; set; }
}

List<xxx> t6 = fsql.Ado.Query<xxx>("select * from song");
List<(int, string ,string)> t7 = fsql.Ado.Query<(int, string, string)>("select * from song");
List<dynamic> t8 = fsql.Ado.Query<dynamic>("select * from song");

分组聚合

var groupby = fsql.Select<Topic>()
    .GroupBy(a => new { tt2 = a.Title.Substring(0, 2), mod4 = a.Id % 4 })
    .Having(a => a.Count() > 0 && a.Avg(a.Key.mod4) > 0 && a.Max(a.Key.mod4) > 0)
    .Having(a => a.Count() < 300 || a.Avg(a.Key.mod4) < 100)
    .OrderBy(a => a.Key.tt2)
    .OrderByDescending(a => a.Count())
    .ToList(a => new { a.Key.tt2, cou1 = a.Count(), arg1 = a.Avg(a.Key.mod4) });
//SELECT substr(a.`Title`, 1, 2) as1, count(1) as2, avg((a.`Id` % 4)) as3 
//FROM `xxx` a 
//GROUP BY substr(a.`Title`, 1, 2), (a.`Id` % 4) 
//HAVING (count(1) > 0 AND avg((a.`Id` % 4)) > 0 AND max((a.`Id` % 4)) > 0) AND (count(1) < 300 OR avg((a.`Id` % 4)) < 100) 
//ORDER BY substr(a.`Title`, 1, 2), count(1) DESC

更多文档整理中。。。

方法 返回值 参数 描述
ToSql string 返回即将执行的SQL语句
ToList List 执行SQL查询，返回 T1 实体所有字段的记录，若存在导航属性则一起查询返回，记录不存在时返回 Count 为 0 的列表
ToList<T> List<T> Lambda 执行SQL查询，返回指定字段的记录，记录不存在时返回 Count 为 0 的列表
ToList<T> List<T> string field 执行SQL查询，返回 field 指定字段的记录，并以元组或基础类型(int,string,long)接收，记录不存在时返回 Count 为 0 的列表
ToOne T1 执行SQL查询，返回 T1 实体所有字段的第一条记录，记录不存在时返回 null
Any bool 执行SQL查询，是否有记录
Sum T Lambda 指定一个列求和
Min T Lambda 指定一个列求最小值
Max T Lambda 指定一个列求最大值
Avg T Lambda 指定一个列求平均值
【分页】
Count long 查询的记录数量
Count <this> out long 查询的记录数量，以参数out形式返回
Skip <this> int offset 查询向后偏移行数
Offset <this> int offset 查询向后偏移行数
Limit <this> int limit 查询多少条数据
Take <this> int limit 查询多少条数据
Page <this> int pageIndex, int pageSize 分页
【条件】
Where <this> Lambda 支持多表查询表达式
WhereIf <this> bool, Lambda 支持多表查询表达式
Where <this> string, parms 原生sql语法条件，Where("id = ?id", new { id = 1 })
WhereIf <this> bool, string, parms 原生sql语法条件，WhereIf(true, "id = ?id", new { id = 1 })
【分组】
GroupBy <this> Lambda 按选择的列分组，GroupBy(a => a.Name)
GroupBy <this> string, parms 按原生sql语法分组，GroupBy("concat(name, ?cc)", new { cc = 1 })
Having <this> string, parms 按原生sql语法聚合条件过滤，Having("count(name) = ?cc", new { cc = 1 })
【排序】
OrderBy <this> Lambda 按列排序，OrderBy(a => a.Time)
OrderByDescending <this> Lambda 按列倒向排序，OrderByDescending(a => a.Time)
OrderBy <this> string, parms 按原生sql语法排序，OrderBy("count(name) + ?cc", new { cc = 1 })
【联表】
LeftJoin <this> Lambda 左联查询，可使用导航属性，或指定关联的实体类型
InnerJoin <this> Lambda 联接查询，可使用导航属性，或指定关联的实体类型
RightJoin <this> Lambda 右联查询，可使用导航属性，或指定关联的实体类型
LeftJoin <this> string, parms 左联查询，使用原生sql语法，LeftJoin("type b on b.id = a.id and b.clicks > ?clicks", new { clicks = 1 })
InnerJoin <this> string, parms 联接查询，使用原生sql语法，InnerJoin("type b on b.id = a.id and b.clicks > ?clicks", new { clicks = 1 })
RightJoin <this> string, parms 右联查询，使用原生sql语法，RightJoin("type b on b.id = a.id and b.clicks > ?clicks", new { clicks = 1 })
From <this> Lambda 多表查询，3个表以上使用非常方便，目前设计最大支持10个表
【其他】
As <this> string alias = "a" 指定别名
Master <this> 指定从主库查询（默认查询从库）
Caching <this> int seconds, string key = null 缓存查询结果