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

来源：我叫So

链接：www.cnblogs.com/so9527/p/5697549.html

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

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

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：

IQuery q = context.Query();

查询数据

基本查询

IQuery q = context.Query();

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 users = context.Query();

IQuery cities = context.Query();

IQuery provinces = context.Query();

//建立连接

IJoiningQuery user_city = users.InnerJoin(cities, (user, city) => user.CityId == city.Id);

IJoiningQuery 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 q = context.Query();

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 q = context.Query();

IGroupingQuery 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 表达式树的方式插入：

//返回主键 Idint id = (int)context.Insert(() => 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(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(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 ?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 + '1';

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

/*

 * DELETE `Users` FROM `Users` WHERE `Users`.`Id` = 1

 */

//批量删除

//删除所有不男不女的用户

context.Delete(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 q = context.Query();

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 = int.Parse('1'),//CAST(N'1' AS SIGNED)

    Int16_Parse = Int16.Parse('11'),//CAST(N'11' AS SIGNED)

    Long_Parse = long.Parse('2'),//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 = bool.Parse('1'),//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