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
                  • Return a Single Record
                    • Return List
                      • Return TreeList
                        • Return List with Navigation Property Data
                          • Return the Specify Field
                            • Ignore the Specified Field When Returning
                              • ToSql
                                • Executing SQL
                                  • WithSql
                                    • ToChunk
                                      • Dto Mapping
                                        • API
                                          • Reference
                                          • Lazy Loading
                                            • Greed-Loading
                                              • Linq to Sql
                                                • withsql
                                                  • Parent Child Relationship Query

                                                Return Data

                                                author iconnicyecalendar iconNovember 21, 2021timer iconAbout 4 minword iconAbout 1310 words

                                                On This Page
                                                • Return a Single Record
                                                • Return List
                                                • Return TreeList
                                                • Return List with Navigation Property Data
                                                • Return the Specify Field
                                                • Ignore the Specified Field When Returning
                                                • ToSql
                                                • Executing SQL
                                                • WithSql
                                                • ToChunk
                                                • Dto Mapping
                                                • API
                                                • Reference

                                                # Return Data

                                                FreeSql uses ExpressionTree to optimize the reading speed. If you know the technology, you must know that in addition to native code under .NETCore technology, the fastest are Emit and ExpressionTree.

                                                At the beginning, we used Reflection + Cache. Although .NETCore optimized the reflection performance, after comparing the performance with Dapper, we found that there was still a certain gap. After we refactored to an implementation based on ExpresstionTree, our performance was comparable to Dapper.

                                                FreeSql supports many types, and the complexity of implementing ExpressionTree is relatively large. If developers are interested in this, you can browse the source code.

                                                # Return a Single Record

                                                Topic t1 = fsql.Select<Topic>().ToOne();
                                                
                                                1

                                                The FreeSql convention is that ToOne/First will always return null or entity object with data, and ToList will always return a non-null List<entity type>

                                                # Return List

                                                List<Topic> t1 = fsql.Select<Topic>().ToList();
                                                
                                                1

                                                # Return TreeList

                                                List<Category> t2 = fsql.Select<Category>.ToTreeList();
                                                List<Category> t3 = fsql.Select<Category>.Where(a => a.Name = "Home Appliances").AsTreeCte().ToTreeList();
                                                //v1.6.0 AsTreeCte() Recursive CTE query for all sub-categories under "Home Appliances"
                                                
                                                1
                                                2
                                                3

                                                The query data will be processed into a tree type.

                                                Note: the entity needs to be configured with parent-child navigation properties

                                                # Return List with Navigation Property Data

                                                List<Topic> t4 = fsql.Select<Topic>().LeftJoin(a => a.Type.Id == a.TypeId).ToList();
                                                
                                                1

                                                At this time, the common field of Topic and the Type field of navigation property will be queried

                                                # Return the Specify Field

                                                //Return a field
                                                List<int> t5 = fsql.Select<Topic>().ToList(a => a.Id);
                                                
                                                //Return anonymous class
                                                List<ANONYMOUS_TYPE> t6 = fsql.Select<Topic>().ToList(a => new { a.Id, a.Title });
                                                
                                                //Return tuple
                                                List<(int, string)> t7 = fsql.Select<Topic>().ToList<(int, string)>("id, title");
                                                
                                                //Return to navigation properties
                                                List<ANONYMOUS_TYPE> t8 = fsql.Select<Topic>().ToList(a => new {
                                                    a.Id, a.Title,
                                                    a.Type //You can directly return the navigation property "Type"
                                                });
                                                
                                                //Return SQL field
                                                List<ANONYMOUS_TYPE> t9 = fsql.Select<Topic>().ToList(a => new {
                                                    cstitle = "substr(a.title, 0, 2)", //Use substr(a.title, 0, 2) as the query field
                                                    csnow = Convert.ToDateTime("now()"), //Use now() as the query field
                                                    //Wonderful idea: how to query the result of window function
                                                });
                                                
                                                //Return the fields of the subquery
                                                List<ANONYMOUS_TYPE> t10 = fsql.Select<Topic>().ToList(a => new {
                                                    a.Id,
                                                    count = fsql.Select<T2>().Count(),
                                                    max = fsql.Select<T2>().Max(b => b.Id),
                                                    min = fsql.Select<T2>().Min(b => b.Id),
                                                    name = fsql.Select<2>().First(b => b.name)
                                                });
                                                
                                                //Return the records of the subquery v3.2.650+
                                                List<匿名类> t11 = fsql.Select<Topic>().ToList(a => new {
                                                    a.Id,
                                                    list1 = fsql.Select<T2>().ToList(),
                                                    list2 = fsql.Select<T2>().Where(b => b.TopicId == a.Id).ToList()
                                                });
                                                
                                                1
                                                2
                                                3
                                                4
                                                5
                                                6
                                                7
                                                8
                                                9
                                                10
                                                11
                                                12
                                                13
                                                14
                                                15
                                                16
                                                17
                                                18
                                                19
                                                20
                                                21
                                                22
                                                23
                                                24
                                                25
                                                26
                                                27
                                                28
                                                29
                                                30
                                                31
                                                32
                                                33
                                                34
                                                35
                                                36
                                                37

                                                In the early constant mechanism, we left it to raw SQL. If you really need to return the string, you can write: "'xxx'"

                                                # Ignore the Specified Field When Returning

                                                Reference to: https://github.com/dotnetcore/FreeSql/issues/528

                                                # ToSql

                                                All ToList can use ToSql to return SQL string. There are two options:

                                                • FieldAliasOptions.AsIndex, the default option, automatically generates as1, as2, as3 .... etc. field aliases, which can prevent the problem of multiple tables with the same field.
                                                • FieldAliasOptions.AsProperty, use the property name as the field alias, appropriately use the two-stage structure SQL and execute it again.

                                                # Executing SQL

                                                class xxx {
                                                    public int Id { get; set; }
                                                    public string Path { get; set; }
                                                    public string Title2 { get; set; }
                                                }
                                                
                                                List<xxx> t11 = fsql.Ado.Query<xxx>("select * from song");
                                                List<(int, string ,string)> t12 = fsql.Ado.Query<(int, string, string)>("select * from song");
                                                List<dynamic> t13 = fsql.Ado.Query<dynamic>("select * from song");
                                                
                                                1
                                                2
                                                3
                                                4
                                                5
                                                6
                                                7
                                                8
                                                9

                                                Note: The entity attributes of Ado.Query are invalid, for example, [Column(Name = "xxx")] is invalid

                                                # WithSql

                                                fsql.Select<Topic>()
                                                  .WithSql("select * from Topic where clicks > @val", new { val = 10 })
                                                  .Page(1, 10)
                                                  .ToList()
                                                
                                                //SELECT a.`Id`, a.`Clicks`, a.`CategoryId`, a.`Title`, a.`CreateTime` 
                                                //FROM (select * from Topic where clicks > @val) a 
                                                
                                                1
                                                2
                                                3
                                                4
                                                5
                                                6
                                                7

                                                When WithSql is used multiple times, UNION ALL query will be used

                                                # ToChunk

                                                Execute queries and return data in blocks, which can reduce memory overhead. For example, if 100,000 pieces of data are read, 100 pieces of data are returned for processing each time.

                                                var testlist1 = fsql.Select<Song>().OrderBy(a => a.Id).ToList();
                                                var testlist2 = new List<Song>();
                                                fsql.Select<Song>().OrderBy(a => a.Id).ToChunk(100, done => {
                                                    testlist2.AddRange(done.Object);
                                                    //done.IsBreak = true; v1.7.0 stop reading 
                                                });
                                                //Here is a demonstration that the final data returned by testlist1 and testlist2 are the same.
                                                
                                                1
                                                2
                                                3
                                                4
                                                5
                                                6
                                                7

                                                # Dto Mapping

                                                fsql.Select<Song>().ToList<Dto>();
                                                //Case 1: The field with the same property name of Dto and Song is queried, and List<Dto> is returned
                                                
                                                fsql.Select<Song>().ToList(a => new Dto { xxx = a.ext }) 
                                                //Case 2: The field with the same property name of Dto and Song is queried, the mapping ext is adjusted, and List<Dto> is returned
                                                
                                                fsql.Select<Song>().ToList(a => new Song { id = a.id }) 
                                                //Case 3: The type specified by Lambda is the same as the type of Song, only the specified field id is queried, and List<Song> is returned
                                                
                                                fsql.Select<Song>().ToList(a => new { id = a.id }) 
                                                //Case 4: Lambda specifies an anonymous type, only queries the specified field id, and returns List<ANONYMOUS_OBJECT>
                                                
                                                1
                                                2
                                                3
                                                4
                                                5
                                                6
                                                7
                                                8
                                                9
                                                10
                                                11

                                                Please handle the difference carefully.

                                                fsql.Select<Song>().ToList(a => new Dto(a.id))
                                                //Case 5: Only query id and return List<Dto>
                                                
                                                fsql.Select<Song>().ToList(a => new Dto(a.id) { xxx = a.ext })
                                                //Case 6: Query id, ext and return List<Dto>
                                                
                                                fsql.Select<Song>().ToList(a => new Song(a.id))
                                                //Case 7: Query id and return List<Song>
                                                
                                                fsql.Select<Song>().ToList(a => new Song(a.id) { xxx = a.ext })
                                                //Case 8: Query id, ext and return List<Song>
                                                
                                                1
                                                2
                                                3
                                                4
                                                5
                                                6
                                                7
                                                8
                                                9
                                                10
                                                11

                                                All methods of GroupBy are not applicable to DTO mapping rules

                                                This kind of mapping supports single table/multi-table, mapping before querying data (not to query all fields first and then to memory mapping).

                                                Searching rules, searching for property names, will loop the internal object _tables (it will grow after join query), and check the main table first until the same field is found.

                                                For example:

                                                Suppose A, B, and C all have id. When the queried Dto structure is: Dto {id, a1, a2, b1, b2 }, A.id is mapped. You can also specify the id = C.id mapping.

                                                Reminder: You can directly map a navigation property in DTO

                                                # API

                                                MethodReturnParameterDescription
                                                ToSqlstringReturn the SQL statement to be executed
                                                ToListList<T1>Execute a SQL query and return the records of all the fields of the T1 entity. If there are navigation properties, they will be queried and returned together. If the record does not exist, a list with Count of 0 will be returned.
                                                ToList<Dto>List<Dto>LambdaExecute SQL query, return the record of the specified field or Dto mapping, if the record does not exist, return the list with Count as 0.
                                                ToList<T>List<T>string fieldExecute SQL query, return the record of the field specified by field, and receive it as a tuple or basic type (int, string, long). If the record does not exist, return a list with Count of 0.
                                                ToOneT1Execute SQL query and return the first record of all fields of the T1 entity, or null if the record does not exist.
                                                ToChunk<空>int size, Action<FetchCallbackArgs<List<T1>>> doneExecute SQL query and return data in blocks, which can reduce memory overhead. For example, if 100,000 pieces of data are read, 100 pieces of data are returned for processing each time.
                                                AnyboolExecute SQL query to determine whether there is a record
                                                SumTLambdaSpecify a column to sum.
                                                MinTLambdaSpecify a column to find the minimum.
                                                MaxTLambdaSpecify a column to find the maximum.
                                                AvgTLambdaSpecify a column to average.

                                                # Reference

                                                • 《Query from Multi Tables》
                                                • 《Return Data》
                                                • 《LinqToSql》
                                                • 《Repository Layer》
                                                • 《Filters and Global Filters》
                                                • 《FreeSql Optimization: Lazy Loading》
                                                • 《FreeSql Optimization: Greed Loading》
                                                • 《Expression Function》
                                                • 《Performance》
                                                • 《Tenant》
                                                edit iconEdit this pageopen in new window
                                                Last update: 5/23/2022, 10:38:02 AM
                                                Contributors: igeekfan,luoyunchong
                                                Prev
                                                Group Aggregation Query
                                                Next
                                                Lazy Loading
                                                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