UDN-企业互联网技术人气社区

板块导航

浏览  : 685
回复  : 1

[讨论交流] .NET数据库访问框架Chloe.ORM

[复制链接]
开花包的头像 楼主
  扯淡

  13年毕业之际,进入第一家公司实习,接触了 EntityFramework,当时就觉得这东西太牛了,访问数据库都可以做得这么轻松、优雅!毕竟那时还年轻,没见过世面。工作之前为了拿个实习机会混个工作证明,匆匆忙忙学了两个月的 C#,就这样,稀里糊涂的做了程序员,从此走上了一条不归路。那会也只知道 SqlHelper,DataTable。ORM?太高大上,没听说过。虽然在第一家公司只呆了两个月,但让我认识了 EntityFramework,从此也走上了 ORM 的不归路...纯纯的实体,增改删超级简单,查询如行云流水,真心没理由抗拒!以至于后来进入第二家公司做开发极其不适应,因为他们没用 EF,也不用类 linq 的 ORM,他们有自己数据库访问框架。那套东西实体设计复杂,支持的功能少,查询条件还依赖字符串,开发容错率太低,DB操作入口接口设计也很重,里面方法不下60个,看心凉,用心累!那时,好怀念 EF~在新公司工作的时间内,来回都是增改页面,做增删查改,修复BUG,多少有点枯燥乏味,渐渐感觉编码能力提升太慢。同时鉴于用公司的 ORM 也不是很顺手,于是,萌生了自己写 ORM 的念头,再然后...Chloe.ORM 面世了~

  Chloe.ORM

  Chloe 查询接口设计借(zhao)鉴(ban) linq,但不支持 linq。开发之前,我给我的 ORM 查询条件接口定义一定要支持lambda表达式(潮流、趋势,在这不讨论表达式树的性能)。开发之初,也有自己设计过查询接口,想了一套又一套,始终没 linq 设计的接口方便,后来,不想了,直接抄 linq,不解释!前人如此伟大设计,不用真对不起他们,我要站在他们的肩膀上!

  先看下 IDbContext 接口:

  1. public interface IDbContext : IDisposable
  2. {
  3.     IDbSession CurrentSession { get; }

  4.     IQuery<T> Query<T>() where T : new();
  5.     IEnumerable<T> SqlQuery<T>(string sql, params DbParam[] parameters) where T : new();

  6.     T Insert<T>(T entity);
  7.     object Insert<T>(Expression<Func<T>> body);

  8.     int Update<T>(T entity);
  9.     int Update<T>(Expression<Func<T, T>> body, Expression<Func<T, bool>> condition);

  10.     int Delete<T>(T entity);
  11.     int Delete<T>(Expression<Func<T, bool>> condition);

  12.     void TrackEntity(object entity);
  13. }
复制代码


  Chloe 操作入口是 IDbContext。IDbContext 仅有两个 Query、两个 Insert、两个 Update 、两个 Delete 和一个 TrackEntity 方法,以及一个 CurrentDbSession 的属性,设计很简单,但绝对能满足81%的需求(多一点满足,多一分热爱)!

  这篇文章,主要介绍 Query 接口使用。

  实体:

  1. public enum Gender
  2. {
  3.     Man = 1,
  4.     Woman
  5. }

  6. [TableAttribute("Users")]
  7. public class User
  8. {
  9.     [Column(IsPrimaryKey = true)]
  10.     [AutoIncrementAttribute]
  11.     public int Id { get; set; }
  12.     public string Name { get; set; }
  13.     public Gender? Gender { get; set; }
  14.     public int? Age { get; set; }
  15.     public int? CityId { get; set; }
  16.     public DateTime? OpTime { get; set; }
  17. }

  18. public class City
  19. {
  20.     [Column(IsPrimaryKey = true)]
  21.     public int Id { get; set; }
  22.     public string Name { get; set; }
  23.     public int ProvinceId { get; set; }
  24. }

  25. public class Province
  26. {
  27.     [Column(IsPrimaryKey = true)]
  28.     public int Id { get; set; }
  29.     public string Name { get; set; }
  30. }
复制代码


  首先,创建一个 DbContext:

  
  1. IDbContext context = new MsSqlContext(DbHelper.ConnectionString);
复制代码


  再创建一个 IQuery:

 
  1.  IQuery q = context.Query();
复制代码


  基本查询

  1. IQuery<User> q = context.Query<User>();
  2. q.Where(a => a.Id > 0).FirstOrDefault();
  3. q.Where(a => a.Id > 0).ToList();
  4. q.Where(a => a.Id > 0).OrderBy(a => a.Age).ToList();
  5. q.Where(a => a.Id > 0).Take(999).OrderBy(a => a.Age).ToList();

  6. //分页。避免生成的 sql 语句太长占篇幅,只选取 Id 和 Name 两个字段
  7. q.Where(a => a.Id > 0).OrderBy(a => a.Age).ThenByDesc(a => a.Id).Select(a => new { a.Id, a.Name }).Skip(1).Take(999).ToList();
  8. /*
  9. * SELECT TOP (999) [T].[Id] AS [Id],[T].[Name] AS [Name] FROM (SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],ROW_NUMBER() OVER(ORDER BY [Users].[Age] ASC,[Users].[Id] DESC) AS [ROW_NUMBER_0] FROM [Users] AS [Users] WHERE [Users].[Id] > 0) AS [T] WHERE [T].[ROW_NUMBER_0] > 1
  10. */

  11. //如果需要多个条件的话
  12. q.Where(a => a.Id > 0).Where(a => a.Name.Contains("lu")).ToList();
  13. /*
  14. * 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 AND [Users].[Name] LIKE '%' + N'lu' + '%')
  15. */

  16. //选取指定字段
  17. q.Select(a => new { a.Id, a.Name, a.Age }).ToList();
  18. //或者
  19. q.Select(a => new User() { Id = a.Id, Name = a.Name, Age = a.Age }).ToList();
  20. /*
  21. * SELECT [Users].[Id] AS [Id],[Users].[Name] AS [Name],[Users].[Age] AS [Age] FROM [Users] AS [Users]
  22. */
复制代码


  连接查询

  建立连接:

  1. MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
  2. IQuery<User> users = context.Query<User>();
  3. IQuery<City> cities = context.Query<City>();
  4. IQuery<Province> provinces = context.Query<Province>();

  5. IJoiningQuery<User, City> user_city = users.InnerJoin(cities, (user, city) => user.CityId == city.Id);
  6. IJoiningQuery<User, City, Province> user_city_province = user_city.InnerJoin(provinces, (user, city, province) => city.ProvinceId == province.Id);
复制代码


  只获取 UserId,CityName,ProvinceName:

  1. user_city_province.Select((user, city, province) => new { UserId = user.Id, CityName = city.Name, ProvinceName = province.Name }).Where(a => a.UserId == 1).ToList();
  2. /*
  3. * SELECT [Users].[Id] AS [UserId],[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
  4. */
复制代码


  调用 Select 方法返回一个包含所有信息的 IQuery 对象:

 
  1.  var view = user_city_province.Select((user, city, province) => new { User = user, City = city, Province = province });
复制代码

  查出一个用户及其隶属的城市和省份:

  1. view.Where(a => a.User.Id == 1).ToList();
  2. /*
  3. * 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
  4. */
复制代码


  这时候也可以选取指定的字段:

  1.   view.Where(a => a.User.Id == 1).Select(a => new { UserId = a.User.Id, CityName = a.City.Name, ProvinceName = a.Province.Name }).ToList();

  2.   /*

  3.   * SELECT [Users].[Id] AS [UserId],[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

  4.   */
复制代码


  Chloe 也支持 Left Join、Right Join、Full Join连接,用法和 Inner Join 一样,就不一一介绍了。

  聚合函数

  1. IQuery<User> q = context.Query<User>();

  2. q.Select(a => DbFunctions.Count()).First();
  3. /*
  4. * SELECT TOP (1) COUNT(1) AS [C] FROM [Users] AS [Users]
  5. */

  6. q.Select(a => new { Count = DbFunctions.Count(), LongCount = DbFunctions.LongCount(), Sum = DbFunctions.Sum(a.Age), Max = DbFunctions.Max(a.Age), Min = DbFunctions.Min(a.Age), Average = DbFunctions.Average(a.Age) }).First();
  7. /*
  8. * SELECT TOP (1) COUNT(1) AS [Count],COUNT_BIG(1) AS [LongCount],SUM([Users].[Age]) AS [Sum],MAX([Users].[Age]) AS [Max],MIN([Users].[Age]) AS [Min],CAST(AVG([Users].[Age]) AS FLOAT) AS [Average] FROM [Users] AS [Users]
  9. */

  10. var count = q.Count();
  11. /*
  12. * SELECT COUNT(1) AS [C] FROM [Users] AS [Users]
  13. */

  14. var longCount = q.LongCount();
  15. /*
  16. * SELECT COUNT_BIG(1) AS [C] FROM [Users] AS [Users]
  17. */

  18. var sum = q.Sum(a => a.Age);
  19. /*
  20. * SELECT SUM([Users].[Age]) AS [C] FROM [Users] AS [Users]
  21. */

  22. var max = q.Max(a => a.Age);
  23. /*
  24. * SELECT MAX([Users].[Age]) AS [C] FROM [Users] AS [Users]
  25. */

  26. var min = q.Min(a => a.Age);
  27. /*
  28. * SELECT MIN([Users].[Age]) AS [C] FROM [Users] AS [Users]
  29. */

  30. var avg = q.Average(a => a.Age);
  31. /*
  32. * SELECT CAST(AVG([Users].[Age]) AS FLOAT) AS [C] FROM [Users] AS [Users]
  33. */
复制代码


  分组查询

  1. IQuery<User> q = context.Query<User>();

  2. IGroupingQuery<User> g = q.Where(a => a.Id > 0).GroupBy(a => a.Age);
  3. g = g.Having(a => a.Age > 1 && DbFunctions.Count() > 0);

  4. g.Select(a => new { a.Age, Count = DbFunctions.Count(), Sum = DbFunctions.Sum(a.Age), Max = DbFunctions.Max(a.Age), Min = DbFunctions.Min(a.Age), Avg = DbFunctions.Average(a.Age) }).ToList();
  5. /*
  6. * SELECT [Users].[Age] AS [Age],COUNT(1) AS [Count],SUM([Users].[Age]) AS [Sum],MAX([Users].[Age]) AS [Max],MIN([Users].[Age]) AS [Min],CAST(AVG([Users].[Age]) AS FLOAT) AS [Avg] FROM [Users] AS [Users] WHERE [Users].[Id] > 0 GROUP BY [Users].[Age] HAVING ([Users].[Age] > 1 AND COUNT(1) > 0)
  7. */
复制代码


  SqlQuery

  上面是纯面向对象的方式查询。连接查询、聚合查询、分组查询如此轻松,有没有觉得很方便?当然,始终和 linq 那种接近 sql 的 from v in q where v > 3 select v 写法没法比!同时,ORM始终是个工具,它并不是万能的,对于一些复杂的语句,还是得需要手写,因此,DbContext 也提供原生 sql 查询接口

  
  1. context.SqlQuery("select Id,Name,Age from Users where Name=@name", DbParam.Create("@name", "lu")).ToList();

  2.   context.SqlQuery("select Id from Users").ToList();
复制代码


  经测试,非 Debug 情况下,且都经过预热后,相同的查询在速度、性能上与 Dapper 相当,甚至比 Dapper 还快那么一丢丢。

  使用进阶

  IQuery 接口支持连接查询、聚合查询、分组查询,这几个接口配合使用可以减少很多我们开发中的烦恼。比如:

  去视图

  做数据库开发,多表关联的数据结构肯定不少,难免会有多表连接查询,很多时候,为了方便查询,一般我们都会建立视图。在我看来视图很烦,真的烦。

  int 烦 = 0;

  1.建视图的时候,字段多的话,烦++,如果出现字段重名的情况,必须起别名,烦++。

  2.视图建立起来了以后,查询是方便了,但后面维护就不那么友好了,比如某个表字段名改了、增加一个字段、删除一个字段等情况,得修改相应的视图(1个或多个),烦++;同时又要去修改相映射的实体,烦++。总之,Console.Write("烦烦烦: " + 烦.ToString()); 对于我这种懒程序员,这简直就是种煎熬!如果一套 ORM 支持连接查询,在一定程度上可以减少在数据库上建视图数量,无形中省出好多时间。

  为了让 Chloe 支持连接查询,费了我不少劲。连接查询的好处可以看上面连接查询部分。

  勉强应付一些复杂查询

  比如,本文中的 User 表、City 表,他们的关系是一个 User 隶属一个 City,一个 City 有多个用户。假设,现在有需求要查出 City 的信息,同时也要把该 City 下用户最小的年龄输出,如果用原生 sql 写的话大概是:

  
  1. select City.*,T.MinAge from City left join (select CityId,Min(Users.Age) as MinAge from Users group by Users.CityId) as T on City.Id=T.CityId
复制代码


  虽然也不是很复杂。来看看 Chloe 如何实现:

  1. IQuery<User> users = context.Query<User>();
  2. IQuery<City> cities = context.Query<City>();
  3. var gq = users.GroupBy(a => a.CityId).Select(a => new { a.CityId, MinAge = DbFunctions.Min(a.Age) });

  4. cities.LeftJoin(gq, (city, g) => city.Id == g.CityId).Select((city, g) => new { City = city, MinAge = g.MinAge }).ToList();
  5. /*
  6. * SELECT [T].[MinAge] AS [MinAge],[City].[Id] AS [Id],[City].[Name] AS [Name],[City].[ProvinceId] AS [ProvinceId] FROM [City] AS [City] LEFT JOIN (SELECT [Users].[CityId] AS [CityId],MIN([Users].[Age]) AS [MinAge] FROM [Users] AS [Users] GROUP BY [Users].[CityId]) AS [T] ON [City].[Id] = [T].[CityId]
  7. */
复制代码


  完全可以用面向对象的方式就可以实现,怎么样?很实用吧,免去拼 sql,让更多的时间去做业务开发!

  更多的用法还有待挖掘。

  支持的lambda

  Chloe 查询条件依赖 lambda 表达式,从对 lambda 表达式树零认知到完成对其解析这块,花了我好多精力,费了好多神,掉了不少头发。现在对谓语支持很丰富,可以说爱怎么写就怎么写~

  1. IQuery<User> q = context.Query<User>();

  2. List<int> ids = new List<int>();
  3. ids.Add(1);
  4. ids.Add(2);
  5. ids.Add(2);

  6. string name = "lu";
  7. string nullString = null;
  8. bool b = false;
  9. bool b1 = true;

  10. q.Where(a => true).ToList();
  11. q.Where(a => a.Id == 1).ToList();
  12. q.Where(a => a.Id == 1 || a.Id > 1).ToList();
  13. q.Where(a => a.Id == 1 && a.Name == name && a.Name == nullString && a.Id == FeatureTest.ID).ToList();
  14. q.Where(a => ids.Contains(a.Id)).ToList();
  15. q.Where(a => !b == (a.Id > 0)).ToList();
  16. q.Where(a => a.Id > 0).Where(a => a.Id == 1).ToList();
  17. q.Where(a => !(a.Id > 10)).ToList();
  18. q.Where(a => !(a.Name == name)).ToList();
  19. q.Where(a => a.Name != name).ToList();
  20. q.Where(a => a.Name == name).ToList();
  21. q.Where(a => (a.Name == name) == (a.Id > 0)).ToList();
  22. q.Where(a => a.Name == (a.Name ?? name)).ToList();
  23. q.Where(a => (a.Age == null ? 0 : 1) == 1).ToList();

  24. //运算操作符
  25. q.Select(a => new
  26. {
  27.     Add = 1 + 2,
  28.     Subtract = 2 - 1,
  29.     Multiply = 2 * 11,
  30.     Divide = 4 / 2,
  31.     And = true & false,
  32.     IntAnd = 1 & 2,
  33.     Or = true | false,
  34.     IntOr = 3 | 1,
  35. }).ToList();
复制代码


  常用的函数

  1.   IQuery<User> q = context.Query<User>();

  2.   var space = new char[] { ' ' };

  3.   DateTime startTime = DateTime.Now;
  4.   DateTime endTime = DateTime.Now.AddDays(1);
  5.   q.Select(a => new
  6. {
  7.     Id = a.Id,

  8.     String_Length = (int?)a.Name.Length,//LEN([Users].[Name])
  9.     Substring = a.Name.Substring(0),//SUBSTRING([Users].[Name],0 + 1,LEN([Users].[Name]))
  10.     Substring1 = a.Name.Substring(1),//SUBSTRING([Users].[Name],1 + 1,LEN([Users].[Name]))
  11.     Substring1_2 = a.Name.Substring(1, 2),//SUBSTRING([Users].[Name],1 + 1,2)
  12.     ToLower = a.Name.ToLower(),//LOWER([Users].[Name])
  13.     ToUpper = a.Name.ToUpper(),//UPPER([Users].[Name])
  14.     IsNullOrEmpty = string.IsNullOrEmpty(a.Name),//太长,不贴了
  15.     Contains = (bool?)a.Name.Contains("s"),//太长,略
  16.     Trim = a.Name.Trim(),//RTRIM(LTRIM([Users].[Name]))
  17.     TrimStart = a.Name.TrimStart(space),//LTRIM([Users].[Name])
  18.     TrimEnd = a.Name.TrimEnd(space),//RTRIM([Users].[Name])
  19.     StartsWith = (bool?)a.Name.StartsWith("s"),//太长,略
  20.     EndsWith = (bool?)a.Name.EndsWith("s"),//太长,略

  21.     SubtractTotalDays = endTime.Subtract(startTime).TotalDays,//CAST(DATEDIFF(DAY,@P_0,@P_1)
  22.     SubtractTotalHours = endTime.Subtract(startTime).TotalHours,//CAST(DATEDIFF(HOUR,@P_0,@P_1)
  23.     SubtractTotalMinutes = endTime.Subtract(startTime).TotalMinutes,//CAST(DATEDIFF(MINUTE,@P_0,@P_1)
  24.     SubtractTotalSeconds = endTime.Subtract(startTime).TotalSeconds,//CAST(DATEDIFF(SECOND,@P_0,@P_1)
  25.     SubtractTotalMilliseconds = endTime.Subtract(startTime).TotalMilliseconds,//CAST(DATEDIFF(MILLISECOND,@P_0,@P_1)

  26.     Now = DateTime.Now,//GETDATE()
  27.     UtcNow = DateTime.UtcNow,//GETUTCDATE()
  28.     Today = DateTime.Today,//CAST(GETDATE() AS DATE)
  29.     Date = DateTime.Now.Date,//CAST(GETDATE() AS DATE)
  30.     Year = DateTime.Now.Year,//DATEPART(YEAR,GETDATE())
  31.     Month = DateTime.Now.Month,//DATEPART(MONTH,GETDATE())
  32.     Day = DateTime.Now.Day,//DATEPART(DAY,GETDATE())
  33.     Hour = DateTime.Now.Hour,//DATEPART(HOUR,GETDATE())
  34.     Minute = DateTime.Now.Minute,//DATEPART(MINUTE,GETDATE())
  35.     Second = DateTime.Now.Second,//DATEPART(SECOND,GETDATE())
  36.     Millisecond = DateTime.Now.Millisecond,//DATEPART(MILLISECOND,GETDATE())
  37.     DayOfWeek = DateTime.Now.DayOfWeek,//(DATEPART(WEEKDAY,GETDATE()) - 1)

  38.     Int_Parse = int.Parse("1"),//CAST(N'1' AS INT)
  39.     Int16_Parse = Int16.Parse("11"),//CAST(N'11' AS SMALLINT)
  40.     Long_Parse = long.Parse("2"),//CAST(N'2' AS BIGINT)
  41.     Double_Parse = double.Parse("3"),//CAST(N'3' AS FLOAT)
  42.     Float_Parse = float.Parse("4"),//CAST(N'4' AS REAL)
  43.     Decimal_Parse = decimal.Parse("5"),//CAST(N'5' AS DECIMAL)
  44.     Guid_Parse = Guid.Parse("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"),//CAST(N'xxx' AS UNIQUEIDENTIFIER) AS [Guid_Parse]

  45.     Bool_Parse = bool.Parse("1"),//CASE WHEN CAST(N'1' AS BIT) = CAST(1 AS BIT) THEN CAST(1 AS BIT) WHEN NOT (CAST(N'1' AS BIT) = CAST(1 AS BIT)) THEN CAST(0 AS BIT) ELSE NULL END AS [Bool_Parse]
  46.     DateTime_Parse = DateTime.Parse("1992-1-16"),//CAST(N'1992-1-16' AS DATETIME) AS [DateTime_Parse]

  47.     B = a.Age == null ? false : a.Age > 1,
  48. }).ToList();
复制代码


  Chloe 的查询,基本就这些用法。因为查询接口直接借鉴 linq,所以,看起来就好像在介绍 linq 一样,抱歉- -。也正因为这点,之前我把项目中的 EF 替换成 Chloe 的时候,因为我个人不怎么用 linq 的 from in select 那种语法,所以,替换的时候几乎不用改什么代码,就可以成功编译运行。EF 对实体间的关系处理得非常好,如一对多,一对一导航,Chloe 倒没那么强大。就目前的 Chloe 的 Query 接口,基本可以满足大部分查询需求了。

  现在市面上各种ORM,层出不穷,有人可能会问 LZ 为什么还要重复造轮子?

  这确实是一个ORM齐放的年代,各色各样,千奇百怪的都有。但让人满意的框架(EF除外,EF在我心中是神一样的存在)少之又少。做得不错的,也总有些方面不足,恰恰却因为一些小小的不足让我止步,如实体复杂,不支持 lambda,支持lambda的但支持的写法又不多,连接查询不是很友好、便捷等等,都怪我太挑剔,抱歉。

  文章开头也说过,增删查改,烦了。想用业余时间做点有意思的东西,提升自己编码能力的同时也可以学到更多知识。因为写了这个框架,我对面对对象的理解更加深刻了,如果不尝试的话,我估计我在程序员职业生涯内连个抽象类、接口都不会设计,更别说会什么设计模式,面对对象编程原则了。之所以选择做 ORM 来开刀,因为 ORM 很贴切我们日常开发,只要涉及数据库,就可以用到!

  如果上面两点还不足以让您明白我为什么要造轮子,那最后我要告诉您的是:我是一枚任性的程序员,我就是要造轮子!

原文作者:佚名  来源:开发者头条

相关帖子

发表于 2016-7-8 08:43:53 | 显示全部楼层
好帖,谢谢分享。
使用道具 举报

回复

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部