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