mysql padleft_.NETCore 新型 ORM 功能介绍

简介

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.Where(a => a.Id == 1).First();

2、分页:第1页,每页20条

fsql.Select.Page(1, 20).ToList();

细节说明:SqlServer 2012 以前的版本,使用 row_number 分页;SqlServer 2012+ 版本,使用最新的 fetch next rows 分页;

3、IN

fsql.Select.Where(a => new { 1,2,3 }.Contains(a.Id)).ToList();

4、联表

fsql.Select.LeftJoin((a, b) => a.YyyId == b.Id).ToList();

5、Exists子表

fsql.Select.Where(a => fsql.Select(b => b.Id == a.YyyId).Any()).ToList();

6、GroupBy & Having

fsql.Select.GroupBy(a => new { a.CategoryId }).Having(a => a.Count > 2).ToList(a => new { a.Key, a.Count() });

7、指定字段查询

fsql.Select.Limit(10).ToList(a => a.Id);

fsql.Select.Limit(10).ToList(a => new { a.Id, a.Name });

fsql.Select.Limit(10).ToList(a => new Dto());

8、执行SQL返回实体

fsql.Ado.Query("select * from xxx");

fsql.Ado.Query("select * from xxx");

fsql.Ado.Query("select * from xxx");

插入

1、单条

fsql.Insert().AppendData(new Xxx()).ExecuteAffrows();

2、单条,返回自增值

fsql.Insert().AppendData(new Xxx()).ExecuteIdentity();

3、单条,返回插入的行(SqlServer 的 output 特性)

fsql.Insert().AppendData(new Xxx()).ExecuteInserted();

4、批量

fsql.Insert().AppendData(数组).ExecuteAffrows();

5、批量,返回插入的行(SqlServer 的 output 特性)

fsql.Insert().AppendData(数组).ExecuteInserted();

6、指定列

fsql.Insert().AppendData(new Xxx()).InsertColumns(a => a.Title).ExecuteAffrows();

fsql.Insert().AppendData(new Xxx()).InsertColumns(a => new { a.Id, a.Title}).ExecuteAffrows();

7、忽略列

fsql.Insert().AppendData(new Xxx()).IgnoreColumns(a => a.Title).ExecuteAffrows();

fsql.Insert().AppendData(new Xxx()).IgnoreColumns(a => new { a.Id, a.Title}).ExecuteAffrows();

8、事务

fsql.Insert().AppendData(new Xxx()).WithTransaction(事务对象).ExecuteAffrows();

更新

1、指定列

fsql.Update(1).Set(a => a.CreateTime, DateTime.Now).ExecuteAffrows();

2、累加,set clicks = clicks + 1

fsql.Update(1).Set(a => a.Clicks + 1).ExecuteAffrows();

3、保存

fsql.Update().SetSource(单个实体).ExecuteAffrows();

4、批量保存

fsql.Update().SetSource(数组).ExecuteAffrows();

5、忽略列

fsql.Update().SetSource(数组).IgnoreColumns(a => new { a.Clicks, a.CreateTime }).ExecuteAffrows();

6、更新条件

fsql.Update().SetSource(数组).Where(a => a.Clicks > 100).ExecuteAffrows();

7、事务

fsql.Update(1).Set(a => a.Clicks + 1).WithTransaction(事务对象).ExecuteAffrows();

删除

1、dywhere

主键值

new[] { 主键值1, 主键值2 }

Xxx对象

new[] { Xxx对象1, Xxx对象2 }

new { id = 1 }

fsql.Delete(new[] { 1, 2 }).ExecuteAffrows();

//DELETE FROM `xxx` WHERE (`Id` = 1 OR `Id` = 2)

fsql.Delete(new Xxx { Id = 1, Title = "test" }).ExecuteAffrows();

//DELETE FROM `xxx` WHERE (`Id` = 1)

fsql.Delete(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(new { id = 1 }).ExecuteAffrows();

//DELETE FROM `xxx` WHERE (`Id` = 1)

2、条件

fsql.Delete().Where(a => a.Id == 1).ExecuteAffrows();

//DELETE FROM `xxx` WHERE (`Id` = 1)

fsql.Delete().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().Where(item).ExecuteAffrows();

//DELETE FROM `xxx` WHERE (`Id` = 1)

var items = new List();

for (var a = 0; a < 10; a++) items.Add(new Xxx { Id = a + 1, Title = $"newtitle{a}", Clicks = a * 100 });

fsql.Delete().Where(items).ExecuteAffrows();

//DELETE FROM `xxx` WHERE (`Id` IN (1,2,3,4,5,6,7,8,9,10))

3、事务

fsql.Delete().Where(a => a.Id == 1).WithTransaction(事务对象).ExecuteAffrows();

初级篇

表达式

支持功能丰富的表达式函数解析,方便程序员在不了解数据库函数的情况下编写代码。这是 FreeSql 非常特色的功能之一,深入细化函数解析尽量做到满意,所支持的类型基本都可以使用对应的表达式函数,例如 日期、字符串、IN查询、数组(PostgreSQL的数组)、字典(PostgreSQL HStore)等等。

1、查找今天创建的数据

fsql.Delete().Where(a => a.CreateTime.Date == DateTime.Now.Date).ToList();

2、SqlServer 下随机获取记录

fsql.Delete().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

表达式

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 Tags { get; set; }

[Column(IsVersion = true)]

public long versionRow { get; set; }

}

2、在外部配置实体

fsql.CodeFirst

.ConfigEntity(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();

//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();

var userRepos = uow.GetRepository();

//上面两个仓储,由同一UnitOfWork uow 创建

//在此执行仓储操作

//这里不受异步方便影响

uow.Commit();

}

3、局部过滤器 + 数据验证

var topicRepository = fsql.GetGuidRepository(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 Tags { get; set; }

}

public class SongContext : DbContext {

public DbSet Songs { get; set; }

public DbSet Tags { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder builder) {

builder.UseFreeSql(fsql);

}

}

导航属性

支持 1对1、1对多、多对1、多对多 的约定导航属性配置,主要用于表达式内部查询;

//OneToOne、ManyToOne

var t0 = fsql.Select().Where(a => a.Parent.Parent.Name == "粤语").ToList();

//OneToMany

var t1 = fsql.Select().Where(a => a.Tags.AsSelect().Any(t => t.Parent.Id == 10)).ToList();

//ManyToMany

var t2 = fsql.Select().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();

//强制读【主库】

下面是以前某项目的测试图片,以供参考,整个过程无感切换和恢复:

fc90419060bd3db3d0d09fa749bbc627.png

ccefa93a12b2aed91cfe12f563b2ede8.png

分区分表

FreeSql 提供 AsTable 分表的基础方法,GuidRepository 作为分存式仓储将实现了分表与分库(不支持跨服务器分库)的封装。

var logRepository = fsql.GetGuidRepository(null, oldname => $"{oldname}_{DateTime.Now.ToString("YYYYMM")}");

上面我们得到一个日志仓储按年月分表,使用它 CURD 最终会操作 Log_201903 表。

合并两个仓储,实现分表下的联表查询:

fsql.GetGuidRepository().Select.FromRepository(logRepository)

.LeftJoin(b => b.UserId == a.Id)

.ToList();

租户

1、按租户字段区分

FreeSql.Repository 现实了 filter(过滤与验证)功能,如:

var topicRepos = fsql.GetGuidRepository(t => t.TerantId == 1);

使用 topicRepos 对象进行 CURD 方法:

在查询/修改/删除时附加此条件,从而达到不会修改 TerantId != 1 的数据;

在添加时,使用表达式验证数据的合法性,若不合法则抛出异常;

利用这个功能,我们可以很方便的实现数据分区,达到租户的目的。

2、按租户分表

FreeSql.Repository 现实了 分表功能,如:

var tenantId = 1;

var reposTopic = orm.GetGuidRepository(null, oldname => $"{oldname}{tenantId}");

上面我们得到一个仓储按租户分表,使用它 CURD 最终会操作 Topic_1 表。

3、按租户分库

与方案二相同,只是表存储的位置不同。

4、全局设置

通过注入的方式设置仓储类的全局过滤器。

public void ConfigureServices(IServiceCollection services) {

services.AddMvc();

services.AddSingleton(Fsql);

services.AddFreeRepository(filter => {

var tenantId = 求出当前租户id;

filter

.Apply("softdelete", a => a.IsDeleted == false)

.Apply("tenant", a => a.TenantId == tenantId)

}, this.GetType().Assembly

);

}

结束语

这次全方位介绍 FreeSql 的功能,只抽取了重要内容发布,由于功能实在太多不方便在一篇文章介绍祥尽。

我个人是非常想展开编写,将每个功能的设计和实现放大来介绍,但还是先希望得到更多人的关注,不然就是一台独角戏了。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值