With Sql

中文 | English

Use Custom SQL Statements

Define entity class:

    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; }
    }

Different query results:

• Return to DataTable.
• Return List<Tuplue> i.e. List<(string,string)> tuple.
• Return List<object> and support paging.
• Return List<TestClassDto> and support paging.

Return to DataTable with specified columns

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

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

Return to DataTable with all columns

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

SELECT * 
FROM ( select * from TestClass  ) a

Return List<Tuple> (i.e. List<(string, string)>)

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

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

Return List<object>

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

SELECT *
	FROM(select * from TestClass  ) a

Return List<object> and support paging

  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

Return List<TestClassDto> and support paging

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

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

WithSql+ ToSQL = Union ALL

Two-Stage ISelect Query: Use WithSql Multiple Times to Convert to UNION ALL Query

After using WithSql multiple times, a query statement based on UNION ALL will be generated. So we can use ISelect.ToSql(FieldAliasOptions.AsProperty) to get the generated SQL as follows:

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 查询

Cross Sub-Table Query: Wse AsTable for the Same Entity Multiple Times to Convert to UNION ALL Query

var sql = fsql.Select<User>()
    .AsTable((type, oldname) => "table_1")
    .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 

Use ToSql to Splice New SQL Statements, And Use IAdo to Execute

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();

Paging Problem

After using UNION ALL, there will be a problem if you paginate directly. Please see the specific example:

多次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分页）都生效。

Solution

Call WithSql Multiple Times

Call WithSql multiple times. If you need to paging, you need to follow the two steps below.

• Step 1: combine the two Sql statements into one by WithSql:

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

The above code will be generated as a Sql statement using UNION ALL:

 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

• Step 2: on the basis of the SQL statement containing UNION ALL, page by calling the Page method:

 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