[开源].NET高性能框架Chloe.ORM-完美支持SQLite

时间:2021-12-10 13:22:01

扯淡

这是一款轻量、高效的.NET C#数据库访问框架(ORM)。查询接口借鉴 Linq(但不支持 Linq)。借助 lambda 表达式,可以完全用面向对象的方式就能轻松执行多表连接查询、分组查询、聚合查询、插入数据、批量删除和更新等操作。

多数据库支持,已然成为 ORM 的标配。继开发了 Chloe 的 .NET Core 版本后,Chloe 继续她的多数据库支持之路。上周花了些时间学习了 SQLite,现在已经支持 SQLite 数据库。

导航

Chloe.ORM

事前准备

实体:

public enum Gender
{
    Man = 1,
    Woman
}

[Table("Users")]
public class User
{
    [Column(IsPrimaryKey = true)]
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public Gender? Gender { get; set; }
    public int? Age { get; set; }
    public int? CityId { get; set; }
    public DateTime? OpTime { get; set; }
}

public class City
{
    [Column(IsPrimaryKey = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    public int ProvinceId { get; set; }
}

public class Province
{
    [Column(IsPrimaryKey = true)]
    public int Id { get; set; }
    public string Name { get; set; }
}

因为框架不依赖具体的数据库驱动,所以得先建个 SQLiteConnectionFactory 类,实现 IDbConnectionFactory 接口:

public class SQLiteConnectionFactory : IDbConnectionFactory
{
    string _connString = null;
    public SQLiteConnectionFactory(string connString)
    {
        this._connString = connString;
    }
    public IDbConnection CreateConnection()
    {
        SQLiteConnection conn = new SQLiteConnection(this._connString);
        return conn;
    }
}

然后通过构造函数注入的方式创建一个 DbContext:

SQLiteContext context = new SQLiteContext(new SQLiteConnectionFactory(DbHelper.ConnectionString));

再创建一个 IQuery<T>:

IQuery<User> q = context.Query<User>();

查询数据

基本查询

IQuery<User> q = context.Query<User>();

q.Where(a => a.Id == 1).FirstOrDefault();
/*
 * SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] FROM [Users] AS [Users] WHERE [Users].[Id] = 1 LIMIT 1 OFFSET 0
 */

//可以选取指定的字段
q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
 * SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name] FROM [Users] AS [Users] WHERE [Users].[Id] = 1 LIMIT 1 OFFSET 0
 */

//分页
q.Where(a => a.Id > 0).OrderBy(a => a.Age).Skip(1).Take(999).ToList();
/*
 * SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime] FROM [Users] AS [Users] WHERE [Users].[Id] > 0 ORDER BY [Users].[Age] ASC LIMIT 999 OFFSET 1
 */

连接查询

IQuery<User> users = context.Query<User>();
IQuery<City> cities = context.Query<City>();
IQuery<Province> provinces = context.Query<Province>();

//建立连接
IJoiningQuery<User, City> user_city = users.InnerJoin(cities, (user, city) => user.CityId == city.Id);
IJoiningQuery<User, City, Province> user_city_province = user_city.InnerJoin(provinces, (user, city, province) => city.ProvinceId == province.Id);

//查出一个用户及其隶属的城市和省份的所有信息
user_city_province.Select((user, city, province) => new { User = user, City = city, Province = province }).Where(a => a.User.Id == 1).ToList();
/*
 * SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Gender] AS [Gender],[Users].[Age] AS [Age],[Users].[CityId] AS [CityId],[Users].[OpTime] AS [OpTime],[City].[Id] AS [Id0],[City].[Name] AS [Name0],[City].[ProvinceId] AS [ProvinceId],[Province].[Id] AS [Id1],[Province].[Name] AS [Name1] FROM [Users] AS [Users] INNER JOIN [City] AS [City] ON [Users].[CityId] = [City].[Id] INNER JOIN [Province] AS [Province] ON [City].[ProvinceId] = [Province].[Id] WHERE [Users].[Id] = 1
 */

//也可以只获取指定的字段信息:UserId,UserName,CityName,ProvinceName
user_city_province.Select((user, city, province) => new { UserId = user.Id, UserName = user.Name, CityName = city.Name, ProvinceName = province.Name }).Where(a => a.UserId == 1).ToList();
/*
 * SELECT [Users].[Id] AS [UserId],[Users].[Name] AS [UserName],[City].[Name] AS [CityName],[Province].[Name] AS [ProvinceName] FROM [Users] AS [Users] INNER JOIN [City] AS [City] ON [Users].[CityId] = [City].[Id] INNER JOIN [Province] AS [Province] ON [City].[ProvinceId] = [Province].[Id] WHERE [Users].[Id] = 1
 */

聚合查询

IQuery<User> q = context.Query<User>();

q.Select(a => AggregateFunctions.Count()).First();
/*
 * SELECT COUNT(1) AS [C] FROM [Users] AS [Users] LIMIT 1 OFFSET 0
 */

q.Select(a => new { Count = AggregateFunctions.Count(), LongCount = AggregateFunctions.LongCount(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Average = AggregateFunctions.Average(a.Age) }).First();
/*
 * SELECT COUNT(1) AS [Count],COUNT(1) AS [LongCount],CAST(SUM([Users].[Age]) AS INTEGER) AS [Sum],CAST(MAX([Users].[Age]) AS INTEGER) AS [Max],CAST(MIN([Users].[Age]) AS INTEGER) AS [Min],CAST(AVG([Users].[Age]) AS REAL) AS [Average] FROM [Users] AS [Users] LIMIT 1 OFFSET 0
 */

var count = q.Count();
/*
 * SELECT COUNT(1) AS [C] FROM [Users] AS [Users]
 */

var longCount = q.LongCount();
/*
 * SELECT COUNT(1) AS [C] FROM [Users] AS [Users]
 */

var sum = q.Sum(a => a.Age);
/*
 * SELECT CAST(SUM([Users].[Age]) AS INTEGER) AS [C] FROM [Users] AS [Users]
 */

var max = q.Max(a => a.Age);
/*
 * SELECT CAST(MAX([Users].[Age]) AS INTEGER) AS [C] FROM [Users] AS [Users]
 */

var min = q.Min(a => a.Age);
/*
 * SELECT CAST(MIN([Users].[Age]) AS INTEGER) AS [C] FROM [Users] AS [Users]
 */

var avg = q.Average(a => a.Age);
/*
 * SELECT CAST(AVG([Users].[Age]) AS REAL) AS [C] FROM [Users] AS [Users]
 */

分组查询

IQuery<User> q = context.Query<User>();

IGroupingQuery<User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age);

g = g.Having(a => a.Age > 1 && AggregateFunctions.Count() > 0);

g.Select(a => new { a.Age, Count = AggregateFunctions.Count(), Sum = AggregateFunctions.Sum(a.Age), Max = AggregateFunctions.Max(a.Age), Min = AggregateFunctions.Min(a.Age), Avg = AggregateFunctions.Average(a.Age) }).ToList();
/*
 * SELECT [Users].[Age] AS [Age],COUNT(1) AS [Count],CAST(SUM([Users].[Age]) AS INTEGER) AS [Sum],CAST(MAX([Users].[Age]) AS INTEGER) AS [Max],CAST(MIN([Users].[Age]) AS INTEGER) AS [Min],CAST(AVG([Users].[Age]) AS REAL) AS [Avg] FROM [Users] AS [Users] WHERE [Users].[Id] > 0 GROUP BY [Users].[Age] HAVING ([Users].[Age] > 1 AND COUNT(1) > 0)
 */

插入数据

方式1

以 lambda 表达式树的方式插入:

//返回主键 Id
int id = (int)context.Insert<User>(() => new User() { Name = "lu", Age = 18, Gender = Gender.Man, CityId = 1, OpTime = DateTime.Now });
/*
 * INSERT INTO [Users]([Name],[Age],[Gender],[CityId],[OpTime]) VALUES('lu',18,1,1,DATETIME('NOW','LOCALTIME'));SELECT LAST_INSERT_ROWID()
 */

方式2

以实体的方式插入:

User user = new User();
user.Name = "lu";
user.Age = 18;
user.Gender = Gender.Man;
user.CityId = 1;
user.OpTime = DateTime.Now;

//会自动将自增 Id 设置到 user 的 Id 属性上
user = context.Insert(user);
/*
 * String @P_0 = 'lu';
   Gender @P_1 = Man;
   Int32 @P_2 = 18;
   Int32 @P_3 = 1;
   DateTime @P_4 = '2016/8/6 22:03:42';
   INSERT INTO [Users]([Name],[Gender],[Age],[CityId],[OpTime]) VALUES(@P_0,@P_1,@P_2,@P_3,@P_4);SELECT LAST_INSERT_ROWID()
 */

更新数据

方式1

以 lambda 表达式树的方式更新:

context.Update<User>(a => new User() { Name = a.Name, Age = a.Age + 100, Gender = Gender.Man, OpTime = DateTime.Now }, a => a.Id == 1);
/*
 * UPDATE [Users] SET [Name]=[Users].[Name],[Age]=([Users].[Age] + 100),[Gender]=1,[OpTime]=DATETIME('NOW','LOCALTIME') WHERE [Users].[Id] = 1
 */

//批量更新
//给所有女性朋友年轻 10 岁
context.Update<User>(a => new User() { Age = a.Age - 10, OpTime = DateTime.Now }, a => a.Gender == Gender.Woman);
/*
 * UPDATE [Users] SET [Age]=([Users].[Age] - 10),[OpTime]=DATETIME('NOW','LOCALTIME') WHERE [Users].[Gender] = 2
 */

方式2

以实体的方式更新:

User user = new User();
user.Id = 1;
user.Name = "lu";
user.Age = 28;
user.Gender = Gender.Man;
user.OpTime = DateTime.Now;

context.Update(user); //会更新所有映射的字段
/*
 * String @P_0 = 'lu';
   Gender @P_1 = Man;
   Int32 @P_2 = 28;
   Nullable<Int32> @P_3 = NULL;
   DateTime @P_4 = '2016/8/6 22:05:02';
   Int32 @P_5 = 1;
   UPDATE [Users] SET [Name]=@P_0,[Gender]=@P_1,[Age]=@P_2,[CityId]=@P_3,[OpTime]=@P_4 WHERE [Users].[Id] = @P_5
 */

/*
 * 支持只更新属性值已变的属性
 */

context.TrackEntity(user);//在上下文中跟踪实体
user.Name = user.Name + "";
context.Update(user);//这时只会更新被修改的字段
/*
 * String @P_0 = 'lu1';
   Int32 @P_1 = 1;
   UPDATE [Users] SET [Name]=@P_0 WHERE [Users].[Id] = @P_1
 */

删除数据

方式1

以 lambda 表达式树的方式删除:

context.Delete<User>(a => a.Id == 1);
/*
 * DELETE FROM [Users] WHERE [Users].[Id] = 1
 */

//批量删除
//删除所有不男不女的用户
context.Delete<User>(a => a.Gender == null);
/*
 * DELETE FROM [Users] WHERE [Users].[Gender] IS NULL
 */

方式2

以实体的方式删除:

User user = new User();
user.Id = 1;
context.Delete(user);
/*
 * Int32 @P_0 = 1;
   DELETE FROM [Users] WHERE [Users].[Id] = @P_0
 */

支持函数

IQuery<User> q = context.Query<User>();

var space = new char[] { ' ' };

DateTime startTime = DateTime.Now;
DateTime endTime = DateTime.Now.AddDays(1);

var ret = q.Select(a => new
{
    Id = a.Id,

    String_Length = (int?)a.Name.Length,//LENGTH([Users].[Name])
    Substring = a.Name.Substring(0),//SUBSTR([Users].[Name],0 + 1)
    Substring1 = a.Name.Substring(1),//SUBSTR([Users].[Name],1 + 1)
    Substring1_2 = a.Name.Substring(1, 2),//SUBSTR([Users].[Name],1 + 1,2)
    ToLower = a.Name.ToLower(),//LOWER([Users].[Name])
    ToUpper = a.Name.ToUpper(),//UPPER([Users].[Name])
    IsNullOrEmpty = string.IsNullOrEmpty(a.Name),//CASE WHEN ([Users].[Name] IS NULL OR [Users].[Name] = '') THEN 1 ELSE 0 END = 1
    Contains = (bool?)a.Name.Contains("s"),//[Users].[Name] LIKE '%' || 's' || '%'
    StartsWith = (bool?)a.Name.StartsWith("s"),//[Users].[Name] LIKE 's' || '%'
    EndsWith = (bool?)a.Name.EndsWith("s"),//[Users].[Name] LIKE '%' || 's'
    Trim = a.Name.Trim(),//TRIM([Users].[Name])
    TrimStart = a.Name.TrimStart(space),//LTRIM([Users].[Name])
    TrimEnd = a.Name.TrimEnd(space),//RTRIM([Users].[Name])

    DiffYears = DbFunctions.DiffYears(startTime, endTime),//(CAST(STRFTIME('%Y',@P_0) AS INTEGER) - CAST(STRFTIME('%Y',@P_1) AS INTEGER))
    DiffMonths = DbFunctions.DiffMonths(startTime, endTime),//((CAST(STRFTIME('%Y',@P_0) AS INTEGER) - CAST(STRFTIME('%Y',@P_1) AS INTEGER)) * 12 + (CAST(STRFTIME('%m',@P_0) AS INTEGER) - CAST(STRFTIME('%m',@P_1) AS INTEGER)))
    DiffDays = DbFunctions.DiffDays(startTime, endTime),//CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) AS INTEGER)
    DiffHours = DbFunctions.DiffHours(startTime, endTime),//CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 24 AS INTEGER)
    DiffMinutes = DbFunctions.DiffMinutes(startTime, endTime),//CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 1440 AS INTEGER)
    DiffSeconds = DbFunctions.DiffSeconds(startTime, endTime),//CAST((JULIANDAY(@P_0) - JULIANDAY(@P_1)) * 86400 AS INTEGER)
    //DiffMilliseconds = DbFunctions.DiffMilliseconds(startTime, endTime),//不支持 Millisecond
    //DiffMicroseconds = DbFunctions.DiffMicroseconds(startTime, endTime),//不支持 Microseconds

    AddYears = startTime.AddYears(1),//DATETIME(@P_0,'+' || 1 || ' years')
    AddMonths = startTime.AddMonths(1),//DATETIME(@P_0,'+' || 1 || ' months')
    AddDays = startTime.AddDays(1),//DATETIME(@P_0,'+' || 1 || ' days')
    AddHours = startTime.AddHours(1),//DATETIME(@P_0,'+' || 1 || ' hours')
    AddMinutes = startTime.AddMinutes(2),//DATETIME(@P_0,'+' || 2 || ' minutes')
    AddSeconds = startTime.AddSeconds(120),//DATETIME(@P_0,'+' || 120 || ' seconds')
    //AddMilliseconds = startTime.AddMilliseconds(2000),//不支持

    Now = DateTime.Now,//DATETIME('NOW','LOCALTIME')
    UtcNow = DateTime.UtcNow,//DATETIME()This method can not pass parameters
    Today = DateTime.Today,//DATE('NOW','LOCALTIME')
    Date = DateTime.Now.Date,//DATE('NOW','LOCALTIME')
    Year = DateTime.Now.Year,//CAST(STRFTIME('%Y',DATETIME('NOW','LOCALTIME')) AS INTEGER)
    Month = DateTime.Now.Month,//CAST(STRFTIME('%m',DATETIME('NOW','LOCALTIME')) AS INTEGER)
    Day = DateTime.Now.Day,//CAST(STRFTIME('%d',DATETIME('NOW','LOCALTIME')) AS INTEGER)
    Hour = DateTime.Now.Hour,//CAST(STRFTIME('%H',DATETIME('NOW','LOCALTIME')) AS INTEGER)
    Minute = DateTime.Now.Minute,//CAST(STRFTIME('%M',DATETIME('NOW','LOCALTIME')) AS INTEGER)
    Second = DateTime.Now.Second,//CAST(STRFTIME('%S',DATETIME('NOW','LOCALTIME')) AS INTEGER)
    Millisecond = DateTime.Now.Millisecond,//@P_2 直接计算 DateTime.Now.Millisecond 的值
    DayOfWeek = DateTime.Now.DayOfWeek,//CAST(STRFTIME('%w',DATETIME('NOW','LOCALTIME')) AS INTEGER)

    Byte_Parse = "),//CAST('1' AS INTEGER)
    Int_Parse = "),//CAST('1' AS INTEGER)
    Int16_Parse = Int16.Parse(""),//CAST('11' AS INTEGER)
    Long_Parse = "),//CAST('2' AS INTEGER)
    Double_Parse = double.Parse("3.1"),//CAST('3.1' AS REAL)
    Float_Parse = float.Parse("4.1"),//CAST('4.1' AS REAL)
    //Decimal_Parse = decimal.Parse("5"),//不支持
    //Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),//不支持 'D544BC4C-739E-4CD3-A3D3-7BF803FCE179'

    Bool_Parse = "),//CAST('1' AS INTEGER)
    DateTime_Parse = DateTime.Parse("2014-01-01"),//DATETIME('2014-01-01')
}).ToList();

坎坎坷坷

SQLite 也是一个关系型数据库,之前开发 MySql Provider 的时候已经积累了些经验,因此,这回开发 SQLite 的 Provider 几乎不费吹灰之力。不过 SQLite 稍微比 MySql “恶心”那么一点:

  1. MySql 不支持 Full Join,SQLite 不支持 Full Join 的同时连 Right Join 也不支持,这是为啥呢?不过不要紧,既然不支持 Right Join,那开发的时候将本是放在 Right 一边的表用 Left Join 去连接其他表就好。支持 Right Join 应该不是难事,只是很奇怪 SQLite 为啥不支持呢!
  2. 支持的函数相对比 SqlServer 和 MySql 要少些,比如,获取两个日期的年差、月差、时差等函数,因为 SQLite 只支持日差函数,因此为了支持 Chloe 的 DbFunctions.DiffYears、DbFunctions.DiffMonths、DbFunctions.DiffHours 等方法,得将其翻译成既长又奇葩的语句- -。

当然,还有其它的不同,但都有变通的方式支持,就不一一列举了。

结语

从最初只支持 SqlServer 到现在支持3种数据库,瞬间感觉 Chloe “高大上”了许多。多数据库支持这路还很长,对于其他数据库的支持,只是时间问题。接下来的发展目标是...待定- -。在开源的一个多月里,要支持这支持那,每周得去学习,同时要不断的维护代码(咱得对得起关注 Chloe 的同学- -),真心有点儿小困,头发又少了很多555,接下来要好好休息放松一下先了。

文中代码都同步在 GitHub,地址:https://github.com/shuxinqin/Chloe/blob/master/src/DotNet/ChloeDemo/SQLiteDemo.cs。Demo 项目中已经包含 SQLite 驱动和数据文件,下载即可运行。

Chloe.ORM 完全开源,遵循 Apache2.0 协议,托管于 GitHub,地址:https://github.com/shuxinqin/Chloe

相关介绍:http://www.cnblogs.com/so9527/p/5636216.html
性能测试:http://www.cnblogs.com/so9527/p/5674498.html