WithSql

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 class TestClssDto
    {
        public string ID { get; set; }

        public int? Age { get; set; }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

不同的查询方式。

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

1.返回DataTable

DataTable dt1 = _fsql.Select<object>()
	.WithSql("select * from TestClass ")
	.ToDataTable("ID,Age");
1
2
3
SELECT ID,Age 
	FROM(select * from TestClass  ) a
1
2

2.返回DataTable

DataTable dt2 = _fsql.Select<object>()
	.WithSql("select * from TestClass ")
	.ToDataTable("*");
1
2
3
SELECT * 
FROM ( select * from TestClass  ) a
1
2

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

List<(string,string)> list1 = _fsql
    .Select<object>()
	.WithSql("select * from TestClass ")
	.ToList<(string, string)>("ID,Age");
1
2
3
4
SELECT ID, Age
	FROM(select * from TestClass  ) a
1
2

4.返回List<object>

var list2 = _fsql.Select<object>()
	.WithSql("select * from TestClass ")
	.ToList<object>("*");
1
2
3
SELECT *
	FROM(select * from TestClass  ) a
1
2

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");
1
2
3
4
5
SELECT ID, Age
	FROM(select * from TestClass  ) a
	WHERE(1 = 1)
	ORDER BY ID DESC
	limit 0,10
1
2
3
4
5

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");
1
2
3
4
5
6
SELECT ID, Age
	FROM(select * from TestClass  ) a
	WHERE(1 = 1)
	ORDER BY ID DESC
	limit 0,10
1
2
3
4
5

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

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();
1
2
3
4
5
6
7
8
9
10
11
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
1
2
3
4
5
6
7
8
9
10
11

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

var sql = fsql.Select<User>()
    .AsTable((type, oldname) => "table_1")a
    .AsTable((type, oldname) => "table_2")
    .ToSql(a => a.Id);
1
2
3
4
select * from (SELECT a."Id" as1 FROM "table_1" a) ftb 
UNION ALL
select * from (SELECT a."Id" as1 FROM "table_2" a) ftb 
1
2
3

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();
1
2
3
4
5
6
7
8
9

分页问题

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();
1
2
3
4
5
6
7
8
 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14

多个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("*")
1
2
3
4

如上生成的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
1
2
3
4
5
6
7
  • 第二步:之后 调用Page则是通过Union ALL后的结果上分页
 var sql2 = g.mysql.Select<Topic>()
	 .WithSql(sql)
	 .Page(2, 10)
	 .ToSql();
1
2
3
4
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
1
2
3
4
5
6
7
8
9