[Open Source]. NET database access framework Chloe. ORM,. netchloe. orm

[Open Source]. NET database access framework Chloe. ORM,. netchloe. orm
Preface

After 13 years of graduation, I went into my first company internship and got in touch with EntityFramework. At that time, I thought it was a great deal. I could access the database so easily and elegantly! After all, I was still young and never met the world. I learned C # for two months in a hurry to get an internship and get together with a proof of work before I went to work. In this way, I became a programmer and now I went on the road. That would only know SqlHelper, DataTable. ORM? I have never heard of it. Although I stayed in my first company for only two months, I got to know the EntityFramework, and now I am on the road to ORM... pure entities are super easy to add, modify, delete, and query. There is no reason to resist them! As a result, it was extremely uncomfortable to enter the second company for development, because they did not use EF, nor did they use ORM like linq, and they had their own database access framework. The design of the East-West entity is complex, with few functions supported, the query conditions still depend on strings, the development success rate is too low, and the DB operation entry interface design is also very heavy. There are no less than 60 methods in it, so it is easy to understand, hard work! At that time, I miss EF ~ During the working hours of the new company, the page was added and changed back and forth. It was boring to add, delete, query, modify, and fix bugs, and gradually felt that the encoding capability was too slow. At the same time, because the company's ORM is not very good, so I came up with the idea of writing my own ORM, And then... Chloe. ORM was available ~

Chloe. ORM

Chloe's query interface is designed to use (zhao) (ban) linq, but does not support linq. Before development, the interface definition for my ORM query condition must support lambda expressions (Trend and trend, which does not discuss the performance of the Expression Tree ). At the beginning of the development, I also designed my own Query Interfaces. I thought about one set after another, but I didn't have the convenience of the interfaces designed by linq. Later, I don't want to think about it. I just copied the interfaces directly, so I don't want to explain it! Sorry for the great design of our predecessors. I want to stand on their shoulders!

First look at the IDbContext interface:

Public interface IDbContext: IDisposable {IDbSession CurrentSession {get;} IQuery <T> Query <T> () where T: new (); IEnumerable <T> SqlQuery <T> (string SQL, params DbParam [] parameters) where T: new (); T Insert <T> (T entity ); object Insert <T> (Expression <Func <T> body); int Update <T> (T entity); int Update <T> (Expression <Func <T, t> body, Expression <Func <T, bool> condition); int Delete <T> (T entity); int Delete <T> (Expression <Func <T, bool> condition); void TrackEntity (object entity );}View Code

The Chloe operation portal is IDbContext. IDbContext has only two queries, two Insert statements, two Update statements, two Delete statements, one TrackEntity method, and one CurrentDbSession attribute. The design is simple, but it can definitely meet 81% of the requirements (more satisfied, more love )!
This article describes how to use the Query interface.

Entity:

Public enum Gender {Man = 1, Woman} [TableAttribute ("Users")] public class User {[Column (IsPrimaryKey = true)] [AutoIncrementAttribute] 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 ;}}View Code

First, create a DbContext:

IDbContext context = new MsSqlContext(DbHelper.ConnectionString);

Create an IQuery <T>:

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

Basic query IQuery <User> q = context. query <User> (); q. where (a =>. id> 0 ). firstOrDefault (); q. where (a =>. id> 0 ). toList (); q. where (a =>. id> 0 ). orderBy (a =>. age ). toList (); q. where (a =>. id> 0 ). take (1, 999 ). orderBy (a =>. age ). toList (); // pagination. To avoid generating too long SQL statements, select only the Id and Name Fields q. where (a =>. id> 0 ). orderBy (a =>. age ). thenByDesc (a =>. id ). select (a => new {. id,. name }). skip (1 ). take (1, 999 ). toList ();/** 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 * // if multiple conditions are required, q. where (a =>. id> 0 ). where (a =>. name. contains ("lu ")). 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 AND [Users]. [Name] LIKE '%' + N' Lu' + '%') * // select the specified field q. select (a => new {. id,. name,. age }). toList (); // or q. select (a => new User () {Id =. id, Name =. name, Age =. age }). toList ();/** SELECT [Users]. [Id] AS [Id], [Users]. [Name] AS [Name], [Users]. [Age] AS [Age] FROM [Users] AS [Users] */View Code connection Query

Establish a connection:

MsSqlContext context = new MsSqlContext (DbHelper. connectionString); 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 );View Code

Get only UserId, CityName, ProvinceName:

user_city_province.Select((user, city, province) => new { UserId = user.Id, CityName = city.Name, ProvinceName = province.Name }).Where(a => a.UserId == 1).ToList();/* * 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 */

Call the Select method to return an IQuery containing all information <T> object:

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

Find a user and the city and province of the User:

view.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 */

In this case, you can also select the specified field:

view.Where(a => a.User.Id == 1).Select(a => new { UserId = a.User.Id, CityName = a.City.Name, ProvinceName = a.Province.Name }).ToList();/* * 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 */

Chloe also supports Left Join, Right Join, and Full Join. The usage is the same as that of Inner Join.

Aggregate Function IQuery <User> q = context. query <User> (); q. select (a => DbFunctions. count ()). first ();/** select top (1) COUNT (1) AS [C] FROM [Users] AS [Users] */q. select (a => new {Count = DbFunctions. count (), LongCount = DbFunctions. longCount (), Sum = DbFunctions. sum (. age), Max = DbFunctions. max (. age), Min = DbFunctions. min (. age), Average = DbFunctions. average (. age )}). first ();/** 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] */var count = q. count ();/** select count (1) AS [C] FROM [Users] AS [Users] */var longCount = q. longCount ();/** SELECT COUNT_BIG (1) AS [C] FROM [Users] AS [Users] */var sum = q. sum (a =>. age);/** select sum ([Users]. [Age]) AS [C] FROM [Users] AS [Users] */var max = q. max (a =>. age);/** select max ([Users]. [Age]) AS [C] FROM [Users] AS [Users] */var min = q. min (a =>. age);/** select min ([Users]. [Age]) AS [C] FROM [Users] AS [Users] */var avg = q. average (a =>. age);/** select cast (AVG ([Users]. [Age]) as float) AS [C] FROM [Users] AS [Users] */View Code grouping query IQuery <User> q = context. query <User> (); IGroupingQuery <User> g = q. where (a =>. id> 0 ). groupBy (a =>. age); g = g. having (a =>. age> 1 & DbFunctions. count ()> 0); g. select (a => new {. age, Count = DbFunctions. count (), Sum = DbFunctions. sum (. age), Max = DbFunctions. max (. age), Min = DbFunctions. min (. age), Avg = DbFunctions. average (. 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], 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 )*/View CodeSqlQuery

The above is an object-oriented query. Is connection query, aggregate query, and group query so easy? Of course, there is always no way to compare it with the from v in q where v> 3 select v method that is close to the SQL method of linq! At the same time, ORM is always a tool, and it is not omnipotent. For some complicated statements, you still need to write them manually. Therefore, DbContext also provides the native SQL query interface.

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

After testing, the same query speed and performance are the same as that of Dapper in non-Debug scenarios and after the push, and it is even faster than Dapper.

Advanced usage

IQuery <T> interfaces support connection query, aggregate query, and group query. These interfaces can be used together to reduce a lot of troubles in our development. For example:

View Removal

For database development, there must be a lot of data structures associated with multiple tables. It is inevitable that there will be multi-Table connection queries. In many cases, we usually create views to facilitate queries. In my opinion, views are annoying.

Int annoying = 0;

1When creating a view, if there are too many fields, it will be annoying to + +. If there is a duplicate field name, aliases must be initiated, annoying + +.

2. After the view is created, the query is convenient, but the subsequent maintenance is not so friendly. For example, when the field name of a table is changed, a field is added, and a field is deleted, you have to modify the corresponding view (one or more), so it is annoying to ++. At the same time, you have to modify the ing object, so it is annoying to ++. In short, Console. Write ("annoying:" + annoying. ToString (); this is simply a torment for a lazy programmer like me! If an ORM supports connection query, you can reduce the number of views on the database to a certain extent, saving a lot of time.

In order to allow Chloe to support connection queries, it takes a lot of effort. For the benefits of connection query, see the connection query section above.

Barely cope with some complex queries

For example, in the User table and City table described in this article, the relationship between a User and a City is that a User belongs to a City and has multiple users. Assume that you need to find out the City information and output the Minimum Age of the City user. If you use a native SQL statement, it is probably:

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

Although it is not very complicated. Let's see how Chloe achieves this:

IQuery <User> users = context. query <User> (); IQuery <City> cities = context. query <City> (); var gq = users. groupBy (a =>. cityId ). select (a => new {. cityId, MinAge = DbFunctions. min (. age)}); cities. leftJoin (gq, (city, g) => city. id = g. cityId ). select (city, g) => new {City = city, MinAge = g. minAge }). toList ();/** 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] */View Code

It can be implemented in an object-oriented way. How can this problem be solved? It is very practical. It eliminates the need to fight SQL and allows more time for business development!

More usage is to be explored.

Supported lambda

Chloe's query condition depends on the lambda expression. It took me a lot of energy, a lot of effort, and a lot of hair to complete the parsing of the lambda Expression Tree. Currently, many predicates are supported. You can say that you can write statements as you like ~

IQuery <User> q = context. query <User> (); List <int> ids = new List <int> (); ids. add (1); ids. add (2); ids. add (2); string name = "lu"; string nullString = null; bool B = false; bool b1 = true; q. where (a => true ). toList (); q. where (a =>. id = 1 ). toList (); q. where (a =>. id = 1 |. id> 1 ). toList (); q. where (a =>. id = 1 &. name = name &. name = nullString &. id = FeatureTest. ID ). toList (); q. where (a => ids. Contains (a. Id). ToList (); q. Where (a =>! B = (. id> 0 )). toList (); q. where (a =>. id> 0 ). where (a =>. id = 1 ). toList (); q. where (a =>! (A. Id> 10). ToList (); q. Where (a =>! (A. Name = name). ToList (); q. Where (a => a. Name! = Name ). toList (); q. where (a =>. name = name ). toList (); q. where (a => (. name = name) = (. id> 0 )). toList (); q. where (a =>. name = (. name ?? Name). ToList (); q. Where (a => (a. Age = null? 0: 1) = 1 ). toList (); // operator q. select (a => new {Add = 1 + 2, Subtract = 2-1, Multiply = 2*11, Divide = 4/2, And = true & false, intAnd = 1 & 2, Or = true | false, IntOr = 3 | 1 ,}). toList ();IQuery <User> q = context. query <User> (); var space = new char [] {''}; DateTime startTime = DateTime. now; DateTime endTime = DateTime. now. addDays (1); q. select (a => new {Id =. id, String_Length = (int ?) A. name. length, // LEN ([Users]. [Name]) Substring =. name. substring (0), // SUBSTRING ([Users]. [Name], 0 + 1, LEN ([Users]. [Name]) Substring1 =. name. substring (1), // SUBSTRING ([Users]. [Name], 1 + 1, LEN ([Users]. [Name]) Substring1_2 =. name. substring (1, 2), // SUBSTRING ([Users]. [Name], 1 + 1, 2) ToLower =. name. toLower (), // LOWER ([Users]. [Name]) ToUpper =. name. toUpper (), // UPPER ([Users]. [Name]) IsNullOrEmpty = String. IsNullOrEmpty (a. Name), // too long, no Contains = (bool ?) A. name. contains ("s"), // too long, slightly Trim =. name. trim (), // RTRIM (LTRIM ([Users]. [Name]) TrimStart =. name. trimStart (space), // LTRIM ([Users]. [Name]) TrimEnd =. name. trimEnd (space), // RTRIM ([Users]. [Name]) StartsWith = (bool ?) A. Name. StartsWith ("s"), // too long, slightly EndsWith = (bool ?) A. name. endsWith ("s"), // too long, slightly SubtractTotalDays = endTime. subtract (startTime ). totalDays, // CAST (DATEDIFF (DAY, @ P_0, @ P_1) SubtractTotalHours = endTime. subtract (startTime ). totalHours, // CAST (DATEDIFF (HOUR, @ P_0, @ P_1) SubtractTotalMinutes = endTime. subtract (startTime ). totalMinutes, // CAST (DATEDIFF (MINUTE, @ P_0, @ P_1) SubtractTotalSeconds = endTime. subtract (startTime ). totalSeconds, // CAST (DATEDIFF (SECOND, @ P_0, @ P _ 1) SubtractTotalMilliseconds = endTime. subtract (startTime ). totalMilliseconds, // CAST (DATEDIFF (MILLISECOND, @ P_0, @ P_1) Now = DateTime. now, // GETDATE () UtcNow = DateTime. utcNow, // GETUTCDATE () Today = DateTime. today, // CAST (GETDATE () as date) Date = DateTime. now. date, // CAST (GETDATE () as date) Year = DateTime. now. year, // DATEPART (YEAR, GETDATE () Month = DateTime. now. month, // DATEPART (MONTH, GETDATE () Day = DateTime. now. day, // DATEPART (DAY, GETDATE () Hour = DateTime. now. hour, // DATEPART (HOUR, GETDATE () Minute = DateTime. now. minute, // DATEPART (MINUTE, GETDATE () Second = DateTime. now. second, // DATEPART (SECOND, GETDATE () Millisecond = DateTime. now. millisecond, // DATEPART (MILLISECOND, GETDATE () DayOfWeek = DateTime. now. dayOfWeek, // (DATEPART (WEEKDAY, GETDATE ()-1) Int_Parse = int. parse ("1"), // CAST (n'1' AS IN T) Int16_Parse = Int16.Parse ("11"), // CAST (n'11' as smallint) Long_Parse = long. parse ("2"), // CAST (n'2' as bigint) Double_Parse = double. parse ("3"), // CAST (n'3' as float) Float_Parse = float. parse ("4"), // CAST (n'4' as real) Decimal_Parse = decimal. parse ("5"), // CAST (n'5' as decimal) Guid_Parse = Guid. parse ("D544BC4C-739E-4CD3-A3D3-7BF803FCE179"), // CAST (N 'xxx' as uniqueidentifier) AS [Guid_Parse] Bool_P Arse = 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] DateTime_Parse = DateTime. parse ("1992-1-16"), // CAST (n' 1992-1-16 'as datetime) AS [DateTime_Parse] B =. age = null? False: a. Age> 1,}). ToList ();View Code

Chloe's query, basically these usage. Because the query interface directly draws on linq, it looks like it is about linq. Sorry --. Because of this, when I replaced the EF in the project with Chloe, because I personally do not use the syntax of the from in select in linq, you can compile and run the program without having to change any code. EF processes the relationships between entities very well. For example, for one-to-many and one-to-one navigation, Chloe is not that powerful. The current Query interface of Chloe can basically meet most Query requirements.

There are various ORM on the market, and some people may ask why LZ needs to duplicate the wheel?

Conclusion

Chloe. ORM is fully open-source. It complies with the Apache2.0 Protocol and is hosted on GitHub for your reference. If you can participate in the development and improvement of Chloe, it would be better. Project address: https://github.com/shuxinqin/javase. I am very grateful to you for your interest or good hope for a star!

If you can give me a thumbs up, thank you!