. NET open source Micro ORM Framework Assessment

What is ORM?

Object-Relational mapping (English: Objects Relation Mapping, abbreviated ORM, or O/RM, or O/R Mapping) is a program technology that enables the conversion of data between different types of systems in an object-oriented programming language. In effect, it actually creates a "virtual object database" that can be used in a programming language.

• The general ORM consists of the following four parts:
• An API for CRUD operations on persistent class objects;
• A language or API used to specify queries related to class and class attributes;
• A tool that prescribes mapping metadata;
• A technique that allows an ORM to be dirtychecking with a transaction pair , LAZY Association fetching, and other optimizations.

The. NET ORM Framework for this comparison

1. Entity Framework

Official website https://msdn.microsoft.com/zh-cn/data/ef.aspx

2. Dapper

Official website Https://github.com/StackExchange/dapper-dot-net

3. Petapoco

Official website http://www.toptensoftware.com/petapoco/

Contrast factor

1. Ease of operation

2. Efficiency of implementation

3. Cross-database usage

Entity Framework

1. Create a new C # console

2. Using NuGet to reference EF components

Project reference Right-click Management nuget Package download and install the entity Framework Online

Project Right-click New Add New Item ADO. NET Entity Data Model

My CLN here is a database name.

Added later there is an Entity Model Data Wizard selected from the database build next here is the configuration database connection new connection set the entity connection for App. Config to Clncontext

Then there is a dialog box-which database objects you want to include in the model, check the table here, click Done OK, and then pop up two warning boxes, this is because there are two TT template need to execute, do not control it, OK on the line, this is the EDMX Database model diagram

The next step is to enter the project's Program.cs and write the code.

  Static void Main (string[] args)        {            new  Stopwatch ();  Stopwatch Object Timing            s.start ();             var New Clncontext ();             foreach (var in dbcontext.nt_photo)            {                Console.WriteLine (item. Postip);            }                       Console.WriteLine (s.elapsed);            Console.readkey ();        }

Nt_photo table has more than 600 data, here to see the speed of the query is pretty fast EF spents 5.9 seconds

Dapper

1. Also create a new console program

2. NuGet References Dapper

Dapper is not as powerful as EF, which is equivalent to a sqlhelper, we need to manually configure the connection string, here the EF generated Nt_photo.cs model class, put in the project, and then it is to enter into the Program.cs to write code

usingSystem;usingSystem.Collections.Generic;usingSystem.Configuration;usingSystem.Data.SqlClient;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingDapper;usingSystem.Diagnostics;usingSystem.Threading;namespacedapperforsql{classProgram {Static voidMain (string[] args) {Stopwatch W=NewStopwatch ();            W.start (); varstr ="data source=.; Initial catalog=cln20140830;integrated security=true"; SqlConnection Con=NewSqlConnection (str); varList = Con.query<nt_photo> ("SELECT * from Nt_photo"); foreach(varIteminchlist) {Console.WriteLine (item.            POSTIP);            } Console.WriteLine (w.elapsed);        Console.readkey (); }    }}

We use the Sqlconnertion object here, because Dapper is the extension to IDbConnection, SqlConnection is the implementation of IDbConnection, and then in our reference Dapper the namespace using Dapper ;

As you can see here, dapper is faster than EF dapper spents 3 seconds

Petapoco

1. Also create a new console program

2. Referencing the Petapoco component with NuGet

3. Configure the connection string in app. Config

<?xml version="1.0"encoding="Utf-8"?><configuration> <connectionStrings> <add name="Clncontext"connectionstring="data source=.; Initial catalog=cln20140830;integrated security=true;"Providername="System.Data.SqlClient"/> </connectionStrings> <startup> <supportedruntime version="v4.0"sku=". netframework,version=v4.5"/> </startup></configuration>

4. After installing the Petapoco, the database access context and model are also automatically generated, where Models-a database.tt is opened, and connectionStringName = "Clncontext" is modified; This will be consistent with the connection string in app. Config, and will automatically generate the database access context, Models, Database.tt, Database.cs

5. The preparatory work is complete, is to enter the subject, also entered into the Program.cs

   Static voidMain (string[] args) {                       varContext =NewClncontext.clncontextdb (); Stopwatch s=NewStopwatch ();            S.start (); varList = Context.query<nt_photo> ("SELECT * from Nt_photo"); foreach(varIteminchlist) {Console.WriteLine (item.            POSTIP);            } Console.WriteLine (s.elapsed);        Console.readkey (); }

Here Petapoco also has the database access context Clncontextdb (), but also need to write SQL statements, first look at the query speed

As you can see here, Petapoco looks faster Petapoco spents 2.4 seconds

In fact, Petapoco more powerful is that it has made additions and deletions to the model method, which is very convenient

Newvar res= PP. Insert ();  

RES is the ID that returns the inserted data.

Comparison results:

You can see the difference in Ef,dapper,petapoco here.

Nt_photo more than 600 data

EF------5.9 seconds

Dapper-------3 seconds

Petapoco-------2.4 seconds

In fact, the first time, the EF will be slower, the first time some model data will be loaded into memory, the back is very fast, here to paste a EF warm-up code

        
   EF warming Machine
    using (varnew  clncontext ())            {                var objectContext = ((iobjectcontextadapter) db). ObjectContext;                 var mappingcollection = (System.Data.Entity.Core.Mapping.StorageMappingItemCollection) ObjectContext.MetadataWorkspace.GetItemCollection (System.Data.Entity.Core.Metadata.Edm.DataSpace.CSSpace);                Mappingcollection.generateviews (new system.collections.generic.list< System.data.entity.core.metadata.edm.edmschemaerror>());            }

Summary: The existence of each ORM has its value, can not say which is good, EF is Microsoft's own launch, a lot of code is automatically generated, a SQL statement does not have to write, it is very convenient, but the EF package is large, there are more than 5M, and the Microsoft package is not conducive to the expansion of good Like writing some complex SQL statements is not very convenient, Dapper and Petapoco compared down are relatively light, and the use of the words are very flexible. Which is better for your project, more comfortable to use, is the best option.

. NET open source Micro ORM Framework Assessment