FreeSql

Table of Contents

Group Aggregation
Navigation Property Grouping
Multi-table Grouping
Query first record on GroupBy
Aggregate
API
Reference

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

中文 | English

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; }
}

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), 
        arg2 = a.Sum(a.Value.Clicks > 100 ? 1 : 0)
    });

//SELECT 
//substr(a.`Title`, 1, 2) as1, 
//count(1) as2, 
//avg(a.`Clicks`) as3, 
//sum(case when a.`Clicks` > 100 then 1 else 0 end) as4 
//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

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

var list = fsql.Select<Topic>()
    .ToAggregate(a => new 
    {
        cou1 = a.Count(), 
        arg1 = a.Avg(a.Key.Clicks), 
        arg2 = a.Sum(a.Key.Clicks > 100 ? 1 : 0)
    });

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

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

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

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

g.Value.Item1 corresponds to Topic
g.Value.Item2 corresponds to Category
g.Value.Item3 corresponds to Area

说明	方法	SQL
总数	.Count()	select count(*) from ...
求和	.Sum(a => a.Score)	select sum([Score]) from ...
平均	.Avg(a => a.Score)	select avg([Score]) from ...
最大值	.Max(a => a.Score)	select max([Score]) from ...
最小值	.Min(a => a.Score)	select min([Score]) from ...

lambda	sql	说明
SqlExt.IsNull(id, 0)	isnull/ifnull/coalesce/nvl	兼容各大数据库
SqlExt.DistinctCount(id)	count(distinct id)
SqlExt.GreaterThan	>	大于
SqlExt.GreaterThanOrEqual	>=	大于或等于
SqlExt.LessThan	<	小于
SqlExt.LessThanOrEqual	<=	小于
SqlExt.EqualIsNull	IS NULL	是否为 NULL
SqlExt.Case(字典)	case when .. end	根据字典 case
SqlExt.GroupConcat	group_concat(distinct .. order by .. separator ..)	MySql
SqlExt.FindInSet	find_in_set(str, strlist)	MySql
SqlExt.StringAgg	string_agg(.., ..)	PostgreSQL
SqlExt.Rank().Over().PartitionBy().ToValue()	rank() over(partition by xx)	开窗函数
SqlExt.DenseRank().Over().PartitionBy().ToValue()	dense_rank() over(partition by xx)
SqlExt.Count(id).Over().PartitionBy().ToValue()	count(id) over(partition by xx)
SqlExt.Sum(id).Over().PartitionBy().ToValue()	sum(id) over(partition by xx)
SqlExt.Avg(id).Over().PartitionBy().ToValue()	avg(id) over(partition by xx)
SqlExt.Max(id).Over().PartitionBy().ToValue()	max(id) over(partition by xx)
SqlExt.Min(id).Over().PartitionBy().ToValue()	min(id) over(partition by xx)
SqlExt.RowNumber(id).Over().PartitionBy().ToValue()	row_number(id) over(partition by xx)

Query first record on GroupBy

fsql.Select<User1>()
    .Where(a => a.Id < 1000)
    .WithTempQuery(a => new
    {
        item = a,
        rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
    })
    .Where(a => a.rownum == 1)
    .ToList();

SELECT *
FROM (
    SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
    FROM [User1] a
    WHERE a.[Id] < 1000
) a
WHERE (a.[rownum] = 1)

or：

fsql.Select<User1>()
    .Where(a => a.Id < 1000)
    .GroupBy(a => a.Nickname)
    .WithTempQuery(g => new { min = g.Min(g.Value.Id) })
    .From<User1>()
    .InnerJoin((a, b) => a.min == b.Id)
    .ToList((a, b) => b);

SELECT b.[Id], b.[Nickname] 
FROM ( 
    SELECT min(a.[Id]) [min] 
    FROM [User1] a 
    WHERE a.[Id] < 1000 
    GROUP BY a.[Nickname] ) a 
INNER JOIN [User1] b ON a.[min] = b.[Id]

more.. 《Nested Query》

Aggregate

Distinct

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

SqlExt.DistinctCount

fsql.Select<Topic>()
    .Aggregate(a => SqlExt.DistinctCount(a.Key.Title), out var count);

SELECT count(distinct a."title") as1 FROM "Topic" a

API

Method	Return	Parameter	Description
ToSql	string		Return the SQL statement to be executed
ToList<T>	List<T>	Lambda	Execute 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 field	Execute 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>	Lambda	Execute SQL query and return the aggregate result of the specified field. (Suitable for scenarios where GroupBy is not required)
Sum	T	Lambda	Specify a column to sum.
Min	T	Lambda	Specify a column to find the minimum.
Max	T	Lambda	Specify a column to find the maximum.
Avg	T	Lambda	Specify a column to average.
【Grouping】
GroupBy	<this>	Lambda	Group by the selected column, GroupBy(a => a.Name)
GroupBy	<this>	string, parms	Group by raw sql statement, GroupBy("concat(name, @cc)", new { cc = 1 })
Having	<this>	string, parms	Filter by raw SQL statement aggregation conditions, Having("count(name) = @cc", new { cc = 1 })
【Members】
Key			Returns the object selected by GroupBy
Value			Return to the main table or the field selector of From<T2,T3....>

Group Aggregation

Navigation Property Grouping

Multi-table Grouping

Query first record on GroupBy

Aggregate

API

Reference

Basic

使用指南

学习与交流

信息