表达式函数

nicye大约 13 分钟约 3939 字

表达式函数

这是 FreeSql 非常特色的功能之一，请别错过文档的细节，可映射的类型基本都可以使用对应的表达式函数，例如日期、字符串、IN 查询、数组（PostgreSQL 数组）、字典（PostgreSQL HStore）等等。

SqlExt.xxx 是 FreeSql 默认提供常见的自定义函数，详细见开窗函数。

Lambda接拼

单表

Expression<Func<T, bool>> where = null;
where = where.And(b => b.num > 0);
where = where.Or(b => b.num > 0);
fsql.Select<T>().Where(where)

多表

Expression<Func<HzyTuple<T1, T2, T3, T4, T5, T6>, bool>> where = null;
where = where.Or(a => a.t6.Id > 0);
fsql.Select<T1, T2, T3, T4, T5, T6>().Where(where)

In查询

var t1 = fsql.Select<T>()
  .Where(a => new[] { 1, 2, 3 }.Contains(a.Id))
  .ToList();
//SELECT .. FROM ..
//WHERE (a.`Id` in (1,2,3))

已优化，防止 where in 元素多过的 SQL 错误，如：

[Err] ORA-01795: maximum number of expressions in a list a 1000

原来：where id in (1..1333)

现在：where id in (1..500) or id in (501..1000) or id in (1001..1333)

In多列查询

//元组集合
vae lst = new List<(Guid, DateTime)>();
lst.Add((Guid.NewGuid(), DateTime.Now));
lst.Add((Guid.NewGuid(), DateTime.Now));

var t2 = fsql.Select<T>()
  .Where(a => lst.Contains(a.Id, a.ct1))
  .ToList();
//SELECT .. FROM ..
//WHERE (a."Id" = '685ee1f6-bdf6-4719-a291-c709b8a1378f' AND a."ct1" = '2019-12-07 23:55:27' OR 
//a."Id" = '5ecd838a-06a0-4c81-be43-1e77633b7404' AND a."ct1" = '2019-12-07 23:55:27')

v3.2.650 使用 .Where(a => list.Any(b => b.Item1 == a. Id && b.Item2 == a. ct1))

WHERE (id, code) in ((1,'code1'), (2,'code2')) 实现代码：In多列查询，表达式自定义实现

In子表

fsql.Select<Topic>()
  .Where(a => fsql.Select<Topic>().As("b").ToList(b => b.Id).Contains(a.Id))
  .ToList();
//SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`
//FROM `Topic` a
//WHERE (((a.`Id`) in (SELECT b.`Id`
//    FROM `Topic` b)))

Exists子表

fsql.Select<Topic>()
  .Where(a => fsql.Select<Topic>().As("b").Where(b => b.Id == a.Id).Any())
  .ToList();
//SELECT a.`Id`, a.`Title`, a.`Clicks`, a.`CreateTime`, a.`CategoryId`
//FROM `Topic` a
//WHERE (exists(SELECT 1
//    FROM `Topic` b
//    WHERE (b.`Id` = a.`Id`)
//    limit 0,1))

提示：由于子查询的实体类与上层相同，使用 As("b") 指明别名，以便区分

查找今天创建的数据

fsql.Select<T>()
  .Where(a => a.CreateTime.Date == DateTime.Today)
  .ToList();
//这行代码说明 FreeSql 表达式解析强大，不是所有 ORM 都支持

fsql.Select<T>()
  .Where(a => a.CreateTime.Between(DateTime.Today, DateTime.Today.AddDays(1)))
  .ToList();
//正常用法应该是这样

fsql.Select<T>()
  .Where(a => a.CreateTime.Subtract(DateTime.Today).TotalDays == 0)
  .ToList();
//WHERE datediff(day, date1, date2) = 0

SqlServer nvarchar/varchar 已兼容表达式解析，分别解析为：N'' 和 ''，优化索引执行计划；

日期格式化

fsql.Select<T>()
  .First(a => a.CreateTime.ToString("HH:mm:ss"));
// SELECT date_format(a.`CreateTime`, '%H:%i:%s') as1 
// FROM `xxx` a 
// limit 0,1

v1.5.0 支持了常用 c# 日期格式化，yyyy MM dd HH mm ss yy M d H hh h m s tt t

tt t 为 AM PM

开窗函数

fsql.Select<T1, T2>()
  .InnerJoin((a, b) => b.Id == a.Id)
  .ToList((a, b) => new
  {
    Id = a.Id,
    EdiId = b.Id,
    over1 = SqlExt.Rank().Over().OrderBy(a.Id).OrderByDescending(b.EdiId).ToValue()
  });

v1.6.0 利用自定义解析功能，增加 SqlExt.Rank().Over().PartitionBy(...)、MySql group_concat 常用函数，欢迎 PR 补充

FreeSql 默认集成了 SqlExt.cs 扩展解析方法：

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)

以上是利用《自定义解析》实现的功能

子表Join

v1.8.0+ string.Join + ToList 实现将子查询的多行结果，拼接为一个字符串，如："1,2,3,4"

fsql.Select<Topic>().ToList(a => new {
  id = a.Id,
  concat = string.Join(",", fsql.Select<StringJoin01>().ToList(b => b.Id))
});
//SELECT a.`Id`, (SELECT group_concat(b.`Id` separator ',') 
//    FROM `StringJoin01` b) 
//FROM `Topic` a

提示：子查询 string.Join + ToList 适配了 sqlserver/pgsql/oracle/mysql/sqlite/firebird/duckdb/达梦/金仓/南大/翰高 #405open in new window

子表First/Count/Sum/Max/Min/Avg

fsql.Select<Category>().ToList(a => new 
{
  all = a,
  first = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).First(b => b.Id),
  count = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Count(),
  sum = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Sum(b => b.Clicks),
  max = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Max(b => b.Clicks),
  min = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Min(b => b.Clicks),
  avg = fsql.Select<Topic>().Where(b => b.CategoryId == a.Id).Avg(b => b.Clicks)
});

子表ToList

v3.2.650+ 以下最多执行3次 SQL

fsql.Select<Topic>().ToList(a => new
{
  all = a,
  list1 = fsql.Select<T2>().ToList(),
  list2 = fsql.Select<T2>().Where(b => b.TopicId == a.Id).ToList()
});

fsql.Select<Topic>()
  .GroupBy(a => new { a.Author })
  .WithTempQuery(a => new { Author = a.Key.Author, Count = a.Count() })
  .ToList(a => new 
  {
    a.Author, a.Count,
    list1 = fsql.Select<T2>().ToList(),
    list2 = fsql.Select<T2>().Where(b => b.Author == a.Author).ToList()
  });

自定义解析

[ExpressionCall]
public static class DbFunc {
  //必要定义 static + ThreadLocal
  static ThreadLocal<ExpressionCallContext> context = new ThreadLocal<ExpressionCallContext>();

  public static DateTime FormatDateTime(this DateTime that, string arg1)
  {
    var up = context.Value;
    if (up.DataType == FreeSql.DataType.Sqlite) //重写内容
      up.Result = $"date_format({up.ParsedContent["that"]}, {up.ParsedContent["arg1"]})";
    return that;
  }
}

var sql1 = fsql.Select<SysModule>()
  .ToSql(a => a.CreateTime.FormatDateTime("yyyy-MM-dd"));
//SELECT date_format(a."CreateTime", 'yyyy-MM-dd') as1 
//FROM "SysModule" a

[ExpressionCall] 特性可在静态扩展类上标记，也可以在单个静态方法上标记；

ExpressionCallContext 属性	类型	描述
DataType	FreeSql.DataType	用于实现不同数据库的适配判断条件
ParsedContent	Dictionary<string, string>	函数的各参数解析结果
DbParameter	DbParameter	that 被参数化的对象（有可能为 null)
UserParameters	List<DbParameter>	可附加参数化对象
Result	string	返回表达式函数表示的 SQL 字符串

当扩展方法返回值为 string 时，其返回值也可以当作 context.Value.Result 功能

当不想解析指定参数时，使用特性 [RawValue] 标记

参数化

Where(lambda) 解析出来的默认是纯文本（已防止SQL注入），对数据库执行计划要求特别高，可以开启 lambda 参数化功能。

var fsql = new FreeSqlBuilder() //请务必定义成 Singleton 单例模式
  .UseGenerateCommandParameterWithLambda(true)
  ...

var id = 1;
fsql.Select<Song>().Where(a => a.Id == id).ToList();
//SELECT .. FROM `Song` a WHERE `Id` = @exp_0

生成的参数对象，DbType、Size、Precision、Scale 值设置默认已作优化，与实体属性定义一致。

诡异操作：

如果不希望 string 参数与实体属性的 Size 相同，可利用自定义表达式函数功能，如下：

var name = "testname";
fsql.Select<TestMySqlStringIsNullable>()
  .Where(a => a.varchar == name).ToList();

fsql.Select<TestMySqlStringIsNullable>()
  .Where(a => a.varchar == name.SetDbParameter(10)).ToList();

public class TestMySqlStringIsNullable {
  public Guid id { get; set; }

  [Column(DbType = "varchar(100)")]
  public string varchar { get; set; }
}

[ExpressionCall]
public static class DbFunc {
  static ThreadLocal<ExpressionCallContext> context = new ThreadLocal<ExpressionCallContext>();

  public static string SetDbParameter(this string that, int size)
  {
    if (context.Value.DbParameter != null)
      context.Value.DbParameter.Size = size;
    return context.Value.ParsedContent["that"];
  }
}

第一条语句产生的参数对象 Size 为 100，第二条为 10：