扯淡
这是一款轻量、高效的.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<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 + "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<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 = 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
