[open source]. Net orm FreeSql 1.5.0 latest version (serial number: long time no see)

I haven't written an article for a long time. The last time I released version 1.0 on New Year's day, this year's version plan is to release a small version at the end of each month (release 2.0 at the end of the year). The open source work of the whole year is mainly to collect user needs, add functions, improve testing, and fix bug s. What are the new features of FreeSQL 1.0 - > 1.5 every six months? I can only say that every function can make me excited, and I can feel the same excitement of users (paranoia).

Can't wait for people to ask, the update speed is too fast, right? Will there be any problem with the upgrade?

• It is not a good habit to upgrade directly without knowing the version update log. It is recommended to pay attention to our update log (there are special documents on github);
• Our version development principle: add new functions and cut off a small number of unreasonable functions while ensuring compatibility as much as possible;
• Our unit test quantity: 4000 +, which is the guarantee of our proud release;

FreeSql is a. Net ORM, which can support. Netframework 4.0 +,. NetCore, Xamarin, XAUI, Blazor, and unspeakable running platforms. Because the code is green and independent, it is very simple to support new platforms. At present, the number of unit tests is 4000 +, the number of Nuget downloads is 123K +, and the source code is submitted almost every day. It is gratifying that FreeSql has joined the ncc open source community: https://github.com/dotnetcore/FreeSql After joining the organization, the community has a greater sense of responsibility and needs to work harder to do a good job in quality and contribute to the open source community. QQ Development Group: 4336577

Why do you have to make wheels again?

The main advantage of FreeSql lies in its ease of use, which is basically out of the box. It has a good switch compatibility between different databases. The author has spent a lot of time on this project. Please take half an hour to understand the project. Thank you.

The overall functional features of FreeSql are as follows:

• Support CodeFirst comparative structure change migration;
• Support DbFirst to import entity classes from database;
• Support rich expression functions, user-defined parsing;
• Support batch add, batch update and BulkCopy;
• Support navigation attributes, greedy loading, delayed loading, cascade saving;
• Support read-write separation, sub table sub database, tenant design;
• Support MySql/SqlServer/PostgreSQL/Oracle/Sqlite / Damin / MsAccess;

The important functions of 1.0 - > 1.5 update are as follows:

1, UnitOfWorkManager work unit manager, which can realize Spring transaction design;

II IFreeSql.InsertOrUpdate Implement batch saving, and execute the merge into or on duplicate key update automatically according to the database;

III ISelect.WhereDynamicFilter Methods to realize dynamic filter conditions (interact with the front-end);

4, Automatic adaptation expression parsing yyyyMMdd commonly used c ා date format;

V IUpdate.SetSourceIgnore Method implementation ignores the field whose property value is null;

Vi FreeSql.Provider.Dameng Based on DmProvider Ado.net Access to daydream database;

7, Automatically identify the common entity characteristics of EFCore, FreeSql.DbContext It has high similarity syntax with EFCore, and supports 90% similar fluent API;

VIII ISelect.ToTreeList The extended method queries the data and processes the entities configured with parent-child navigation attributes into a tree List;

9, BulkCopy improves the performance of mass data insertion;

10, SQLite: memory: memory mode;

FreeSql is very simple to use. You only need to define an IFreeSql object:

static IFreeSql fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.MySql, connectionString) .UseAutoSyncStructure(true) //Automatically synchronize entity structure to database .Build(); //Be sure to define Singleton singleton mode

public class SongService { BaseRepository<Song> _repo; public SongService(BaseRepository<Song> repo) { _repo = repo; } [Transactional] public virtual void Test1() { _repo.Insert(new Song { Title = "Canon 1" }); //Transaction 1 this.Test2(); } [Transactional(Propagation = Propagation.Nested)] //Nested transactions, new (transactions without Test1) public virtual void Test2() { _repo.Insert(new Song { Title = "Canon 2" }); } }

Baserepository is FreeSql.BaseRepository The general warehouse class implemented by the package can be inherited and reused in the actual project.

The mode of Propagation refers to Spring transactions, which are in the following modes:

• Requierd: if there is no current transaction, create a new transaction. If there is already a transaction, add it to the transaction. It is the default choice.
• Supports: supports the current transaction. If there is no current transaction, it will be executed in a non transactional way.
• Mandatory: use the current transaction. If there is no current transaction, an exception will be thrown.
• NotSupported: performs the operation in a non transactional manner. If there is a transaction, the current transaction will be suspended.
• Never: perform the operation in a non transactional manner, and throw an exception if the current transaction exists.
• Nested: executed as a nested transaction. (this is used in the above example)

This is what UnitOfWorkManager does. It avoids the tedious Try/Catch operation of getting Session instance to start transaction / commit / rollback transaction every time for data operation. These are also good applications of AOP (aspect oriented programming) mechanism. On the one hand, it makes the development business logic clearer and the division of labor easier. On the other hand, the application of AOP isolation reduces the coupling of programs, so that we can combine all aspects in different applications to greatly improve code reuse.

Preparation before use step 1: Configuration Startup.cs injection

//Startup.cs public void ConfigureServices(IServiceCollection services) { services.AddSingleton<IFreeSql>(fsql); services.AddScoped<UnitOfWorkManager>(); services.AddFreeRepository(null, typeof(Startup).Assembly); }

Prepare for use step 2: define transaction characteristics

[AttributeUsage(AttributeTargets.Method)] public class TransactionalAttribute : Attribute { /// <summary> ///Transaction propagation mode /// </summary> public Propagation Propagation { get; set; } = Propagation.Requierd; /// <summary> ///Transaction isolation level /// </summary> public IsolationLevel? IsolationLevel { get; set; } }

Prepare for use step 3: introduce dynamic agent Library

Get UnitOfWorkManager from the container in Before and call its var UOW= uowManager.Begin ( attr.Propagation , attr.IsolationLevel )Methods

In the After call Before uow.Commit Or Rollback method, finally called uow.Dispose

Question and answer: is it right to start a business in the right way?

It may not be a real transaction, but it may be a virtual one. It may be a fake unit of work (without a transaction), it may be a continuation of the last transaction, or it may be a new transaction, depending on the propagation mode.

IFreeSql defines the insertoupdate method to realize the function of batch insertion or update. It uses the database features to save and automatically adapts according to the database during execution:

fsql.InsertOrUpdate<T>() .SetSource(items) //Data to be operated .ExecuteAffrows();

Since the type of fsql variable we defined earlier is MySql, the executed statement is roughly as follows:

INSERT INTO `T`(`id`, `name`) VALUES(1, '001'), (2, '002'), (3, '003'), (4, '004') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`)

When the entity class has an auto increment attribute, the bulk insertoupdate can be split into two executions at most. The data with no auto increment or auto increment is calculated internally, and the two commands (transaction execution) of insert into and merge into mentioned above are executed respectively.

Have you ever seen such advanced query function? WhereDynamicFilter can easily complete this task at the back end. The front end assembles the corresponding json string according to the UI and transmits it to the back end, as follows:

DynamicFilterInfo dyfilter = JsonConvert.DeserializeObject<DynamicFilterInfo>(@" { ""Logic"" : ""Or"", ""Filters"" : [ { ""Field"" : ""Code"", ""Operator"" : ""NotContains"", ""Value"" : ""val1"", ""Filters"" : [ { ""Field"" : ""Name"", ""Operator"" : ""NotStartsWith"", ""Value"" : ""val2"", } ] }, { ""Field"" : ""Parent.Code"", ""Operator"" : ""Eq"", ""Value"" : ""val11"", ""Filters"" : [ { ""Field"" : ""Parent.Name"", ""Operator"" : ""Contains"", ""Value"" : ""val22"", } ] } ] } "); fsql.Select<VM_District_Parent>().WhereDynamicFilter(dyfilter).ToList(); //SELECT a.""Code"", a.""Name"", a.""ParentCode"", a__Parent.""Code"" as4, a__Parent.""Name"" as5, a__Parent.""ParentCode"" as6 //FROM ""D_District"" a //LEFT JOIN ""D_District"" a__Parent ON a__Parent.""Code"" = a.""ParentCode"" //WHERE (not((a.""Code"") LIKE '%val1%') AND not((a.""Name"") LIKE 'val2%') OR a__Parent.""Code"" = 'val11' AND (a__Parent.""Name"") LIKE '%val22%')

Supported operators: Contains/StartsWith/EndsWith/NotContains/NotStartsWith/NotEndsWith, Equals/Eq/NotEqual, greaterthan / greaterthan orequal, lessthan / lessthan orequal

I don't know if you have this problem. You can use it in ORM expression DateTime.Now.ToString("yyyyMM") is a very difficult task to convert. In the databases I adapt, only MsAccess can be directly translated into the corresponding SQL execution.

This idea comes from another ORM issues, and from time to time I will learn about the advantages and disadvantages of other ORM in order to supplement FreeSql.

After the idea came out, i.e. I worked hard to complete it on May 24, 2020. In addition to the coding adaptation of each database, more time was spent on unit tests, which have passed all the tests (4000 + unit tests are not blown).

Just use this function to let you feel the seriousness of FreeSql. It is not a personal project mentioned by some people. Thank you.

var dtn = DateTime.Parse("2020-1-1 0:0:0"); var dts = Enumerable.Range(1, 12).Select(a => dtn.AddMonths(a)) .Concat(Enumerable.Range(1, 31).Select(a => dtn.AddDays(a))) .Concat(Enumerable.Range(1, 24).Select(a => dtn.AddHours(a))) .Concat(Enumerable.Range(1, 60).Select(a => dtn.AddMinutes(a))) .Concat(Enumerable.Range(1, 60).Select(a => dtn.AddSeconds(a))); foreach (var dt in dts) { Assert.Equal(dt.ToString("yyyy-MM-dd HH:mm:ss.fff"), fsql.Select<T>().First(a => dt.ToString())); Assert.Equal(dt.ToString("yyyy-MM-dd HH:mm:ss"), fsql.Select<T>().First(a => dt.ToString("yyyy-MM-dd HH:mm:ss"))); Assert.Equal(dt.ToString("yyyy-MM-dd HH:mm"), fsql.Select<T>().First(a => dt.ToString("yyyy-MM-dd HH:mm"))); Assert.Equal(dt.ToString("yyyy-MM-dd HH"), fsql.Select<T>().First(a => dt.ToString("yyyy-MM-dd HH"))); Assert.Equal(dt.ToString("yyyy-MM-dd"), fsql.Select<T>().First(a => dt.ToString("yyyy-MM-dd"))); Assert.Equal(dt.ToString("yyyy-MM"), fsql.Select<T>().First(a => dt.ToString("yyyy-MM"))); Assert.Equal(dt.ToString("yyyyMMddHHmmss"), fsql.Select<T>().First(a => dt.ToString("yyyyMMddHHmmss"))); Assert.Equal(dt.ToString("yyyyMMddHHmm"), fsql.Select<T>().First(a => dt.ToString("yyyyMMddHHmm"))); Assert.Equal(dt.ToString("yyyyMMddHH"), fsql.Select<T>().First(a => dt.ToString("yyyyMMddHH"))); Assert.Equal(dt.ToString("yyyyMMdd"), fsql.Select<T>().First(a => dt.ToString("yyyyMMdd"))); Assert.Equal(dt.ToString("yyyyMM"), fsql.Select<T>().First(a => dt.ToString("yyyyMM"))); Assert.Equal(dt.ToString("yyyy"), fsql.Select<T>().First(a => dt.ToString("yyyy"))); Assert.Equal(dt.ToString("HH:mm:ss"), fsql.Select<T>().First(a => dt.ToString("HH:mm:ss"))); Assert.Equal(dt.ToString("yyyy MM dd HH mm ss yy M d H hh h"), fsql.Select<T>().First(a => dt.ToString("yyyy MM dd HH mm ss yy M d H hh h"))); Assert.Equal(dt.ToString("yyyy MM dd HH mm ss yy M d H hh h m s tt t").Replace("morning", "AM").Replace("afternoon", "PM").Replace("upper", "A").Replace("lower", "P"), fsql.Select<T>().First(a => dt.ToString("yyyy MM dd HH mm ss yy M d H hh h m s tt t"))); }

Support common c ා date format, yy yy M M D D H H mm s s YY M D H HH m s t t t

tt t by AM PM

The two conversions of AM PM are not perfect and can be used barely.

This function has been mentioned several times by the user, and each time they think FreeSql.Repository This can be done by comparing the state of.

This time, the author loves them. Why do we have to use a function to restrict users? Do you often hear about EF framework and MVC framework? The definition of framework is actually constraint + specification.

The author does not want to make such a constraint, but rather wants to provide as many practical functions as possible for the user to choose, defining the project as: functional components.

fsql.Update<Song>() .SetSourceIgnore(item, col => col == null) .ExecuteAffrows();

The second parameter is func < object, bool > type. col is equal to the value of the attribute. The above code will ignore the = = null attribute when updating the entity item.

Founded in 2000, Wuhan Dameng database Co., Ltd. is a basic software enterprise under China Electronic Information Industry Group (CEC). It specializes in the research and development, sales and service of database management system, and provides users with services such as big data platform architecture consultation, data technology plan, product deployment and implementation. Over the years, daydream has always adhered to original innovation and independent research and development. At present, it has mastered the core and cutting-edge technology in the field of data management and data analysis, with all the source code and full independent intellectual property rights.

I don't know that you haven't heard of the relevant policies. After the government promotes localization, it is a trend. Although. NET is not made in China, it can't limit the programming language at present. At present, it is forced to promote the operating system and database.

We know that EFCore for oracle has a lot of problems, and it has not been updated to 3.x. in this context, a domestic database can not expect who can achieve a good EFCore. At present, except for EFCore for sqlserver, we are not sure that we are fully dominant. At least in other databases, we must be more grounded.

To put it bluntly, daydream database has been supported for a long time. It was previously implemented in the way of Odbc, but later, it is troublesome according to the user's feedback on the environment of Odbc. After research, we decided to support it ado.net Adapt to make users more convenient. use ado.net The method of "connect to dream" only needs to modify the type when IFreeSql was created, as follows:

static IFreeSql fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.Dameng, connectionString) .UseAutoSyncStructure(true) //Automatically synchronize entity structure to database .Build(); //Be sure to define Singleton singleton mode

EFCore currently has the largest number of users. In order to facilitate the transition of some projects to FreeSql, we have made some "AI":

• Automatically identify EFCore entity properties: Key/Required/NotMapped/Table/Column

[Table("table01")] //This is actually a feature of EFCore class MyTable { [Key] public int Id { get; set; } }

• Fluent API similar to 90% of efcore

fsql.CodeFirst.Entity<Song>(eb => { eb.ToTable("tb_song"); eb.Ignore(a => a.Field1); eb.Property(a => a.Title).HasColumnType("varchar(50)").IsRequired(); eb.Property(a => a.Url).HasMaxLength(100); eb.Property(a => a.RowVersion).IsRowVersion(); eb.Property(a => a.CreateTime).HasDefaultValueSql("current_timestamp"); eb.HasKey(a => a.Id); eb.HasIndex(a => new { a.Id, a.Title }).IsUnique().HasName("idx_xxx11"); //One to many, many to one eb.HasOne(a => a.Type).HasForeignKey(a => a.TypeId).WithMany(a => a.Songs); //Many to many eb.HasMany(a => a.Tags).WithMany(a => a.Songs, typeof(Song_tag)); }); fsql.CodeFirst.Entity<SongType>(eb => { eb.HasMany(a => a.Songs).WithOne(a => a.Type).HasForeignKey(a => a.TypeId); eb.HasData(new[] { new SongType { Id = 1, Name = "popular", Songs = new List<Song>(new[] { new Song{ Title = "I Really Love You" }, new Song{ Title = "love you forever" }, }) }, new SongType { Id = 2, Name = "rural", Songs = new List<Song>(new[] { new Song{ Title = "Country folks" }, }) }, }); }); public class SongType { public int Id { get; set; } public string Name { get; set; } public List<Song> Songs { get; set; } } public class Song { [Column(IsIdentity = true)] public int Id { get; set; } public string Title { get; set; } public string Url { get; set; } public DateTime CreateTime { get; set; } public int TypeId { get; set; } public SongType Type { get; set; } public int Field1 { get; set; } public long RowVersion { get; set; } }

What do you mean? A table with parent-child relationship should know that the data is flat when it is checked back, and it needs to be recursively transformed into a tree. Considering the high practicability of this function, it is integrated. A piece of code from the unit test:

var repo = fsql.GetRepository<VM_District_Child>(); repo.DbContextOptions.EnableAddOrUpdateNavigateList = true; repo.DbContextOptions.NoneParameter = true; repo.Insert(new VM_District_Child { Code = "100000", Name = "China", Childs = new List<VM_District_Child>(new[] { new VM_District_Child { Code = "110000", Name = "Beijing", Childs = new List<VM_District_Child>(new[] { new VM_District_Child{ Code="110100", Name = "Beijing" }, new VM_District_Child{ Code="110101", Name = "Dongcheng District" }, }) } }) }); var t3 = fsql.Select<VM_District_Child>().ToTreeList(); Assert.Single(t3); Assert.Equal("100000", t3[0].Code); Assert.Single(t3[0].Childs); Assert.Equal("110000", t3[0].Childs[0].Code); Assert.Equal(2, t3[0].Childs[0].Childs.Count); Assert.Equal("110100", t3[0].Childs[0].Childs[0].Code); Assert.Equal("110101", t3[0].Childs[0].Childs[1].Code);

Note: entities need to configure parent-child navigation properties

In the past, FreeSql was able to perform batch data operations. For example, if the batch data exceeds some limits of the database, it will be split and executed. In fact, the performance is OK.

This requirement is also from the user, and then it is implemented. After the implementation, I also made a special performance test comparison. SQL Server bulkcopy has a relatively large income, and mysql has a very small income.

Test results (52 fields, 18W-50 rows of data, in ms):

There is no doubt about Oracle insertion performance. It is possible to install Student Edition with a large limit

Test results (10 fields, 18W-50 rows of data, in ms):

The test results are carried out under the same operating system with preheating

The ExecuteMySqlBulkCopy method is in the FreeSql.Provider.MySqlConnector Implemented in

To understand the EFCore, you should know that there is an inMemory implementation. SQLite also has a memory mode, so it's very good FreeSql.Provider.Sqlite With a little adaptation, inMemory mode can be implemented.

Using inMemory mode is very simple. You only need to modify the type created by IFreeSql and the connection string:

static IFreeSql fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.Sqlite, "Data Source=:memory:") .UseAutoSyncStructure(true) //Automatically synchronize entity structure to database .Build(); //Be sure to define Singleton singleton mode

Memory mode + FreeSql CodeFirst function, it's a good experience. Because every time the structure is migrated, fsql releases the data.

Finally finished, this article is the longest editing time in my history, lasting 4 hours!!! I could use this 4 hours to write a bunch of glue code, but I had to write promotion articles. I sincerely hope that you can move your little finger and forward the articles to let more people know that. NET has such a good ORM. Thank you!!

FreeSql open source protocol MIT https://github.com/dotnetcore/FreeSql It can be used commercially and has complete documents. QQ Development Group: 4336577

CSRedisCore said: the treatment of FreeSql is much better.

If you have a good idea of ORM implementation, please leave a message to the author for discussion, thank you for watching!

26 May 2020, 01:03 | Views: 1660