.NET高性能框架Chloe.ORM-完美支持MySql

扯淡

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

支持主流数据库，似乎成了 ORM 的标配，Chloe 正在往这方向发展。Chloe 成型之初只支持 SqlServer，在很长的一段时间内，一直只是在维稳。经过公司项目中实战了一段时间，目前框架架构和功能都已经稳定，该支持的都已支持。因此，继上周做了性能测试后，花了点时间学习了下 MySql，然后花了些时间做了 MySql 的 Provider，现已支持 MySql 数据库。

导航

• Chloe.ORM
  • 事前准备
  • 查询数据
    • 基本查询
    • 连接查询
    • 聚合函数
    • 分组查询
  • 插入数据
  • 更新数据
  • 删除数据
  • 支持函数
• 坎坎坷坷
• 结语

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; }
}

建个 MySqlConnectionFactory 类，实现 IDbConnectionFactory 接口：

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

创建一个 DbContext：

MySqlContext context = new MySqlContext(new MySqlConnectionFactory(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 0,1
 */

//可以选取指定的字段
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 0,1
 */

//分页
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 1,999
 */

连接查询

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

//查出一个用户及其隶属的城市和省份的所有信息
var view = 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 0,1
 */

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`,SUM(`Users`.`Age`) AS `Sum`,MAX(`Users`.`Age`) AS `Max`,MIN(`Users`.`Age`) AS `Min`,AVG(`Users`.`Age`) AS `Average` FROM `Users` AS `Users` LIMIT 0,1
 */

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 SUM(`Users`.`Age`) AS `C` FROM `Users` AS `Users`
 */

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

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

var avg = q.Average(a => a.Age);
/*
 * SELECT AVG(`Users`.`Age`) 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`,SUM(`Users`.`Age`) AS `Sum`,MAX(`Users`.`Age`) AS `Max`,MIN(`Users`.`Age`) AS `Min`,AVG(`Users`.`Age`) 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(N'lu',18,1,1,NOW());SELECT @@IDENTITY
 */

方式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/7/24 21:33:58';
   INSERT INTO `Users`(`Name`,`Gender`,`Age`,`CityId`,`OpTime`) VALUES(?P_0,?P_1,?P_2,?P_3,?P_4);SELECT @@IDENTITY
 */

更新数据

方式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`=NOW() 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`=NOW() 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/7/24 21:35:14';
   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 `Users` FROM `Users` WHERE `Users`.`Id` = 1
 */

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

方式2

以实体的方式删除：

User user = new User();
user.Id = 1;
context.Delete(user);
/*
 * Int32 ?P_0 = 1;
   DELETE `Users` 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),//SUBSTRING(`Users`.`Name`,0 + 1,LENGTH(`Users`.`Name`))
    Substring1 = a.Name.Substring(1),//SUBSTRING(`Users`.`Name`,1 + 1,LENGTH(`Users`.`Name`))
    Substring1_2 = a.Name.Substring(1, 2),//SUBSTRING(`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` = N'') THEN 1 ELSE 0 END = 1
    Contains = (bool?)a.Name.Contains("s"),//`Users`.`Name` LIKE CONCAT('%',N's','%')
    Trim = a.Name.Trim(),//TRIM(`Users`.`Name`)
    TrimStart = a.Name.TrimStart(space),//LTRIM(`Users`.`Name`)
    TrimEnd = a.Name.TrimEnd(space),//RTRIM(`Users`.`Name`)
    StartsWith = (bool?)a.Name.StartsWith("s"),//`Users`.`Name` LIKE CONCAT(N's','%')
    EndsWith = (bool?)a.Name.EndsWith("s"),//`Users`.`Name` LIKE CONCAT('%',N's')

    DiffYears = DbFunctions.DiffYears(startTime, endTime),//TIMESTAMPDIFF(YEAR,?P_0,?P_1)
    DiffMonths = DbFunctions.DiffMonths(startTime, endTime),//TIMESTAMPDIFF(MONTH,?P_0,?P_1)
    DiffDays = DbFunctions.DiffDays(startTime, endTime),//TIMESTAMPDIFF(DAY,?P_0,?P_1)
    DiffHours = DbFunctions.DiffHours(startTime, endTime),//TIMESTAMPDIFF(HOUR,?P_0,?P_1)
    DiffMinutes = DbFunctions.DiffMinutes(startTime, endTime),//TIMESTAMPDIFF(MINUTE,?P_0,?P_1)
    DiffSeconds = DbFunctions.DiffSeconds(startTime, endTime),//TIMESTAMPDIFF(SECOND,?P_0,?P_1)
    //DiffMilliseconds = DbFunctions.DiffMilliseconds(startTime, endTime),//MySql 不支持 Millisecond
    //DiffMicroseconds = DbFunctions.DiffMicroseconds(startTime, endTime),//ex

    Now = DateTime.Now,//NOW()
    UtcNow = DateTime.UtcNow,//UTC_TIMESTAMP()
    Today = DateTime.Today,//CURDATE()
    Date = DateTime.Now.Date,//CURDATE()
    Year = DateTime.Now.Year,//YEAR(NOW())
    Month = DateTime.Now.Month,//MONTH(NOW())
    Day = DateTime.Now.Day,//DAY(NOW())
    Hour = DateTime.Now.Hour,//HOUR(NOW())
    Minute = DateTime.Now.Minute,//MINUTE(NOW())
    Second = DateTime.Now.Second,//SECOND(NOW())
    Millisecond = DateTime.Now.Millisecond,//?P_2 AS `Millisecond`
    DayOfWeek = DateTime.Now.DayOfWeek,//(DAYOFWEEK(NOW()) - 1)

    //Byte_Parse = byte.Parse("1"),//不支持
    Int_Parse = "),//CAST(N'1' AS SIGNED)
    Int16_Parse = Int16.Parse(""),//CAST(N'11' AS SIGNED)
    Long_Parse = "),//CAST(N'2' AS SIGNED)
    //Double_Parse = double.Parse("3"),//N'3' 不支持，否则可能会成为BUG
    //Float_Parse = float.Parse("4"),//N'4' 不支持，否则可能会成为BUG
    //Decimal_Parse = decimal.Parse("5"),//不支持
    Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),//N'D544BC4C-739E-4CD3-A3D3-7BF803FCE179'

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

坎坎坷坷

MySql 和 SqlServer 同为关系型数据库，两者大同小异。相对来说，MySql 稍微简单了些。比如在 SqlServer 里，select 1>0 这种语法是不支持的，但在 MySql 里却可以，对于 >、<、= 等等的一些比较运算符，是可以作为有返回值结果返回(1或0)，这给我开发 MySql Provider 方便了许多，因此，MySql Provider 较 SqlServer Provider 的代码量也少了些。但两者的一些差异也给我带来不少麻烦！

因为本来就对 MySql 零认知，即使是一点点的不同，也挺折腾的。比如，MySql 不支持 Full Join，我完全不知道。开发调试的时候报错，一直以为是语法问题，检查了一遍又一遍的语法，感觉就是没错啊，但就是没法执行，始终不知道是怎么回事！将 Full Join 改成 Inner Join 或 Left Join 又可以执行了，愁死我了，因为压根没想过也觉得不可能是因为 MySql 不支持 Full Join 的原因，折腾了我半天！

还有，因为 Chloe 内部对数据类型要求很严谨，很依赖数据的 CAST 类型转换语法，在 SqlServer 里，CAST 的目标类型只要是 SqlServer 支持的类型都可以。但到了 MySql 就不一样了，转换的目标类型就限定几个(CHAR、DATE、TIME、DATETIME、DECIMAL、SIGNED 和 UNSIGNED)，如果需要将一个 Int 类型转换成 Double 类型怎么办？貌似还真没办法，我也是没辙...因为，如果一个数据库字段是 Int 类型，在 SqlServer 的 DataReader 里调用不是 GetInt 方法，而是如 GetDouble、GetInt64 这类的强类型方法会报错，不允许这样调用。我的思维定型在 SqlServer 上了，所以在 MySql 的 CAST 转换上折腾了好久！后来不知道那根筋膨胀，突然想到在 MySql 的 DataReader 上会不会就可以调用 GetDouble、GetInt64 呢？尝试了下，还真可以，豁然开朗。数据库类型是 Int 类型，可以用 GetDouble、GetInt64 获取值，这真的万万想不到- -。不过 MySql 的 DataReader 为什么可以这样玩呢？好奇的我想知道为什么，网上应该有 MySql.Data.dll 的源码吧！于是上 GitHub 搜罗了一番，还真有！不得不佩服开源力量的强大！看了下它对强类型方法实现，大概都是类似下面这样：

public override Int64 GetInt64(int i)
{
    IMySqlValue v = GetFieldValue(i, true);
    if (v is MySqlInt64)
        return ((MySqlInt64)v).Value;

    return (Int64)ChangeType(v, i, typeof(Int64));
}

看到了吗？原来它做了个判断，如果获取的类型不是 Int64 类型数据，它会自己帮我们做类型转换。我估计是因为 MySql CAST 支持转换的目标类型有限，DataReader 才不得已做了这么一个措施！看起来很人性化，但从另一方面来说有点不严谨(怪我，被 SqlServer 影响太深)！

这些小问题看起来不值一提，但确实花了好多时间解决它！细节，真是搞死咱这些小白了！

结语

由于，从一开始就给 Chloe.ORM 定了要支持多数据库的目标，因此，在设计框架的时候，把 SqlGenerator 给抽象了出来，所以，这次做 MySql 的 Provider 没动任何 Chloe.dll 项目里的代码，只是增加了一个 Chloe.MySql.dll 扩展。学习 MySql 并深入理解 MySql 花了我挺长时间，但开发 Chloe.MySql.dll 却只用了两个多小时。由于本来就对 MySql 很陌生，在接下来的一段时间内，我会继续对 Chloe.MySql 做测试，保证“零BUG”。

Chloe.ORM 还小，要走的路还很长。近期貌似 .NET Core 很火，接下来的发展目标是支持 .NET Core。对 Chloe.ORM 项目感兴趣的同学，敬请期待！

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