ORM

1. What are ORMs and How does it work?

It is definitely a brainstorming task to develop an application that suits business services and concurrently boost their conversion. Whenever businesses think of developing an application, they also need to think about its maintainability, up gradation as per technology and improving overall performance. These indispensable factors keep the application stable for a long time and assist businesses to achieve the best results using ORM tools for most of their applications. These tools help them to separate the database design from object-oriented design. But in parallel, if the performance of the application is also an important factor then this performance factor should also be added by software developers to the list while deciding to choose an ORM software. Otherwise, after a long time, it might be difficult to handle the performance factor using that ORM.

2. What is Object-relational Mapping (ORM)?

Object-relational mapping (ORM) is a layer that converts our data between Database and object-oriented entities using object-oriented programming (OOP) language.

Object relational Mapping

There are several ORM tools available in the market. Given below are some of the most commonly used tools required to build applications and they are

  • Entity Framework
  • Dapper
  • NHibernate

In this article, we will compare different features of the ORMs with ADO.NET. We will also compare the performance of both these frameworks using a sample project. So that it helps us to choose an ORM that best fits our application requirements.

3. Features Comparison in ORMs

Different ORMs support different features and for comparison, we have listed below the known and commonly used features of the different ORMs and its convenient output.

Features Comparison

4. Performance Comparison in ORMs

To measure the performance, Software developers will have to practically check the performance of different ORMs. Users can download the sample project and required database script files from here. Then later start with setting up a Database environment.

1. Environment Setup – Database

Create a database schema with three different tables. You can use the schema.sql script file to create a database. And to generate dummy data, use data.sql script file. You can find both script files and dummy data files with a sample project.

  • Project (1 to n relationship with Team table)
  • Team (1 to n relationship with TeamMember table)
  • TeamMember

Environment Setup

For our performance test, we have added,

  • 500 data for Project, 1000 data for Team and 1000000 data for TeamMember

And we will execute queries to get the below results on multiple iterations,

  • Get TeamMember by TeamMemberId
  • Get TeamMembers by TeamId
  • Get TeamMembers by ProjectId

2. Environment Setup – Code

For our performance test, our dedicated software development team have created a sample project and added a layer class for each framework. The layer class connects and executes the query to the database. So, basically, we will check how fast the given framework creates the query, fetch the result, and map the objects.

Also, to maintain the same behavior in all the frameworks, we have calculated execution time by including the below points,

  • Model mapping – The Ado.Net framework is returning results into SqlDataAdapter. So, we also need to manually map the result with the relevant model as for other ORMs we are getting results into its relevant model.
  • Disable change tracking – We have disabled the default change tracking behavior of EF, EF Core and NHibernate ORMs while performing the query.

For example, if you want to check the given added code snippet for GetTeamMemberByProject method of Ado.Net, Dapper, EF Core and NHibernate frameworks.

Ado. Net

public TestResult GetTeamMemberByProject(int projectId)
    	{
        	Stopwatch watch = new Stopwatch();
        	watch.Start();
        	DataTable table = new DataTable();
        	TestResult result = new TestResult();
        	List teamMembers = new List();
 
        	using (SqlConnection conn = new SqlConnection(_connectionString))
        	{
            	conn.Open();
            	using (SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT [TM].*
                                                                     FROM [dbo].[TeamMember] AS [TM]
                                                                     INNER JOIN [dbo].[Team] AS [T]
                                                                     ON [TM].[TeamID] = [T].[ID]
                                                                     WHERE [T].[ProjectID] = @ID
                                                                     ORDER BY [TM].[FirstName]", conn))
     	       {
                    adapter.SelectCommand.Parameters.Add(new SqlParameter("@ID", projectId));
                	adapter.Fill(table);
            	}
        	}
 
        	foreach (DataRow row in table.Rows)
        	{
            	teamMembers.Add(new TeamMember
            	{
                	ID = Convert.ToInt32(row["ID"]),
                	FirstName = Convert.ToString(row["FirstName"]),
                	LastName = Convert.ToString(row["LastName"]),
                	TeamID = Convert.ToInt32(row["TeamID"]),
                	DateOfBirth = Convert.ToDateTime(row["DateOfBirth"])
            	});
        	}
 
        	watch.Stop();
        	result.MemberCount = teamMembers.Count;
     	   result.Time = watch.ElapsedMilliseconds;
        	return result;
    	}

Dapper

public TestResult GetTeamMemberByProject(int projectId)
   	{
        	Stopwatch watch = new Stopwatch();
        	watch.Start();
        	List teamMembers = new List();
        	TestResult result = new TestResult();
 
        	using (SqlConnection conn = new SqlConnection(_connectionString))
        	{
            	conn.Open();
            	teamMembers = conn.Query(@"SELECT [TM].*
                                                       FROM [dbo].[TeamMember] AS [TM]
                                                       INNER JOIN [dbo].[Team] AS [T]
                                                       ON [TM].[TeamID] = [T].[ID]
                                                       WHERE [T].[ProjectID] = @ID
                                                       ORDER BY [TM].[FirstName]", new { ID = projectId })
                                                     .ToList();
        	}
 
        	watch.Stop();
        	result.MemberCount = teamMembers.Count;
        	result.Time = watch.ElapsedMilliseconds;
        	return result;
    	}

EF Core

public TestResult GetTeamMemberByProject(int projectId)
{
Stopwatch watch = new Stopwatch();
   	watch.Start();
   	List teamMembers = new List();
   	TestResult result = new TestResult();
 
   	using (EfCoreDbContext context = new EfCoreDbContext(_connectionString))
   	{
   	   	teamMembers = context.Teams.AsNoTracking()
                            	.Include(x => x.TeamMembers)
                            	.Where(x => x.ProjectID == projectId)
          	                  .SelectMany(x => x.TeamMembers)
                            	.Select(x => new TeamMember
                            	{
                                	ID = x.ID,
                                	DateOfBirth = x.DateOfBirth,
                                	FirstName = x.FirstName,
                                	LastName = x.LastName,
                                	TeamID = x.TeamID
                            	}).OrderBy(x => x.FirstName).ToList();
	   }
 
       watch.Stop();
   	result.MemberCount = teamMembers.Count;
   	result.Time = watch.ElapsedMilliseconds;
   	return result;
}

NHibernate

public TestResult GetTeamMemberByProject(int projectId)
    	{
        	Stopwatch watch = new Stopwatch();
        	watch.Start();
        	TestResult result = new TestResult();
        	List teamMembers = new List();
 
        	using (IStatelessSession session = NHibernateHelper.OpenStatelessSession())
        	{
            	teamMembers = session.QueryOver()
                                .JoinQueryOver(x => x.Team)
       	                     .Where(x => x.ProjectID == projectId).List()
                            	.Select(x => new TeamMember()
                            	{
                                	ID = x.Id,
                                	FirstName = x.FirstName,
                                	LastName = x.LastName,
                                	DateOfBirth = x.DateOfBirth,
                                	TeamID = x.TeamID
                            	}).OrderBy(x => x.FirstName).ToList();
        	}
 
        	watch.Stop();
        	result.MemberCount = teamMembers.Count;
        	result.Time = watch.ElapsedMilliseconds;
        	return result;
    	}

Results

Now, let’s check the result of different queries for the different ORMs with SQL Server.

  • Get TeamMember by TeamMemberId
    TeamMemberId
  • Get TeamMembers by TeamId
    TeamId
  • Get TeamMembers by ProjectId
    ProjectId

Analysis

If you have noticed the results, after including model mapping as well as disabling change tracking, all ORMs have nearly a large difference in execution time. Also, for the very first iteration, EF & EF Core consuming a little more execution time compared to others but for the next iterations, both have almost the same execution time.

Modify the sample

You can also explore the sample project for more details. And based on your requirements, you can modify the script to add dummy data and modify the constant parameters (like Count of Iteration, the total count of each table).

5. A Quick Recap

It’s always best to use the right tool for the right job.

In this article, Our software programmers have compared the commonly used features which are supported by different ORMs. Also, we have checked the performance of ORMs on different query results. So, based on Software developers’ requirements, we have comprehensively described how each feature works and now you have a simplified way to choose the right ORM framework for any application.

profile-image
Vishal Shah

Vishal Shah has an extensive understanding of multiple application development frameworks and holds an upper hand with newer trends in order to strive and thrive in the dynamic market. He has nurtured his managerial growth in both technical and business aspects and gives his expertise through his blog posts.

Related Service

Know more about our Custom Software Development Service

Learn More

Want to Hire Skilled Developers


    Comments

    • Leave a message...

    Related Articles
    How to setup your own MQTT broker on Azure
    How to setup your own MQTT broker on Azure
    Oct 7, 2021
    VueJS Guide for Beginners
    VueJS Guide for Beginners
    Aug 24, 2021
    What is a WIX Toolset?
    What is a WIX Toolset?
    Apr 15, 2021