Skip to content
FreeSql DocumentsFreeSql Documents
Guide
NuGetopen in new window
Apiopen in new window
github icon
  • Guide

      • Query Data
        • Pagination
          • Query from Single Table
            • Query from Multi Tables
              • Group Aggregation Query
                • Return Data
                  • Lazy Loading
                    • Greed-Loading
                      • Linq to Sql
                        • withsql
                          • Return to DataTable with specified columns
                            • Return to DataTable with all columns
                              • Return List<Tuple> (i.e. List<(string, string)>)
                                • Return List<object>
                                  • Return List<object> and support paging
                                    • Return List<TestClassDto> and support paging
                                      • WithSql+ ToSQL = Union ALL
                                        • Two-Stage ISelect Query: Use WithSql Multiple Times to Convert to UNION ALL Query
                                          • Cross Sub-Table Query: Wse AsTable for the Same Entity Multiple Times to Convert to UNION ALL Query
                                            • Use ToSql to Splice New SQL Statements, And Use IAdo to Execute
                                            • Paging Problem
                                              • Solution: Call WithSql Multiple Times
                                            • Parent Child Relationship Query

                                          withsql

                                          author iconnicyecalendar iconNovember 21, 2021timer iconAbout 3 minword iconAbout 967 words

                                          On This Page
                                          • Return to DataTable with specified columns
                                          • Return to DataTable with all columns
                                          • Return List<Tuple> (i.e. List<(string, string)>)
                                          • Return List<object>
                                          • Return List<object> and support paging
                                          • Return List<TestClassDto> and support paging
                                          • WithSql+ ToSQL = Union ALL
                                            • Two-Stage ISelect Query: Use WithSql Multiple Times to Convert to UNION ALL Query
                                            • Cross Sub-Table Query: Wse AsTable for the Same Entity Multiple Times to Convert to UNION ALL Query
                                            • Use ToSql to Splice New SQL Statements, And Use IAdo to Execute
                                          • Paging Problem
                                            • Solution: Call WithSql Multiple Times

                                          # withsql

                                          # 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; }
                                              }
                                          
                                          1
                                          2
                                          3
                                          4
                                          5
                                          6
                                          7
                                          8
                                          9
                                          10
                                          11
                                          12
                                          13
                                          14
                                          15
                                          16
                                          17
                                          18
                                          19
                                          20
                                          21
                                          22
                                          23
                                          24

                                          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");
                                          
                                          1
                                          2
                                          3
                                          SELECT ID,Age 
                                          	FROM(select * from TestClass  ) a
                                          
                                          1
                                          2

                                          # Return to DataTable with all columns

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

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

                                          # Return List<object>

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

                                          # 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");
                                          
                                          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

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

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

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

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

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

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

                                          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
                                          
                                          1
                                          2
                                          3
                                          4
                                          5
                                          6
                                          7
                                          • 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();
                                          
                                          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
                                          edit iconEdit this pageopen in new window
                                          Last update: 5/31/2022, 10:08:53 PM
                                          Contributors: luoyunchong,igeekfan,luoyunchong
                                          Prev
                                          Linq to Sql
                                          Next
                                          Parent Child Relationship Query
                                          Copyright © 2018-present nicye
                                          Copyright © 2022 nicye

                                          This app can be installed on your PC or mobile device. This will allow this web app to look and behave like any other installed app. You will find it in your app lists and be able to pin it to your home screen, start menus or task bars. This installed web app will also be able to safely interact with other apps and your operating system.

                                          Description