扯淡

这是一款轻量、高效的.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
     
       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,ProvinceNameuser_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，地址：。

相关介绍：

性能测试：