- Notifications
You must be signed in to change notification settings - Fork 889
With Sql
2881099 edited this page Oct 11, 2023 · 11 revisions
中文 | English
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 enum Sex { Boy, Girl } public class TestClssDto { public string ID { get; set; } public int? Age { get; set; } public DateTime? Birthday { 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.
v3.2.666 WithTempQuery + FromQuery 嵌套查询
DataTable dt1 = _fsql.Select<object>() .WithSql("select * from TestClass") .Where(...) .ToDataTable("ID, Age");SELECT ID, Age FROM ( select * from TestClass ) a WHERE ...DataTable dt2 = _fsql.Select<object>() .WithSql("select * from TestClass") .Where(...) .ToDataTable("*");SELECT * FROM ( select * from TestClass ) a WHERE ...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 ...var list2 = _fsql.Select<object>() .WithSql("select * from TestClass ") .Where(...) .ToList<object>("*");SELECT * FROM ( select * from TestClass ) a WHERE ...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,10var 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,10v3.2.666 UnionAll 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) ftbvar 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 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();After using UNION ALL, there will be a problem if you paginate directly. Please see the specific example:
There is a problem with using WithSql + Page multiple times: There is a paging statement in each WithSql
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 After multiple Sql statements are combined with WithSql (and a UNION ALL statement is generated), if you directly use the Page method for paging, it will cause a paging statement to be generated in each sub-table.
WithSql can realize the function of sub-table with AsTable. When querying across sub-tables, paging will take effect in each sub-table (that is, each SQL paging in WithSql).
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 thePagemethod:
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