# 表达式函数

## In查询

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

In 只能实现单列查询，多列怎么办？

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

var t2 = fsql.Select<T>()
.Where(a => lst.Contains(a.Id, a.ct1))
.ToSql();
//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' OR
//a."Id" = 'b8b366f3-1c03-4547-9c96-d362dd5cae6a' AND a."ct1" = '2019-12-07 23:55:27')

## 查找今天创建的数据

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

var t4 = fsql.Select<T>().Where(a => a.CreateTime.Between(DateTime.Today, DateTime.Today.AddDays(1))).ToSql();

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

## 自定义表达式函数(v0.11.23)

[ExpressionCall]
public static class DbFunc
{

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

public static string SetDbParameter(this string that, int size)
{
if (context.Value.DbParameter != null)
{
//已经参数化了，直接修改长度
//提示：此条件可能开启了全局表达式参数化功能 UseGenerateCommandParameterWithLambda(true)
context.Value.DbParameter.Size = size;
return that;
}
var guid = Guid.NewGuid().ToString("N").ToLower();
{
ParameterName = guid,
SqlDbType = System.Data.SqlDbType.VarChar,
Size = size,
Value = that
});
return \$"@{guid}"; //重写内容
}
}

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

var sql2 = fsql.Select<SysModule>()
.Where(a => a.Title == "123123".SetDbParameter(20))
.ToSql();
//SELECT ...
//FROM [SysModule] a
//WHERE (a.[Title] = @6a8b79d7001540369a2f52ecbba15679)

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

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

## 命令参数化(v0.12.1)

var fsql = new FreeSqlBuilder()
.UseGenerateCommandParameterWithLambda(true)
...

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

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
{

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

## AOP拦截解析

IFreeSql 对象有 Aop 成员，那里提供一堆 AOP 拦截的方法。其实有一个事件名称：ParseExpression。

/// <summary>
/// 可自定义解析表达式
/// </summary>
EventHandler<AopParseExpressionEventArgs> ParseExpression { get; set; }

public class AopParseExpressionEventArgs : EventArgs {
public AopParseExpressionEventArgs(Expression expression, Func<Expression, string> freeParse) {
this.Expression = expression;
this.FreeParse = freeParse;
}

/// <summary>
/// 内置解析功能，可辅助您进行解析
/// </summary>
public Func<Expression, string> FreeParse { get; }

/// <summary>
/// 需要您解析的表达式
/// </summary>
public Expression Expression { get; }
/// <summary>
/// 解析后的内容
/// </summary>
public string Result { get; set; }
}

FreeParse 是提供给外部的解析工具，它拥有 FreeSql 所有表达式功能，当您自定义解析的过程中遇到特别难处理的，可通过它快速解析出表达式的子部分内容。

## 表达式函数全览

a ? b : c case when a then b else c end case when a then b else c end case when a then b else c end case when a then b else c end a成立时取b值，否则取c值
a ?? b ifnull(a, b) isnull(a, b) coalesce(a, b) nvl(a, b) 当a为null时，取b值

a - b a - b a - b a - b a - b
a * b a * b a * b a * b a * b
a / b a / b a / b a / b a / b
a / b a div b a / b a / b trunc(a / b) 整除(a,b都为整数)
a % b a % b a % b a % b mod(a,b)

### 数组

a.Length - - case when a is null then 0 else array_length(a,1) end - 数组长度

a.Any() - - case when a is null then 0 else array_length(a,1) end > 0 - 数组是否为空

a.Contains(b) - - a @> array[b] - a数组是否包含b元素
a.Concat(b) - - a || b - 数组相连
a.Count() - - 同 Length - 数组长度

### 字典 Dictionary<string, string>

a.Count - - case when a is null then 0 else array_length(akeys(a),1) end - 字典长度
a.Keys - - akeys(a) - 返回字典所有key数组
a.Values - - avals(a) - 返回字典所有value数组
a.Contains(b) - - a @> b - 字典是否包含b
a.ContainsKey(b) - - a? b - 字典是否包含key
a.Concat(b) - - a || b - 字典相连
a.Count() - - 同 Count - 字典长度

### JSON JToken/JObject/JArray

a.Count - - jsonb_array_length(coalesce(a, '[])) - json数组类型的长度
a.Any() - - jsonb_array_length(coalesce(a, '[])) > 0 - json数组类型，是否为空
a.Contains(b) - - coalesce(a, '{}') @> b::jsonb - json中是否包含b
a.ContainsKey(b) - - coalesce(a, '{}') ? b - json中是否包含键b
a.Concat(b) - - coalesce(a, '{}') b::jsonb
Parse(a) - - a::jsonb - 转化字符串为json类型

### 字符串

string.Empty '' '' '' ''
string.IsNullOrEmpty(a) (a is null or a = '') (a is null or a = '') (a is null or a = '') (a is null or a = '') (a is null or a = '')
string.Concat(a,b,c...) concat(a, b, c) a + b + c a || b || c a || b || c a || b || c
a.CompareTo(b) strcmp(a, b) - case when a = b then 0 when a > b then 1 else -1 end case when a = b then 0 when a > b then 1 else -1 end case when a = b then 0 when a > b then 1 else -1 end
a.Contains('b') a like '%b%' a like '%b%' a ilike'%b%' a like '%b%' a like '%b%'
a.EndsWith('b') a like '%b' a like '%b' a ilike'%b' a like '%b' a like '%b'
a.IndexOf(b) locate(a, b) - 1 locate(a, b) - 1 strpos(a, b) - 1 instr(a, b, 1, 1) - 1 instr(a, b) - 1
a.Length char_length(a) len(a) char_length(a) length(a) length(a)
a.Replace(b, c) replace(a, b, c) replace(a, b, c) replace(a, b, c) replace(a, b, c) replace(a, b, c)
a.StartsWith('b') a like 'b%' a like 'b%' a ilike'b%' a like 'b%' a like 'b%'
a.Substring(b, c) substr(a, b, c + 1) substring(a, b, c + 1) substr(a, b, c + 1) substr(a, b, c + 1) substr(a, b, c + 1)
a.ToLower lower(a) lower(a) lower(a) lower(a) lower(a)
a.ToUpper upper(a) upper(a) upper(a) upper(a) upper(a)
a.Trim trim(a) trim(a) trim(a) trim(a) trim(a)
a.TrimEnd rtrim(a) rtrim(a) rtrim(a) rtrim(a) rtrim(a)
a.TrimStart ltrim(a) ltrim(a) ltrim(a) ltrim(a) ltrim(a)

### 日期

DateTime.Now now() getdate() current_timestamp systimestamp
DateTime.UtcNow utc_timestamp() getutcdate() (current_timestamp at time zone 'UTC') sys_extract_utc(systimestamp)
DateTime.Today curdate convert(char(10),getdate(),120) current_date trunc(systimestamp)
DateTime.MaxValue cast('9999/12/31 23:59:59' as datetime) '9999/12/31 23:59:59' '9999/12/31 23:59:59'::timestamp to_timestamp('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS.FF6')
DateTime.MinValue cast('0001/1/1 0:00:00' as datetime) '1753/1/1 0:00:00' '0001/1/1 0:00:00'::timestamp to_timestamp('0001-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS.FF6')
DateTime.Compare(a, b) a - b a - b extract(epoch from a::timestamp-b::timestamp) extract(day from (a-b))
DateTime.DaysInMonth(a, b) dayofmonth(last_day(concat(a, '-', b, '-1'))) datepart(day, dateadd(day, -1, dateadd(month, 1, cast(a as varchar) + '-' + cast(b as varchar) + '-1'))) extract(day from (a
DateTime.Equals(a, b) a = b a = b a = b a = b
DateTime.IsLeapYear(a) a%4=0 and a%100<>0 or a%400=0 a%4=0 and a%100<>0 or a%400=0 a%4=0 and a%100<>0 or a%400=0 mod(a,4)=0 AND mod(a,100)<>0 OR mod(a,400)=0
DateTime.Parse(a) cast(a as datetime) cast(a as datetime) a::timestamp to_timestamp(a,'YYYY-MM-DD HH24:MI:SS.FF6')
a.Date cast(date_format(a, '%Y-%m-%d') as datetime) convert(char(10),a,120) a::date trunc(a)
a.Day dayofmonth(a) datepart(day, a) extract(day from a::timestamp) cast(to_char(a,'DD') as number)
a.DayOfWeek dayofweek(a) datepart(weekday, a) - 1 extract(dow from a::timestamp) case when to_char(a)='7' then 0 else cast(to_char(a) as number) end
a.DayOfYear dayofyear(a) datepart(dayofyear, a) extract(doy from a::timestamp) cast(to_char(a,'DDD') as number)
a.Hour hour(a) datepart(hour, a) extract(hour from a::timestamp) cast(to_char(a,'HH24') as number)
a.Millisecond floor(microsecond(a) / 1000) datepart(millisecond, a) extract(milliseconds from a::timestamp)-extract(second from a::timestamp)*1000 cast(to_char(a,'FF3') as number)
a.Minute minute(a) datepart(minute, a) extract(minute from a::timestamp) cast(to_char(a,'MI') as number)
a.Month month(a) datepart(month, a) extract(month from a::timestamp) cast(to_char(a,'FF3') as number)
a.Second second(a) datepart(second, a) extract(second from a::timestamp) cast(to_char(a,'SS') as number)
a.Subtract(b) timestampdiff(microsecond, b, a) datediff(millisecond, b, a) * 1000 (extract(epoch from a::timestamp-b::timestamp)*1000000) a - b
a.Ticks timestampdiff(microsecond, '0001-1-1', a) * 10 datediff(millisecond, '1970-1-1', a) * 10000 + 621355968000000000 extract(epoch from a::timestamp)*10000000+621355968000000000 cast(to_char(a,'FF7') as number)
a.TimeOfDay timestampdiff(microsecond, date_format(a, '%Y-%m-%d'), a) '1970-1-1 ' + convert(varchar, a, 14) extract(epoch from a::time)*1000000 a - trunc(a)
a.Year year(a) datepart(year, a) extract(year from a::timestamp)
a.Equals(b) a = b a = b a = b a = b
a.CompareTo(b) a - b a - b a - b a - b
a.ToString() date_format(a, '%Y-%m-%d %H:%i:%s.%f') convert(varchar, a, 121) to_char(a, 'YYYY-MM-DD HH24:MI:SS.US') to_char(a,'YYYY-MM-DD HH24:MI:SS.FF6')

### 时间

TimeSpan.Zero 0 0 - 0微秒
TimeSpan.MaxValue 922337203685477580 922337203685477580 - numtodsinterval(233720368.5477580,'second')
TimeSpan.MinValue -922337203685477580 -922337203685477580 - numtodsinterval(-233720368.5477580,'second')
TimeSpan.Compare(a, b) a - b a - b - extract(day from (a-b))
TimeSpan.Equals(a, b) a = b a = b - a = b
TimeSpan.FromDays(a) a * 1000000 * 60 * 60 * 24 a * 1000000 * 60 * 60 * 24 - numtodsinterval(a*86400,'second')
TimeSpan.FromHours(a) a * 1000000 * 60 * 60 a * 1000000 * 60 * 60 - numtodsinterval(a*3600,'second')
TimeSpan.FromMilliseconds(a) a * 1000 a * 1000 - numtodsinterval(a/1000,'second')
TimeSpan.FromMinutes(a) a * 1000000 * 60 a * 1000000 * 60 - numtodsinterval(a*60,'second')
TimeSpan.FromSeconds(a) a * 1000000 a * 1000000 - numtodsinterval(a,'second')
TimeSpan.FromTicks(a) a / 10 a / 10 - numtodsinterval(a/10000000,'second')
a.Add(b) a + b a + b - a + b
a.Subtract(b) a - b a - b - a - b
a.CompareTo(b) a - b a - b - extract(day from (a-b))
a.Days a div (1000000 * 60 * 60 * 24) a div (1000000 * 60 * 60 * 24) - extract(day from a)
a.Hours a div (1000000 * 60 * 60) mod 24 a div (1000000 * 60 * 60) mod 24 - extract(hour from a)
a.Milliseconds a div 1000 mod 1000 a div 1000 mod 1000 - cast(substr(extract(second from a)-floor(extract(second from a)),2,3) as number)
a.Seconds a div 1000000 mod 60 a div 1000000 mod 60 - extract(second from a)
a.Ticks a * 10 a * 10 - (extract(day from a)*86400+extract(hour from a)*3600+extract(minute from a)*60+extract(second from a))*10000000
a.TotalDays a / (1000000 * 60 * 60 * 24) a / (1000000 * 60 * 60 * 24) - extract(day from a)
a.TotalHours a / (1000000 * 60 * 60) a / (1000000 * 60 * 60) - (extract(day from a)*24+extract(hour from a))
a.TotalMilliseconds a / 1000 a / 1000 - (extract(day from a)*86400+extract(hour from a)*3600+extract(minute from a)*60+extract(second from a))*1000
a.TotalMinutes a / (1000000 * 60) a / (1000000 * 60) -
a.TotalSeconds a / 1000000 a / 1000000 - (extract(day from a)*86400+extract(hour from a)*3600+extract(minute from a)*60+extract(second from a))
a.Equals(b) a = b a = b - a = b
a.ToString() cast(a as varchar) cast(a as varchar) - to_char(a)

### 数学函数

Math.Abs(a) abs(a) abs(a) abs(a)
Math.Acos(a) acos(a) acos(a) acos(a) acos(a)
Math.Asin(a) asin(a) asin(a) asin(a) asin(a)
Math.Atan(a) atan(a) atan(a) atan(a) atan(a)
Math.Atan2(a, b) atan2(a, b) atan2(a, b) atan2(a, b) -
Math.Ceiling(a) ceiling(a) ceiling(a) ceiling(a) ceil(a)
Math.Cos(a) cos(a) cos(a) cos(a) cos(a)
Math.Exp(a) exp(a) exp(a) exp(a) exp(a)
Math.Floor(a) floor(a) floor(a) floor(a) floor(a)
Math.Log(a) log(a) log(a) log(a) log(e,a)
Math.Log10(a) log10(a) log10(a) log10(a) log(10,a)
Math.PI(a) 3.1415926535897931 3.1415926535897931 3.1415926535897931 3.1415926535897931
Math.Pow(a, b) pow(a, b) power(a, b) pow(a, b) power(a, b)
Math.Round(a, b) round(a, b) round(a, b) round(a, b) round(a, b)
Math.Sign(a) sign(a) sign(a) sign(a) sign(a)
Math.Sin(a) sin(a) sin(a) sin(a) sin(a)
Math.Sqrt(a) sqrt(a) sqrt(a) sqrt(a) sqrt(a)
Math.Tan(a) tan(a) tan(a) tan(a) tan(a)
Math.Truncate(a) truncate(a, 0) floor(a) trunc(a, 0) trunc(a, 0)

### 类型转换

Convert.ToBoolean(a) | bool.Parse(a) a not in ('0','false') a not in ('0','false') a::varchar not in ('0','false','f','no') - a not in ('0','false')
Convert.ToByte(a) | byte.Parse(a) cast(a as unsigned) cast(a as tinyint) a::int2 cast(a as number) cast(a as int2)
Convert.ToChar(a) substr(cast(a as char),1,1) substring(cast(a as nvarchar),1,1) substr(a::char,1,1) substr(to_char(a),1,1) substr(cast(a as character),1,1)
Convert.ToDateTime(a) | DateTime.Parse(a) cast(a as datetime) cast(a as datetime) a::timestamp to_timestamp(a,'YYYY-MM-DD HH24:MI:SS.FF6') datetime(a)
Convert.ToDecimal(a) | decimal.Parse(a) cast(a as decimal(36,18)) cast(a as decimal(36,19)) a::numeric cast(a as number) cast(a as decimal(36,18))
Convert.ToDouble(a) | double.Parse(a) cast(a as decimal(32,16)) cast(a as decimal(32,16)) a::float8 cast(a as number) cast(a as double)
Convert.ToInt16(a) | short.Parse(a) cast(a as signed) cast(a as smallint) a::int2 cast(a as number) cast(a as smallint)
Convert.ToInt32(a) | int.Parse(a) cast(a as signed) cast(a as int) a::int4 cast(a as number) cast(a as smallint)
Convert.ToInt64(a) | long.Parse(a) cast(a as signed) cast(a as bigint) a::int8 cast(a as number) cast(a as smallint)
Convert.ToSByte(a) | sbyte.Parse(a) cast(a as signed) cast(a as tinyint) a::int2 cast(a as number) cast(a as smallint)
Convert.ToSingle(a) | float.Parse(a) cast(a as decimal(14,7)) cast(a as decimal(14,7)) a::float4 cast(a as number) cast(a as float)
Convert.ToString(a) cast(a as char) cast(a as nvarchar) a::varchar to_char(a) cast(a as character)
Convert.ToUInt16(a) | ushort.Parse(a) cast(a as unsigned) cast(a as smallint) a::int2 cast(a as number) cast(a as unsigned)
Convert.ToUInt32(a) | uint.Parse(a) cast(a as unsigned) cast(a as int) a::int4 cast(a as number) cast(a as decimal(10,0))
Convert.ToUInt64(a) | ulong.Parse(a) cast(a as unsigned) cast(a as bigint) a::int8 cast(a as number) cast(a as decimal(21,0))
Guid.Parse(a) substr(cast(a as char),1,36) cast(a as uniqueidentifier) a::uuid substr(to_char(a),1,36) substr(cast(a as character),1,36)
Guid.NewGuid() - newid() - - -
new Random().NextDouble() rand() rand() random() dbms_random.value random()

## 使用指南

• FreeSql有什么特色功能
• Dapper比较
• EntityFramework比较

## 信息

##### Clone this wiki locally
You can’t perform that action at this time.