共计 20412 个字符，预计需要花费 52 分钟才能阅读完成。
简介
FreeSql 是一个功能强大的 .NETStandard 库，用于对象关系映射程序 (O/RM)，支持 .NETCore 2.1+ 或 .NETFramework 4.6.1+。
定义
IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite,
@”Data Source=|DataDirectory|/test.db;Pooling=true;Max Pool Size=10″)
.UseAutoSyncStructure(true) // 自动同步实体结构到数据库
.Build();
入门篇
查询
1、查询一条
fsql.Select<Xxx>.Where(a => a.Id == 1).First();
2、分页：第 1 页，每页 20 条
fsql.Select<Xxx>.Page(1, 20).ToList();
细节说明：SqlServer 2012 以前的版本，使用 row_number 分页；SqlServer 2012+ 版本，使用最新的 fetch next rows 分页；
3、IN
fsql.Select<Xxx>.Where(a => new { 1,2,3}.Contains(a.Id)).ToList();
4、联表
fsql.Select<Xxx>.LeftJoin<Yyy>((a, b) => a.YyyId == b.Id).ToList();
5、Exists 子表
fsql.Select<Xxx>.Where(a => fsql.Select<Yyy>(b => b.Id == a.YyyId).Any()).ToList();
6、GroupBy & Having
fsql.Select<Xxx>.GroupBy(a => new { a.CategoryId}).Having(a => a.Count > 2).ToList(a => new { a.Key, a.Count() });
7、指定字段查询
fsql.Select<Xxx>.Limit(10).ToList(a => a.Id);
fsql.Select<Xxx>.Limit(10).ToList(a => new { a.Id, a.Name});
fsql.Select<Xxx>.Limit(10).ToList(a => new Dto());
8、执行 SQL 返回实体
fsql.Ado.Query<Xxx>(“select * from xxx”);
fsql.Ado.Query<(int, string, string)>(“select * from xxx”);
fsql.Ado.Query<dynamic>(“select * from xxx”);
插入
1、单条
fsql.Insert<Xxx>().AppendData(new Xxx()).ExecuteAffrows();
2、单条，返回自增值
fsql.Insert<Xxx>().AppendData(new Xxx()).ExecuteIdentity();
3、单条，返回插入的行（SqlServer 的 output 特性）
fsql.Insert<Xxx>().AppendData(new Xxx()).ExecuteInserted();
4、批量
fsql.Insert<Xxx>().AppendData( 数组).ExecuteAffrows();
5、批量，返回插入的行（SqlServer 的 output 特性）
fsql.Insert<Xxx>().AppendData( 数组).ExecuteInserted();
6、指定列
fsql.Insert<Xxx>().AppendData(new Xxx()).InsertColumns(a => a.Title).ExecuteAffrows();
fsql.Insert<Xxx>().AppendData(new Xxx()).InsertColumns(a => new { a.Id, a.Title}).ExecuteAffrows();
7、忽略列
fsql.Insert<Xxx>().AppendData(new Xxx()).IgnoreColumns(a => a.Title).ExecuteAffrows();
fsql.Insert<Xxx>().AppendData(new Xxx()).IgnoreColumns(a => new { a.Id, a.Title}).ExecuteAffrows();
8、事务
fsql.Insert<Xxx>().AppendData(new Xxx()).WithTransaction(事务对象).ExecuteAffrows();
更新
1、指定列
fsql.Update<Xxx>(1).Set(a => a.CreateTime, DateTime.Now).ExecuteAffrows();
2、累加，set clicks = clicks + 1
fsql.Update<Xxx>(1).Set(a => a.Clicks + 1).ExecuteAffrows();
3、保存
fsql.Update<Xxx>().SetSource( 单个实体).ExecuteAffrows();
4、批量保存
fsql.Update<Xxx>().SetSource( 数组).ExecuteAffrows();
5、忽略列
fsql.Update<Xxx>().SetSource( 数组).IgnoreColumns(a => new { a.Clicks, a.CreateTime}).ExecuteAffrows();
6、更新条件
fsql.Update<Xxx>().SetSource( 数组).Where(a => a.Clicks > 100).ExecuteAffrows();
7、事务
fsql.Update<Xxx>(1).Set(a => a.Clicks + 1).WithTransaction(事务对象).ExecuteAffrows();
删除
1、dywhere
主键值
new[] { 主键值 1, 主键值 2}
Xxx 对象
new[] { Xxx 对象 1, Xxx 对象 2}
new {id = 1}
fsql.Delete<Xxx>(new[] {1, 2}).ExecuteAffrows();
//DELETE FROM `xxx` WHERE (`Id` = 1 OR `Id` = 2)
fsql.Delete<Xxx>(new Xxx { Id = 1, Title = “test”}).ExecuteAffrows();
//DELETE FROM `xxx` WHERE (`Id` = 1)
fsql.Delete<Xxx>(new[] {new Xxx { Id = 1, Title = “test”}, new Xxx {Id = 2, Title = “test”} }).ExecuteAffrows();
//DELETE FROM `xxx` WHERE (`Id` = 1 OR `Id` = 2)
fsql.Delete<Xxx>(new { id = 1}).ExecuteAffrows();
//DELETE FROM `xxx` WHERE (`Id` = 1)
2、条件
fsql.Delete<Xxx>().Where(a => a.Id == 1).ExecuteAffrows();
//DELETE FROM `xxx` WHERE (`Id` = 1)
fsql.Delete<Xxx>().Where(“id = ?id”, new { id = 1}).ExecuteAffrows();
//DELETE FROM `xxx` WHERE (id = ?id)
var item = new Xxx {Id = 1, Title = “newtitle”};
var t7 = fsql.Delete<Xxx>().Where(item).ExecuteAffrows();
//DELETE FROM `xxx` WHERE (`Id` = 1)
var items = new List<Xxx>();
for (var a = 0; a < 10; a++) items.Add(new Xxx { Id = a + 1, Title = $”newtitle{a}”, Clicks = a * 100 });
fsql.Delete<Xxx>().Where(items).ExecuteAffrows();
//DELETE FROM `xxx` WHERE (`Id` IN (1,2,3,4,5,6,7,8,9,10))
3、事务
fsql.Delete<Xxx>().Where(a => a.Id == 1).WithTransaction(事务对象).ExecuteAffrows();
初级篇
表达式
支持功能丰富的表达式函数解析，方便程序员在不了解数据库函数的情况下编写代码。这是 FreeSql 非常特色的功能之一，深入细化函数解析尽量做到满意，所支持的类型基本都可以使用对应的表达式函数，例如 日期、字符串、IN 查询、数组（PostgreSQL 的数组）、字典（PostgreSQL HStore) 等等。
1、查找今天创建的数据
fsql.Delete<Xxx>().Where(a => a.CreateTime.Date == DateTime.Now.Date).ToList();
2、SqlServer 下随机获取记录
fsql.Delete<Xxx>().OrderBy(a => Guid.NewGuid()).Limit(1).ToSql();
4、表达式函数全览
表达式
MySql
SqlServer
PostgreSQL
Oracle
功能说明
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
数字相加
数字 + 字符串
concat(a, b)
cast(a as varchar) + cast(b as varchar)
case(a as varchar) + 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 / b
除
a % b
a % b
a % b
a % b
mod(a,b)
模
等等 …
5、数组
表达式
MySql
SqlServer
PostgreSQL
Oracle
功能说明
a.Length
–
–
case when a is null then 0 else array_length(a,1) end
–
数组长度
常量数组.Length
–
–
array_length(array[ 常量数组元素逗号分割],1)
–
数组长度
a.Any()
–
–
case when a is null then 0 else array_length(a,1) end > 0
–
数组是否为空
常量数组.Contains(b)
b in (常量数组元素逗号分割)
b in (常量数组元素逗号分割)
b in (常量数组元素逗号分割)
b in (常量数组元素逗号分割)
IN 查询
a.Contains(b)
–
–
a @> array[b]
–
a 数组是否包含 b 元素
a.Concat(b)
–
–
a + b
–
数组相连
a.Count()
–
–
同 Length
–
数组长度
一个细节证明 FreeSql 匠心制作
通用的 in 查询 select.Where(a => new []{1,2,3}.Contains(a.xxx))
假设 xxxs 是 pgsql 的数组字段类型，其实会与上面的 in 查询起冲突，FreeSql 解决了这个矛盾 select.Where(a => a.xxxs.Contains(1))
6、字典 Dictionary<string, string>
表达式
MySql
SqlServer
PostgreSQL
Oracle
功能说明
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
–
字典长度
7、JSON JToken/JObject/JArray
表达式
MySql
SqlServer
PostgreSQL
Oracle
功能说明
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
–
连接两个 json
Parse(a)
–
–
a::jsonb
–
转化字符串为 json 类型
8、字符串
表达式
MySql
SqlServer
PostgreSQL
Oracle
Sqlite
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 = ”)
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.PadLeft(b, c)
lpad(a, b, c)
–
lpad(a, b, c)
lpad(a, b, c)
lpad(a, b, c)
a.PadRight(b, c)
rpad(a, b, c)
–
rpad(a, b, c)
rpad(a, b, c)
rpad(a, b, c)
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)
使用字符串函数可能会出现性能瓶颈，虽然不推荐使用，但是作为功能库这也是不可缺少的功能之一。
9、日期
表达式
MySql
SqlServer
PostgreSQL
Oracle
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
‘-‘
b
‘-01′)::timestamp+’1 month’::interval-‘1 day’::interval)
cast(to_char(last_day(a
‘-‘
b
‘-01′),’DD’) as number)
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.Add(b)
date_add(a, interval b microsecond)
dateadd(millisecond, b / 1000, a)
a::timestamp+(b
‘ microseconds’)::interval
增加 TimeSpan 值
a + b
a.AddDays(b)
date_add(a, interval b day)
dateadd(day, b, a)
a::timestamp+(b
‘ day’)::interval
a + b
a.AddHours(b)
date_add(a, interval b hour)
dateadd(hour, b, a)
a::timestamp+(b
‘ hour’)::interval
a + b/24
a.AddMilliseconds(b)
date_add(a, interval b*1000 microsecond)
dateadd(millisecond, b, a)
a::timestamp+(b
‘ milliseconds’)::interval
a + b/86400000
a.AddMinutes(b)
date_add(a, interval b minute)
dateadd(minute, b, a)
a::timestamp+(b
‘ minute’)::interval
a + b/1440
a.AddMonths(b)
date_add(a, interval b month)
dateadd(month, b, a)
a::timestamp+(b
‘ month’)::interval
add_months(a,b)
a.AddSeconds(b)
date_add(a, interval b second)
dateadd(second, b, a)
a::timestamp+(b
‘ second’)::interval
a + b/86400
a.AddTicks(b)
date_add(a, interval b/10 microsecond)
dateadd(millisecond, b / 10000, a)
a::timestamp+(b
‘ microseconds’)::interval
a + b/86400000000
a.AddYears(b)
date_add(a, interval b year)
dateadd(year, b, a)
a::timestamp+(b
‘ year’)::interval
add_months(a,b*12)
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)
年
cast(to_char(a,’YYYY’) as number)
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′)
10、时间
表达式
MySql(微秒)
SqlServer(秒)
PostgreSQL(微秒)
Oracle(Interval day(9) to second(7))
TimeSpan.Zero
0
0
–
0 微秒
numtodsinterval(0,’second’)
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)
–
(extract(day from a)1440+extract(hour from a)60+extract(minute from a))
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)
11、数学函数
表达式
MySql
SqlServer
PostgreSQL
Oracle
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)
12、类型转换
表达式
MySql
SqlServer
PostgreSQL
Oracle
Sqlite
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.ToString(a)
cast(a as decimal(14,7))
cast(a as decimal(14,7))
a::float4
to_char(a)
cast(a as character)
Convert.ToSingle(a), float.Parse(a)
cast(a as char)
cast(a as nvarchar)
a::varchar
cast(a as number)
cast(a as smallint)
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()
CodeFirst
参数选项
说明
IsAutoSyncStructure
【开发环境必备】自动同步实体结构到数据库，程序运行中检查实体表是否存在，然后创建或修改
IsSyncStructureToLower
转小写同步结构
IsSyncStructureToUpper
转大写同步结构，适用 Oracle
IsConfigEntityFromDbFirst
使用数据库的主键和自增，适用 DbFirst 模式，无须在实体类型上设置 [Column(IsPrimary)] 或者 ConfigEntity。此功能目前可用于 mysql/sqlserver/postgresql。
IsNoneCommandParameter
不使用命令参数化执行，针对 Insert/Update，调试神器
IsLazyLoading
延时加载导航属性对象，导航属性需要声明 virtual
1、配置实体（特性）
public class Song {
[Column(IsIdentity = true)]
public int Id {get; set;}
public string Title {get; set;}
public string Url {get; set;}
public virtual ICollection<Tag> Tags {get; set;}
[Column(IsVersion = true)]
public long versionRow {get; set;}
}
2、在外部配置实体
fsql.CodeFirst
.ConfigEntity<Song>(a => {
a.Property(b => b.Id).IsIdentity(true);
a.Property(b => b.versionRow).IsVersion(true);
});
DbFirst
1、获取所有数据库
fsql.DbFirst.GetDatabases();
// 返回字符串数组, [“cccddd”, “test”]
2、获取指定数据库的表信息
fsql.DbFirst.GetTablesByDatabase(fsql.DbFirst.GetDatabases()[0]);
// 返回包括表、列详情、主键、唯一键、索引、外键、备注等信息
3、生成实体
new FreeSql.Generator.TemplateGenerator()
.Build(fsql.DbFirst,
@”C:\Users\28810\Desktop\github\FreeSql\Templates\MySql\simple-entity”,
// 模板目录（事先下载）
@”C:\Users\28810\Desktop\ 你的目录 ”,
// 生成后保存的目录
“cccddd”
// 数据库
);
高级篇
Repository 仓储实现
1、单个仓储
var curd = fsql.GetRepository<Xxx, int>();
//curd.Find(1);
var item = curd.Get(1);
curd.Update(item);
curd.Insert(item);
curd.Delete(1);
curd.Select.Limit(10).ToList();
2、工作单元
using (var uow = fsql.CreateUnitOfWork()) {
var songRepos = uow.GetRepository<Song>();
var userRepos = uow.GetRepository<User>();
// 上面两个仓储，由同一 UnitOfWork uow 创建
// 在此执行仓储操作
// 这里不受异步方便影响
uow.Commit();
}
3、局部过滤器 + 数据验证
var topicRepository = fsql.GetGuidRepository<Topic>(a => a.UserId == 1);
之后在使用 topicRepository 操作方法时：
查询 / 修改 / 删除时附过滤条件，从而达到不会修改其他用户的数据；
添加时，使用过滤条件验证合法性，若不合法则抛出异常；如以下方法就会报错：
topicRepository.Insert(new Topic { UserId = 2})
4、乐观锁
更新实体数据，在并发情况下极容易造成旧数据将新的记录更新。FreeSql 核心部分已经支持乐观锁。
乐观锁的原理，是利用实体某字段，如：long version，更新前先查询数据，此时 version 为 1，更新时产生的 SQL 会附加 where version = 1，当修改失败时（即 Affrows == 0）抛出异常。
每个实体只支持一个乐观锁，在属性前标记特性：[Column(IsVersion = true)] 即可。
无论是使用 FreeSql/FreeSql.Repository/FreeSql.DbContext，每次更新 version 的值都会增加 1
5、DbContext
dotnet add package FreeSql.DbContext
实现类似 EFCore 使用方法，跟踪对象状态，最终通过 SaveChanges 方法以事务的方式提交整段操作。
using (var ctx = new SongContext()) {
var song = new Song {BigNumber = “1000000000000000000”};
ctx.Songs.Add(song);
song.BigNumber = (BigInteger.Parse(song.BigNumber) + 1).ToString();
ctx.Songs.Update(song);
var tag = new Tag {
Name = “testaddsublist”,
Tags = new[] {
new Tag {Name = “sub1”},
new Tag {Name = “sub2”},
new Tag {
Name = “sub3”,
Tags = new[] {
new Tag {Name = “sub3_01”}
}
}
}
};
ctx.Tags.Add(tag);
ctx.SaveChanges();
}
public class Song {
[Column(IsIdentity = true)]
public int Id {get; set;}
public string BigNumber {get; set;}
[Column(IsVersion = true)] // 乐观锁
public long versionRow {get; set;}
}
public class Tag {
[Column(IsIdentity = true)]
public int Id {get; set;}
public int? Parent_id {get; set;}
public virtual Tag Parent {get; set;}
public string Name {get; set;}
public virtual ICollection<Tag> Tags {get; set;}
}
public class SongContext : DbContext {
public DbSet<Song> Songs {get; set;}
public DbSet<Tag> Tags {get; set;}
protected override void OnConfiguring(DbContextOptionsBuilder builder) {
builder.UseFreeSql(fsql);
}
}
导航属性
支持 1 对 1、1 对多、多对 1、多对多 的约定导航属性配置，主要用于表达式内部查询；
//OneToOne、ManyToOne
var t0 = fsql.Select<Tag>().Where(a => a.Parent.Parent.Name == “ 粤语 ”).ToList();
//OneToMany
var t1 = fsql.Select<Tag>().Where(a => a.Tags.AsSelect().Any(t => t.Parent.Id == 10)).ToList();
//ManyToMany
var t2 = fsql.Select<Song>().Where(s => s.Tags.AsSelect().Any(t => t.Name == “ 国语 ”)).ToList();
不朽篇
读写分离
数据库读写分离，本功能是客户端的读写分离行为，数据库服务器该怎么配置仍然那样配置，不受本功能影响，为了方便描术后面讲到的【读写分离】都是指客户端的功能支持。
各种数据库的读写方案不一，数据库端开启读写分离功能后，读写分离的实现大致分为以下几种：
1、nginx 代理，配置繁琐且容易出错；
2、中件间，如 MyCat，MySql 可以其他数据库怎么办？
3、在 client 端支持；
FreeSql 实现了第 3 种方案，支持一个【主库】多个【从库】，【从库】的查询策略为随机方式。
若某【从库】发生故障，将切换到其他可用【从库】，若已全部不可用则使用【主库】查询。
出现故障【从库】被隔离起来间隔性的检查可用状态，以待恢复。
IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, connstr)
.UseSlave(“connectionString1”, “connectionString2”)
// 使用从数据库，支持多个
.Build();
select.Where(a => a.Id == 1).ToOne();
// 读【从库】（默认）
select.Master().WhereId(a => a.Id == 1).ToOne();
// 强制读【主库】
下面是以前某项目的测试图片，以供参考，整个过程无感切换和恢复：
分区分表
FreeSql 提供 AsTable 分表的基础方法，GuidRepository 作为分存式仓储将实现了分表与分库（不支持跨服务器分库）的封装。
var logRepository = fsql.GetGuidRepository<Log>(null, oldname => $”{oldname}_{DateTime.Now.ToString(“YYYYMM”)}”);
上面我们得到一个日志仓储按年月分表，使用它 CURD 最终会操作 Log_201903 表。
合并两个仓储，实现分表下的联表查询：
fsql.GetGuidRepository<User>().Select.FromRepository(logRepository)
.LeftJoin<Log>(b => b.UserId == a.Id)
.ToList();
租户
1、按租户字段区分
FreeSql.Repository 现实了 filter（过滤与验证）功能，如：
var topicRepos = fsql.GetGuidRepository<Topic>(t => t.TerantId == 1);
使用 topicRepos 对象进行 CURD 方法：
在查询 / 修改 / 删除时附加此条件，从而达到不会修改 TerantId != 1 的数据；
在添加时，使用表达式验证数据的合法性，若不合法则抛出异常；
利用这个功能，我们可以很方便的实现数据分区，达到租户的目的。
2、按租户分表
FreeSql.Repository 现实了 分表功能，如：
var tenantId = 1;
var reposTopic = orm.GetGuidRepository<Topic>(null, oldname => $”{oldname}{tenantId}”);
上面我们得到一个仓储按租户分表，使用它 CURD 最终会操作 Topic_1 表。
3、按租户分库
与方案二相同，只是表存储的位置不同。
4、全局设置
通过注入的方式设置仓储类的全局过滤器。
public void ConfigureServices(IServiceCollection services) {
services.AddMvc();
services.AddSingleton<IFreeSql>(Fsql);
services.AddFreeRepository(filter => {
var tenantId = 求出当前租户 id;
filter
.Apply<ISoftDelete>(“softdelete”, a => a.IsDeleted == false)
.Apply<ITenant>(“tenant”, a => a.TenantId == tenantId)
}, this.GetType().Assembly
);
}
结束语
这次全方位介绍 FreeSql 的功能，只抽取了重要内容发布，由于功能实在太多不方便在一篇文章介绍祥尽。
我个人是非常想展开编写，将每个功能的设计和实现放大来介绍，但还是先希望得到更多人的关注，不然就是一台独角戏了。
gayhub: https://github.com/2881099/FreeSql，肯请献上宝贵的一星，谢谢！
