FreeSql uses ExpressionTree to optimize read speed, if you know technology, you must know.NETCore technology in addition to native code, the fastest is Emit and ExpressionTree. In the early stage of the project, reflection + cache was used. Although reflection performance was optimized by.netcore, there was still a certain gap after comparison with Dapper performance test. ExpresstionTree did not match Dapper performance until changed to ExpresstionTree. FreeSql supports more types, the realization of ExpressionTree complexity is large, interested friends can read the source code.

1. Return a single record

Topic t1 = fsql.Select<Topic>().ToOne();
Copy the code

FreeSql has a convention that ToOne always returns null or entity objects with data and ToList always returns non-null List< entity type >

2. Return List

List<Topic> t1 = fsql.Select<Topic>().ToList();
Copy the code

3. Return TreeList

List<Category> t2 = fsql.Select<Category>.ToTreeList();
Copy the code

Query data is processed as a tree. Note: Entities need to be configured with parent-child navigation attributes

4, Return List + navigation property data

List<Topic> t3 = fsql.Select<Topic>().LeftJoin(a => a.Type.Id == a.TypeId).ToList();
// The normal field + the navigation object Type is returned
Copy the code

5. Return the specified field

// Return a field
List<int> t4 = fsql.Select<Topic>().ToList(a => a.Id);

// Returns an anonymous classList< anonymous class > t5 = fsql.select <Topic>().tolist (a =>new { a.Id, a.Title });

// Returns a tuple
List<(int.string)> t6 = fsql.Select<Topic>().ToList<(int.string) > ("id, title");

// Return the SQL fieldList< anonymous class > t7 = fsql.select <Topic>().tolist (a =>new {
    a.Id,
    a.Title,
    a.Type, // You can return to the navigation property Type directly
    cstitle = "substr(a.title, 0, 2)".// select substr(a.test, 0, 2) as the query field
    csnow = Convert.ToDateTime("now()"), // use now() as the query field
    // Query the result of the window function
});

// Returns the fields of the subqueryList< anonymous class > t8 = 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)
});
Copy the code

6. Execute SQL

class xxx {
    public int Id { get; set; }
    public string Path { get; set; }
    public string Title2 { get; set; }
}

List<xxx> t9 = fsql.Ado.Query<xxx>("select * from song");
List<(int.string ,string)> t10 = fsql.Ado.Query<(int.string.string) > ("select * from song");
List<dynamic> t11 = fsql.Ado.Query<dynamic> ("select * from song");
Copy the code

Note: The entity properties of ado.query are invalid, such as [Column(Name = “XXX “)]

7. Use AsTable skillfully

fsql.Select<User>()
    .AsTable((a, b) => "(select * from tb_topic where clicks > 10)")
    .Page(1.10).ToList()
Copy the code

V1.0.1 ISelect WithSql(“select * from user… ) function quickly proxy this method

8 ToChunk.

Performing queries and returning data in chunks reduces memory overhead. For example, read 100,000 pieces of data and return 100 pieces of processing at a 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, list =>
{
    testlist2.AddRange(list);
});
Testlist1 returns the same data as testList2.
Copy the code

API

methods The return value parameter describe
ToSql string Returns the SQL statement to be executed
ToList List Execute SQL query, return all the records of T1 entity field, if there is a navigation attribute query return, return the list of Count 0 if the record does not exist
ToList<Dto> List<Dto> Lambda Execute an SQL query to return records for a specified field or Dto mapping, or a list with Count 0 if the record does not exist
ToList<T> List<T> string field Performs an SQL query that returns a record of the field specified and is received as a tuple or base type (int, String,long), and returns a list of Count 0 if the record does not exist
ToOne T1 Execute SQL query to return the first record of all fields of T1 entity, or null if the record does not exist
ToChunk < empty > int size, Action<List<T1>> done Performing SQL queries and returning data in chunks reduces memory overhead. For example, read 100,000 pieces of data and return 100 pieces of processing at a time.
Any bool Perform SQL query to check whether there are records
Sum T Lambda Specify a column sum
Min T Lambda Specify a column to minimize
Max T Lambda Specify a column for maximum value
Avg T Lambda Specify a column to average

Series article navigation

  • (1) Introduction

  • (2) Automatic migration of entities

  • (3) Entity characteristics

  • (4) Solid features of Fluent Api

  • (5) Insert data

  • (6) Batch insert data

  • (7) Ignore columns when inserting data

  • (8) Specify columns when inserting data

  • (9) Delete data

  • (x) Update data

  • (11) Update data Where

  • (12) Specify columns when updating data

  • (13) Ignore columns when updating data

  • (14) Batch update data

  • (15) Query data

  • (16) paging query

  • (17) joint table query

  • (18) Navigation attributes

  • (19) multi-table query

  • (20) query where ecascade

  • (21) Query returned data

  • (22) Dto mapping query

  • (23) Grouping and aggregation

  • (24) Introduction To Linq To Sql syntax

  • (25) delayed loading

  • Include, IncludeMany, Dto, ToList

  • (27) the SQL statement has been written, and entity class mapping for the second query

  • (28) Business

  • Lambda expression

  • (30) Reading and writing separation

  • (31) Zoning table

  • (32) Aop

  • CodeFirst type mapping

  • (34) CodeFirst migration instructions

  • CodeFirst custom features

The resources
Beginner’s Guide | “Select” | “Update” | “Insert” | “Delete”
Expression function | “CodeFirst” | “DbFirst” | “The BaseEntity”
“Repository” | “The UnitOfWork” | The Filter | Optimism Lock | “The DbContext”
Unread | Partition table | “The tenants” | The AOP | Black Tech | Update log