static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, "Data Source = 127.0.0.1; Port=3306; User ID=root; Password=root; Initial Catalog=cccddd; Charset=utf8; SslMode=none; Max pool size=10")
    .Build(); // Be sure to define the Singleton Singleton pattern

[Table(Name = "tb_topic")]
class Topic {
    [Column(IsIdentity = true, IsPrimary = true)]
    public int Id { get; set; }
    public int Clicks { get; set; }
    public string Title { get; set; }
    public DateTime CreateTime { get; set; }}Copy the code

Group aggregation

var groupby = fsql.Select<Topic>()
    .GroupBy(a => new { tt2 = a.Title.Substring(0.2), mod4 = a.Id % 4 })
    .Having(a => a.Count() > 0 && a.Avg(a.Key.mod4) > 0 && a.Max(a.Key.mod4) > 0)
    .Having(a => a.Count() < 300 || a.Avg(a.Key.mod4) < 100)
    .OrderBy(a => a.Key.tt2)
    .OrderByDescending(a => a.Count())
    .ToList(a => new { a.Key.tt2, cou1 = a.Count(), arg1 = a.Avg(a.Key.mod4) });
//SELECT substr(a.`Title`, 1, 2) as1, count(1) as2, avg((a.`Id` % 4)) as3 
//FROM `xxx` a 
//GROUP BY substr(a.`Title`, 1, 2), (a.`Id` % 4) 
//HAVING (count(1) > 0 AND avg((a.`Id` % 4)) > 0 AND max((a.`Id` % 4)) > 0) AND (count(1) < 300 OR avg((a.`Id` % 4)) < 100).
//ORDER BY substr(a.`Title`, 1, 2), count(1) DESC
Copy the code

API

methods The return value parameter describe
ToSql string Returns the SQL statement to be executed
ToList<T> List<T> Lambda Performs an SQL query that returns a record for a specified field 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
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
【 group 】
GroupBy <this> Lambda GroupBy selected column, GroupBy(a => a.name)
GroupBy <this> string, parms GroupBy(“concat(name,? cc)”, new { cc = 1 })
Having <this> string, parms Having(“count(name) =? cc”, new { cc = 1 })
[members]
Key Returns the object selected by GroupBy
Value Return the main table or From<T2,T3…. > field selector

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