. Net open-source micro-ORM Framework evaluation,. netorm framework

. Net open-source micro-ORM Framework evaluation,. netorm framework

What is ORM?

Object relationship Mapping (ORM, O/RM, or O/R mapping) is a program technology, it is used to convert data of different types of systems in object-oriented programming languages. In effect, it is actually a "Virtual Object Database" that can be used in programming languages ".

• The general ORM includes the following four parts:
• An API for performing CRUD operations on persistent class objects;
• A language or API is used to specify queries related to classes and class attributes;
• A tool that specifies mapping metadata;
• One technology allows the implementation of ORM to perform DIRTYCHECKING, lazy association fetching, and other optimization operations together with the transaction object.

The comparison of the. net orm framework

1. Entity Framework

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

2. Dapper

Official https://github.com/StackExchange/dapper-dot-net

3. PetaPoco

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

Comparison Factors

1. ease of operation

2. execution efficiency

3. Cross-database use

Entity Framework

1. Create a C # Console 

2. Use NuGet to reference the EF component

Project Reference right-click to manage NuGet packages download and install Entity Framework online

Right-click the project to create a new ADO. NET object data model.

The CLN here uses the database name.

After adding the object model, the data wizard selects to generate from the database. Next, configure the database connection. Create a connection and set the object connection of App. Config to CLNContext

Then, a dialog box is displayed, Which database objects you want to include in the model. Check the table and click Finish. Then, two warning boxes are displayed, this is because there are two TT templates that need to be executed. You don't have to worry about it. OK. This is the relationship diagram of the Edmx database model.

Next, enter the project's Program. cs and write the code.

static void Main(string[] args)
        {
            Stopwatch S = new Stopwatch();  //Stopwatch object Timing             S.Start();

            var DBContext = new CLNContext();
            foreach (var item in DBContext.NT_Photo)
            {
                Console.WriteLine(item.PostIP);
            }
           
            Console.WriteLine(S.Elapsed);
            Console.ReadKey();

        }

 The NT_Photo table contains more than 600 pieces of data. here we can see that the query speed is quite fast. EF takes 5.9 seconds.

Dapper

1. Create a 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 we put the NT_Photo.cs model class generated by EF in the project, and then enter the Program. code written in cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Diagnostics;
using System.Threading;

namespace DapperForsql
{
    class Program
    {
        static void Main(string[] args)
        {
            
            Stopwatch w = new Stopwatch();
            w.Start();
            var str = "data source=.;initial catalog=CLN20140830;integrated security=True";
            SqlConnection Con = new SqlConnection(str);
            var list = Con.Query<NT_Photo>("select * from NT_Photo");

            foreach (var item in list)
            {
                Console.WriteLine(item.PostIP);
            }
            Console.WriteLine(w.Elapsed);
            Console.ReadKey();
        }
    }
}

 SqlConnertion object is used here, because Dapper extends IDbConnection, SqlConnection implements IDbConnection, and then using Dapper, The namespace of Dapper, is referenced;

Dapper is faster than EF. Dapper takes 3.0 seconds.

PetaPoco

1. Create a console Program

2. Use NuGet to reference the PetaPoco component

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 PetaPoco is installed, the Database access context and Model are automatically generated. Choose Models> Database. tt: Modify ConnectionStringName = "CLNContext. the connection strings in Config are consistent. If you save the changes, the Database access context is automatically generated, Models-> Database. tt-> Database. cs

5. After the preparation is complete, you can start the topic and enter Program. cs.

static void Main(string[] args)
        {
           
            var Context = new CLNContext.CLNContextDB();
            Stopwatch s = new Stopwatch();
            s.Start();
            var list = Context.Query<NT_Photo>("select * from NT_Photo");
            foreach (var item in list)
            {
                Console.WriteLine(item.PostIP);
            }
            Console.WriteLine(s.Elapsed);
            Console.ReadKey();


        }

Here, PetaPoco also has the database access context CLNContextDB (), but it also needs to write SQL statements. Let's take a look at the query speed first.

We can see that PetaPoco seems to be faster. PetaPoco takes 2.4 seconds.

What makes PetaPoco more powerful is that it adds, deletes, modifies, and queries the model, which is very convenient.

NT_Photo PP = new NT_Photo (); var res = PP. Insert ();

// Res is the ID of the inserted data.

Comparison result:

Here we can see the difference between EF, Dapper, and PetaPoco.

NT_Photo more than 600 data records

EF ------ 5.9 seconds

Dapper ------- 3.0 seconds

PetaPoco ------- 2.4 seconds

In fact, if EF is used for the first time, it will be slower. For the first time, it will load some model data into the memory, and the later part will be very fast. Here is an EF warm-up machine code

// EF warm-up
 using (var db = new 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. It cannot be a good one. EF is launched by Microsoft. Many codes are automatically generated, and no SQL statement is required, it is indeed very convenient, but EF has a large package with more than 5 MB, and Microsoft's encapsulated package is not very conducive to expansion. It is not very convenient to write some complicated SQL statements, compared with PetaPoco, Dapper is light and flexible to use. Which of the following is the best choice for your project.