Mego (2)-Net mainstream ORM framework Analysis

Then we tested the performance of each ORM framework, and we can visually see the performance differences between the ORM framework and native ADO. Let me share with you my understanding of the ORM framework and some experience in using it.

• How ORM Frames Work
• Typical ORM Framework Implementation
• EF's Most powerful ORM
• EF and Efcore defects

How ORM Frames work

All ORM frameworks work without the following diagram, except that each frame is implemented in a different degree but the ultimate goal is the same.

If it is an ORM framework then there must be a blue part of these elements, whether additions and deletions to the ORM must be the object as the starting point, using objects to construct a LINQ expression, so that we can describe in the world of objects we want to do the database operation, The final implementation of LINQ is also a lambda expression (which makes LINQ much more intuitive in code), and the more powerful ORM records metadata from object types to database objects, which can be used to translate complex lambda expressions into a common intermediate expression. This expression is actually abstract in the specific implementation of the different databases, the final intermediate expression and then the specific implementation of the specified database to generate the final SQL statement, by the ADO object to the database, if the data exists return will be written back to the CLR object.

The above summarizes the implementation of all ORM, here is the typical dapper, the reason why it is small and high performance is because there is no green part of the framework, it can be said that all ORM framework is the most streamlined (there may be other similar frameworks, There is only dapper as a typical case description), so no extra parts are naturally small and fast. However, the existence of the green part determines how strong the ORM framework is, just as EF is the only one in the world that has fully implemented the elements in the green box and is supported most comprehensively, so the EF must be heavy and slow (at least EF6 relative to other frameworks).

Typical ORM Framework implementation

1. Dapper: Need to write your own handwritten SQL statement to complete the operation, relatively simple but also dapperextensions help, can be inserted, modified and deleted without writing SQL statement full object operation, it should be pointed out that it should not be considered a complete ORM framework, Because the daily development accounted for more than the query or the need for handwritten SQL, can not be typed object.
2. Nhibernate: This is a long-dated framework that requires its own configuration of metadata to be a bit stronger than dapperextensions, but not very supportive of query expressions.
3. Sqlsugar, Chloe.orm: Although such a framework does not support LINQ but can implement its own set of lambda expressions for generating SQL statements, the overall idea is to convert SQL syntax into expression syntax, typically using variable names in expressions as aliases in generated SQL statements, with additional features that are more than more flexible and practical.
4. LINQ2DB: This type of ORM is the implementation of a more complete ORM process (see), and support a large number of databases, in short, the function is relatively strong.

EF's most powerful ORM

EF is by far the most powerful ORM, which I believe is not controversial, you can refer to this document (EF Core and EF6 Feature by Feature Comparison), The features of EF6 and Efcore are listed, and it is believed that no ORM framework implements most of these features, and here are a few features:

• LINQ queries are fully supported, and there are a number of frameworks that support LINQ but some of them are not supported, but EF is currently the most fully supported, and not fully implemented EF6 support for LINQ in the official version of Efcore until now.
• Efcore Performance Improvement: From the previous blog (Mego (1)), you can see that for a powerful ORM framework Efcore has improved quite a lot, in short, very close to the native ADO framework.
• Object inheritance: This is a good design, it allows you to inherit the relational database implementation object, there will be an equivalent parent-child table in the data table.
• Database migration function, no matter how you look at this feature, but for the development of this feature is too practical, I believe you can see in many demos using EF build database.
• The collection properties of the object are expanded, usually we also want to take out the details of the order together, more cases can also be the order data paging, this function in the large ORM is not the need to deal with their own, and then in the EF only need to write a line of code can be done.
• Many-to-many relationships: this is also a relatively common but also in other ORM relatively rare features, EF is currently the best support.
• The EF framework interfaces with Microsoft's many other frameworks that require data access, such as the ASP. Identity,asp.net WebApi ODATA.

EF and Efcore defects

Although the EF or Efcore functionality is already strong, there are a lot of deficiencies in the development process, and there are many flaws that are one of the reasons why many people use it (and EF6 performance issues, of course). The following will be listed in the EF6 of the shortcomings for everyone's reference.

insufficient data update capabilities

The ability to submit data in EF has never been possible, and all data operations in EF6 have been submitted individually by individual objects, which means that each object is sent to the database during a large amount of data submission, which is why performance is under and update and delete data must be retrieved from the database object or additional objects to operate. This problem is solved in efcore and the performance is better as seen in the previous test. However, EF does not currently support conditional updates or conditional deletions. However, this problem can be solved using this framework (Entity framework Plus), but this framework is charged.

Create Time Modification time issue

We often encounter the need to record creation time or modification time when creating and updating data in business system development, but there are only triggers to solve in either EF or Efcore, but it can be a hassle to maintain these triggers when the data table gets longer. One would think of the default value, which is supported in this EF, but EF cannot selectively insert or update the specified field, which is a dilemma.

Creating and modifying time is just one example of this kind of problem, which can be summed up when we want to insert or update data to specify that the field is generated by invoking the corresponding data expression, rather than the value of the application, here is an example for you to see, In a load-balanced architecture We need to create an order when the order serial number, this serial number must be unique and continuous, in such a multi-server concurrency only in the case of the database generated this serial number is a good choice, so as to fully guarantee that when the failure of the submission of the newly-divided serial number can be returned, At the same time, we ensure that the generation will not repeat and continuous, this time it is very necessary for ORM to automatically use the number generation function to generate values.

The curve in EF is resolved, the insert, change, or delete operations in EF6 are supported for stored procedure mappings, so you can refer to this article (Entity Framework Code First Insert, Update, and delete Stored procedures ( EF6 onwards) There is a detailed explanation, here we can make a fuss, we modify the stored procedure to call the expression we specify, so that can not use the trigger, but also to achieve the purpose. There are people here who ask that maintaining storage also requires a large price, where we can rewrite the code of the EF Database migration generation stored procedure to unify processing.

Entity Collection Properties Multi-layer expansion

Either EF or efcore has an include function that displays the collection or object properties that contain it when the entity object is returned, which is called the property expansion, which is known first. However, you can expand the properties of the object (for example, company-owner, customer-to-order), or expand the collection properties (for example, order-to-detail collection), where the properties below the collection cannot be expanded, for example, products that I want to get (for example, order-and-detail collection) This is not supported.

Supplemental EF Actual internal code is able to support this operation but is not public, if you have some knowledge of the ASP. NET WebApi OData, you can know that if your OData service is built on EF, you can support multilevel data expansion in the OData syntax. and is well implemented in EF6, but the premise is that your database is SQL Server.

Object Relationship Restrictions

For example, an order will have multiple details, each of which corresponds to a product, logically we can assume that the order and the product is a many-to-many relationship, but in the EF is not so free to operate.

There are many limitations to the operation of relationships in EF, especially in many-to-many relationships, where a hidden relational entity is generated to create associations that you cannot control, and that the primary table must be the primary key in the relationship link, but these problems are EF6 in the Efcore. However, Efcore currently does not support many-to-many relationships, at least not yet seen in the flush API.

time Stamp

It feels like some of the features in EF are designed for SQL Server, and the mandatory timestamp in EF must be a byte array, but the timestamp in MySQL is actually a datetime.

multiple Database Implementation differences

EF and Efcore support multiple databases, and the principle is that the intermediate expressions described in the previous article of the EF definition are then handed over to the parties themselves for subsequent operations. This design has the following drawbacks:

• The database supports binding data access components, such as MySQL.Data.Entity, which must work under the Mysql.data component, which is not possible if you want to switch to a MySQL component.
• The database provider implementation is too expensive, If you have time to look at the implementation code in Entityframework.sqlserver or MySQL.Data.Entity, these are the code that is maintained by the well-known company Microsoft and Oracle, where the implementation code is very complex and can be considered a very large project for a typical team.
• Database provider Implementation quality, or MySQL.Data.Entity as an example, each provider does not fully realize the various functions of EF in SQL Server as Microsoft does, and the code is a lot more complex, even if the code maintained by Oracle is equally bad, Two examples of MySQL.Data.Entity are shared below.

MySQL.Data.Entity problem One: This component is in MySQL5.6 because the primary key of the database is limited to more than 700 bytes, so it will error the primary key too long when the database operation is automatically migrated, which equals this is completely useless.

MySQL.Data.Entity problem Two: This component in MySQL5.7, due to a bug in the database (virtual columns such as "The IS null-judging error problem for" SELECT 1 ") causes the SQL script generated by a particular LINQ expression to fail to get the correct result.

MySQL.Data.Entity problem three: for inherited SQL Generation this part is completely not implemented, you will find that the generated script is wrong.

MySQL.Data.Entity problem Four: The specific implementation of the EF include operation is dependent on the cross apply (SQL Server) syntax, but in MySQL there is absolutely no, and it writes out the substitution statement, so this function does not exist under MySQL.

The above is because I participated in the EF docking MySQL project, this is my team and I took a few months to come to some of the summary, but fortunately we finally by modifying the MySQL.Data.Entity source code to solve these problems.

In summary, other frameworks are not deterministic, but the level of support for each database in EF is very asymmetric.

Summary

In EntityFramework's 10-year development process, it began to develop rapidly, But back to EntityFramework6.1.3 (March 2015) This version is like the end of the entityframework, then EntityFramework6 there is no update on this system until today, it should be Microsoft has given up its turn to Entityframew Orkcore, but the development of Entityframeworkcore is not so fast, so far has not exceeded EntityFramework6, so until today Microsoft is afraid to announce the abandonment of ENTITYFRAMEWORK6.

The above is my personal opinion, for reference only.

Mego (2)-Net mainstream ORM framework Analysis