9

withsql

2881099 edited this page 2023-10-11 19:03:57 +08:00

Table of Contents

• WithSql 自定义 SQL
• 1.返回 DataTable
• 2.返回 DataTable
• 3.返回List<Tuplue> 即List<(string,string)> 元组
• 4.返回List<object>
• 5.返回List<object> 且能支持分页
• 6.返回List<TestClassDto>且能支持分页
• 通过 WithSql + ToSQL实现 Union ALL 查询方法
• 1、二次 ISelect 查询：WithSql 使用多次，等于 UNION ALL 查询
• 2、跨分表查询：AsTable 相同实体多次操作，等于 Union ALL 查询
• 3、利用 ToSql 拼接新的 SQL，使用 IAdo 执行
• 分页问题
• 解决方案

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

中文 | English

WithSql 自定义 SQL

定义实体类

public class TestClass
{
    [Column(Name = "ID", IsPrimary = true)]
    public string No { get; set; }
    public int? Age { get; set; }
    public string Name { get; set; }
    [Column(Name = "BIRTH_DAY")]
    public DateTime? Birthday { get; set; }
    public decimal Point { get; set; }
    public Sex? Sex { get; set; }
}
public enum Sex { Boy, Girl }
public class TestClssDto
{
    public string ID { get; set; }
    public int? Age { get; set; }
    public DateTime? Birthday { get; set; }
}

不同的查询方式。

• 返回DataTable
• 返回List<Tuplue> 即List<(string,string)>元组
• 返回List<object> 且能支持分页
• 返回List<TestClassDto>且能支持分页

1.返回 DataTable

DataTable dt1 = _fsql.Select<object>()
    .WithSql("select * from TestClass")
    .Where(...)
    .ToDataTable("ID, Age");

SELECT ID, Age
FROM ( select * from TestClass ) a
WHERE ...

2.返回 DataTable

DataTable dt2 = _fsql.Select<object>()
    .WithSql("select * from TestClass")
    .Where(...)
    .ToDataTable("*");

SELECT *
FROM ( select * from TestClass ) a
WHERE ...

3.返回List<Tuplue> 即List<(string,string)> 元组

List<(string,string)> list1 = _fsql
    .Select<object>()
    .WithSql("select * from TestClass")
    .Where(...)
    .ToList<(string, string)>("ID, Age");

SELECT ID, Age
FROM ( select * from TestClass ) a
WHERE ...

4.返回List<object>

var list2 = _fsql.Select<object>()
    .WithSql("select * from TestClass ")
    .Where(...)
    .ToList<object>("*");

SELECT *
FROM ( select * from TestClass ) a
WHERE ...

5.返回List<object> 且能支持分页

var list3 = _fsql.Select<object>()
    .WithSql("select * from TestClass ")
    .WhereIf(true, "1=1")
    .Page(1, 10).OrderBy("ID DESC")
    .ToList<object>("ID,Age");

SELECT ID, Age
FROM ( select * from TestClass ) a
WHERE (1 = 1)
ORDER BY ID DESC
limit 0,10

6.返回List<TestClassDto>且能支持分页

var list4 = _fsql.Select<object>()
    .WithSql("select * from TestClass ")
    .WhereIf(true, "1=1")
    .Page(1, 10)
    .OrderBy("ID DESC")
    .ToList<TestClssDto>("ID,Age,BIRTH_DAY as Birthday");

SELECT ID,Age,BIRTH_DAY as Birthday
FROM ( select * from TestClass ) a
WHERE (1 = 1)
ORDER BY ID DESC
limit 0,10

通过 WithSql + ToSQL实现 Union ALL 查询方法

v3.2.666 UnionAll 联合查询

1、二次 ISelect 查询：WithSql 使用多次，等于 UNION ALL 查询

WithSql 使用多次为 UNION ALL 查询，所以我们可以利用 ISelect.ToSql(FieldAliasOptions.AsProperty) 得到生成的 SQL，如下：

var sql1 = fsql.Select<Topic>()
    .Where(a => a.Title.Contains("xxx"))
    .ToSql();
var sql2 = fsql.Select<Topic>()
    .Where(a => a.Title.Contains("yyy"))
    .ToSql();

fsql.Select<Topic>()
    .WithSql(sql1)
    .WithSql(sql2)
    .ToList();

SELECT  * from (SELECT a.`Id`, a.`Clicks`, a.`TypeGuid`, a.`Title`, a.`CreateTime` 
FROM ( SELECT a.`Id`, a.`Clicks`, a.`TypeGuid`, a.`Title`, a.`CreateTime` 
    FROM `tb_topic` a 
    WHERE ((a.`Title`) LIKE '%xxx%') ) a) ftb
 
UNION ALL
 
SELECT  * from (SELECT a.`Id`, a.`Clicks`, a.`TypeGuid`, a.`Title`, a.`CreateTime` 
FROM ( SELECT a.`Id`, a.`Clicks`, a.`TypeGuid`, a.`Title`, a.`CreateTime` 
    FROM `tb_topic` a 
    WHERE ((a.`Title`) LIKE '%yyy%') ) a) ftb

2、跨分表查询：AsTable 相同实体多次操作，等于 Union ALL 查询

var sql = fsql.Select<User>()
    .AsTable((type, oldname) => "table_1")a
    .AsTable((type, oldname) => "table_2")
    .ToSql(a => a.Id);

select * from (SELECT a."Id" as1 FROM "table_1" a) ftb 
UNION ALL
select * from (SELECT a."Id" as1 FROM "table_2" a) ftb 

3、利用 ToSql 拼接新的 SQL，使用 IAdo 执行

var sql1 = fsql.Select<Topic>()
    .Where(a => a.Id > 100 && a.Id < 200)
    .ToSql(a => new { a.Id, a.Title }, FieldAliasOptions.AsProperty);
var sql2 = fsql.Select<Topic>()
    .Where(a => a.Id > 1001 && a.Id < 1200)
    .ToSql(a => new { a.Id, a.Title }, FieldAliasOptions.AsProperty);

fsql.Ado.CommandFluent($"{sql1} UNION ALL {sql2}")
    .ExecuteDataTable();

分页问题

Union All 之后 如果直接 分页会有一个问题。请看具体示例

多次 WithSql + Page 存在问题：每个WithSql内都有一个Page分页

var sql1 = fsql.Select<Topic>()
    .Where(a => a.Title.Contains("xxx"))
    .ToSql();
var sql2 = fsql.Select<Topic>()
    .Where(a => a.Title.Contains("yyy"))
    .ToSql();

fsql.Select<Topic>().WithSql(sql1).WithSql(sql2).Page(1, 20).ToList();

 SELECT  * from (SELECT a.`Id`, a.`Clicks`, a.`TypeGuid`, a.`Title`, a.`CreateTime` 
    FROM ( SELECT a.`Id`, a.`Clicks`, a.`TypeGuid`, a.`Title`, a.`CreateTime` 
        FROM `tb_topic` a 
        WHERE ((a.`Title`) LIKE '%xxx%') ) a 
    limit 0,20) ftb
    
    UNION ALL
    
    SELECT  * from (SELECT a.`Id`, a.`Clicks`, a.`TypeGuid`, a.`Title`, a.`CreateTime` 
    FROM ( SELECT a.`Id`, a.`Clicks`, a.`TypeGuid`, a.`Title`, a.`CreateTime` 
        FROM `tb_topic` a 
        WHERE ((a.`Title`) LIKE '%yyy%') ) a 
    limit 0,20) ftb

多个sql union all使用withsql，直接Page分页，会导致每个子表都生效，子表都生成分页。

WithSql 可以和 AsTable 实现分表的功能。

分表跨表查询的时候，分页是要向每个子表（即每个 WithSql 中的 SQL 分页）都生效。

解决方案

多次 withsql ，如需分页，需要按下面的二步操作

• 第一步：通过witsql，将二个sql组成一个sql。

 var sql = fsql.Select<Topic>()
    .WithSql("SELECT * FROM tb_topic where id > 11")
    .WithSql("SELECT * FROM tb_topic where id < 10")
    .ToSql("*")

如上生成的 UOION ALL 的 sql

SELECT  * from (SELECT * 
    FROM ( SELECT * FROM tb_topic where id > 11 ) a) ftb

    UNION ALL

    SELECT  * from (SELECT * 
    FROM ( SELECT * FROM tb_topic where id < 10 ) a) ftb

• 第二步：之后 调用 Page 则是通过 Union ALL 后的结果上分页

 var sql2 = g.mysql.Select<Topic>()
     .WithSql(sql)
     .Page(2, 10)
     .ToSql();

SELECT a.`Id`, a.`Clicks`, a.`TypeGuid`, a.`Title`, a.`CreateTime`
FROM ( SELECT  * from (SELECT *
    FROM ( SELECT * FROM tb_topic where id > 11 ) a) ftb

    UNION ALL

    SELECT  * from (SELECT *
    FROM ( SELECT * FROM tb_topic where id < 10 ) a) ftb ) a 
limit 10,10

Basic

• 入门 Getting Started
• 安装 How to Install
• 添加 Insert Data
  • 单条 Single
  • 批量 Batch
• 删除 Delete Data
• 修改 Update Data
  • 更新实体 Entity
  • 更新指定列 Specified Columns
• 添加或修改 Insert or Update ✨
• 查询 Query Data
  • 分页 Pagination
  • 单表 Single Table
  • 多表 Multi Tables
  • 分组聚合 Group Aggregation
  • 嵌套查询 Nested Query ✨
  • 联合查询
  • 返回数据 Return Data ✨
  • 延时加载 Lazy Loading
  • 贪婪加载 Greed Loading ✨
  • LINQ 扩展 LinqToSql
  • 树型查询 Parent-Child Relp. ✨
• 仓储层 Repository Layer
  • 工作单元 Unit of Work
  • 联级保存 Cascade Saving
  • 联级删除 Cascade Deletion
  • 工作单元管理器 ✨
  • 聚合根（实验室）
• CodeFirst
  • 实体特性✨
  • FluentApi
  • 自定义特性
  • 类型映射
  • 导航属性 Entity Relationship ✨
  • 迁移结构
• DbFirst
• 表达式函数
  • 字符串
  • 日期/时间
  • 其他
  • 自定义函数
• 事务
• 过滤器
• ADO
• AOP✨
• 读写分离
• 分表分库
• 租户
• 性能
• 动态操作 Dynamic Operations
• 你不知道的功能✨
• API参考

使用指南

• Dapper比较
• EntityFramework比较

学习与交流

• 常见问题
• 提交Issues

信息

• 服务支持
• 更新日志