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

Return to DataTable with specified columns

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

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

Return to DataTable with all columns

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

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

Return List<Tuple> (i.e. 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 ...

Return List<object>

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

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

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,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 Query

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

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:

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).

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