What is an ORM tool?
ORM stands for Object-Relational Mapping. Available in languages such as Java and C#, an ORM tool is a technique that lets you query and manipulate data from a database using an object-oriented paradigm. Examples of ORM tools are Hibernate for Java and NHibernate and Entity Framework for C#.
Why do I have ORM tools?
I am not totally against ORM tools; over 90% of the projects I work with, whether it is a new, current or a legacy project, have some sort of ORM tool, which really help to maintain the project as well as promoting cleaner code, uphold the DRY rule and abstracting the database system so you can change it whenever you want. A lot of people are also used to using an ORM tool so it helps them to get on board on the project.
So where do the problems start? When the data or logic becomes slightly complex.
Firstly, implementing the simplest of database functionality can be difficult. An example being cascade deletes in Entity Framework, which I have already written regarding this here. But the real problems are when you have some slightly complex queries. For example, when you have a table with about 10 JOINs, involving children and sibling entities; for something like this, your ORM tool will spit out some god awful inefficient SQL query about 15 times the size of SQL you could have written.
What is this Dapper you speak of?
Now I’m not saying you shouldn’t use an ORM tool as most people will tell you “If you're not using an ORM, then you ultimately end up writing one.”, as it is excellent for smaller, less complex projects which is when I would ALWAYS recommend using an ORM tool. One library that makes the best of both worlds is Dapper. Dapper allows you to execute an SQL query and map the result to a list of objects. Here is a basic example:
public class {
public int ? Id {
get;
set;
}
public string Name {
get;
set;
}
public string Address {
get;
set;
}
public string Country {
get;
set;
}
}
public IEnumerable GetAccounts {
string query = "select * from Accounts";
var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBstring"].ToString());
var result = con.Query(query);
return result;
}This allows you to get a list of all Accounts which are automatically mapped to your strongly typed object, i.e. Account. The main difference between this and other ORM tools is the fact you get to dictate your SQL query; a query that an ORM tool executes will be wholly inefficient and once you have added all your indexes and database optimizations, there’s not much more you can do.
I have used Dapper with a legacy project, which originally used Entity Framework to create an SQL query with many JOIN’s, CTE’s, IN’s, the whole sha-bang, which would take several seconds to execute. Looking into the query plan and after applying all possible optimizations, about 98% of the work was due to CLUSTERED INDEX SCANs, which in SQL terms is the best-case scenario, so not much you can do to improve it. However, when I started using Dapper and writing the SQL query myself, the query took mere milliseconds although mapping the result to the objects was a pain as there were many children and sibling objects involved.
Dapper Performance
So, the query and mapping can become quite complex, but is it then faster than using Entity Framework? Only most of the time. Below are some performance benchmarks I’ve taken from the Dapper GitHub page:
Method
Duration
Hand coded (using a SqlDataReader) 47ms
Dapper ExecuteMapperQuery 49ms
ServiceStack.OrmLite (QueryById) 50ms
PetaPoco 52ms
BLToolkit 80ms
SubSonic CodingHorror 107ms
NHibernate SQL 104ms
Linq 2 SQL ExecuteQuery 181ms
Entity framework ExecuteStoreQuery 631ms
As you can see, Dapper wins quite dramatically from the common ORM tools Entity Framework and NHibernate. The only method that beats Dapper is manual SQL using ADO.NET (which I would definitely not recommend).
In conclusion, Dapper seems like the perfect tool for me: someone who likes to use an ORM tool but loves to write SQL. I don’t like the fact that I have to write the mappings manually which you have very complex queries, but I guess that’s the downside of it. I’ll leave you with my final thought: There’s nothing you can do in C#, which you can’t do faster in SQL.
Comments