FreeSql Documents

Group Aggregation Query

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, connectionString)
     //Be sure to define as singleton mode
    .Build(); 

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; }
}
1
2
3
4
5
6
7
8
9
10
11
12
13

Group Aggregation

var list = 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, cou1 = a.Count(), arg1 = a.Avg(a.Value.Clicks) });

//SELECT substr(a.`Title`, 1, 2) as1, count(1) as2, avg(a.`Id`) as3 
//FROM `Topic` 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
1
2
3
4
5
6
7
8
9
10
11
12
13

To find the aggregate value without grouping, please use ToAggregate instead of ToList

If Topic has the navigation property Category, and Category has the navigation property Area, the navigation property grouping code is as follows:

var list = fsql.Select<Topic>()
    .GroupBy(a => new { a.Clicks, a.Category })
    .ToList(a => new { a.Key.Category.Area.Name });
1
2
3

Note: Write as above, an error will be reported and cannot be resolved a.Key.Category.Area.Name. The solution is to use Include:

var list = fsql.Select<Topic>()
    .Include(a => a.Category.Area)
    //This line must be added, 
    //otherwise only the Category will be grouped without its sub-navigation property Area

    .GroupBy(a => new { a.Clicks, a.Category })
    .ToList(a => new { a.Key.Category.Area.Name });
1
2
3
4
5
6
7

However, you can also solve it like this:

var list = fsql.Select<Topic>()
    .GroupBy(a => new { a.Clicks, a.Category, a.Category.Area })
    .ToList(a => new { a.Key.Area.Name });
1
2
3

Multi-table Grouping

var list = fsql.Select<Topic, Category, Area>()
    .GroupBy((a, b, c) => new { a.Title, c.Name })
    .Having(g => g.Count() < 300 || g.Avg(g.Value.Item1.Clicks) < 100)
    .ToList(g => new { count = g.Count(), Name = g.Key.Name });
1
2
3
4
  • g.Value.Item1 corresponds to Topic
  • g.Value.Item2 corresponds to Category
  • g.Value.Item3 corresponds to Area

Aggregate

var list = fsql.Select<Topic>()
    .Aggregate(a => Convert.ToInt32("count(distinct title)"), out var count)
    .ToList();
1
2
3

API

MethodReturnParameterDescription
ToSqlstringReturn the SQL statement to be executed
ToList<T>List<T>LambdaExecute SQL query and return the records of the specified field. When the record does not exist, return a list with Count of 0.
ToList<T>List<T>string fieldExecute SQL query, and 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.
ToAggregate<T>List<T>LambdaExecute SQL query and return the aggregate result of the specified field. (Suitable for scenarios where GroupBy is not required)
SumTLambdaSpecify a column to sum.
MinTLambdaSpecify a column to find the minimum.
MaxTLambdaSpecify a column to find the maximum.
AvgTLambdaSpecify a column to average.
【Grouping】
GroupBy<this>LambdaGroup by the selected column, GroupBy(a => a.Name)
GroupBy<this>string, parmsGroup by raw sql statement, GroupBy("concat(name, @cc)", new { cc = 1 })
Having<this>string, parmsFilter by raw SQL statement aggregation conditions, Having("count(name) = @cc", new { cc = 1 })
【Members】
KeyReturns the object selected by GroupBy
ValueReturn to the main table or the field selector of From<T2,T3....>

Reference