.Net open source micro-ORM framework evaluation

What is ORM?

Object Relation Mapping (English: Object Relation Mapping, referred to as ORM, or O/RM, or O/R mapping), is a programming technology used to implement different object-oriented programming languages Conversion between type system data. In effect, it actually creates a "virtual object database" that can be used in programming languages.

General ORM includes the following four parts:

An API that performs CRUD operations on persistent class objects;

A language or API used to specify queries related to classes and class attributes;

An API that specifies MAPPING METADATA Tool;

A technology that allows ORM implementation to perform DIRTYCHECKING, LAZY ASSOCIATION FETCHING and other optimization operations together with transaction objects.

The .NET ORM frameworks compared this time

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/

Comparison factors

1. Ease of operation

2. Execution efficiency

3. Cross-database usage

Entity Framework

1. Create a new C# console

2. Use NuGet to reference EF components

Right-click project reference to manage NuGet packages and download them online And install the Entity Framework

Right-click on the project to create a new item and add a new item ADO.NET Entity Data Model

I use the database name for the CLN here

After adding it, there is an entity model data wizard and choose to generate it from the database The next step is to configure the database connection. Create a new connection and set the entity connection of App.Config to CLNContext

. Then a dialog box appears -- which database objects do you want to include in the model? Check the table here and click Finish. It's OK, and then two warning boxes will pop up. This is because there are two TT templates that need to be executed. Don't worry about it, just confirm. This is where the Edmx database model relationship diagram appears

The next step is to enter the project The code is written in Program.cs

static void Main(string[] args)
        {
            Stopwatch S = new Stopwatch();  //秒表对象 计时
            S.Start();

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

        }

NT_Photo table. There are more than 600 pieces of data. Here you can see that the query speed is quite fast. EF takes 5.9 seconds

Dapper

1. Create a new console as well Program

2. NuGet references Dapper

• 

• Dapper is not as powerful as EF. It is equivalent to a SqlHelper. We need to manually configure the connection string. Here is the NT_Photo.cs model class generated by EF just now. , put it into the project, and then go into Program.cs to write the code

• 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();
          }
      }
  }

We use the SqlConnertion object here, because Dapper has extended IDbConnection, and SqlConnection implements IDbConnection, and then in our Reference Dapper's namespace using Dapper;

As you can see here, Dapper is faster than EF. Dapper takes 3.0 seconds

PetaPoco

1. Also create a new 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 installing PetaPoco, the database access context and model Model will also be automatically generated. Here open Models -> Database.tt and modify ConnectionStringName = "CLNContext"; here It must be consistent with the connection string in App.Config. After changing and saving, the database access context will be automatically generated. Models -> Database.tt -> Database.cs

5. After the preparation is completed, let’s get to the point. Now, also 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 a database access context CLNContextDB(), but it also needs to write SQL statements. Let’s take a look at the query speed first

在这里可以看到，PetaPoco貌似更快 PetaPoco用时2.4秒

其实PetaPoco更强大的是，它对模型做了增删改查的方法，这就非常方便了

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

//res就是返回插入的数据的ID

对比结果:

这里可以看到EF，Dapper，PetaPoco 的差别了

NT_Photo 600多条数据

EF ------ 5.9秒

Dapper ------- 3.0秒

PetaPoco ------- 2.4秒

其实EF第一次的话，会慢一些，第一次会把一些模型数据加载到内存中，后面就非常快了，这里贴一个EF 暖机代码

//EF暖机
    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>());
            }

总结：每个ORM的存在都有它的价值，不能说哪个哪个好，EF是微软自家推出的，很多代码都是自动生成的，一句SQL语句都不用写，确实非常方便，但是EF的包很大，有5M多，而且微软封装好的也不太利于扩展，像写一些复杂的SQl语句就不是很方便了，Dapper 和PetaPoco相比下来都是比较轻的，而且用起来的话也是非常灵活的。哪一个更适合你的项目，用起来更顺手，才是最好的选择方案。