My first experience with an ORM was with LINQ to SQL. Then soon after that I tried Entity Framework. Working with either of these is similar as the primary way you're going to write queries with these is through LINQ. The LINQ statements then are translated into SQL, and it's a pretty sweet set up most of the time.
However, these two ORMs have a lot of functionality behind the scenes which you really have to learn about when troubleshooting performance problems and such. The problem then is that the abstraction level is so high that when there's a problem it's hard to figure out how to solve it. And I mean, why spend all of this time refining your LINQ queries to spit out better SQL queries, when you can just write a straight SQL query that does what you want more efficiently?
Dapper: the micro-ORM
So that's what Dapper is all about. Dapper could be classified as a micro-ORM. It really is just a bare minimum implementation of an ORM with two main extension methods to work with. You have Query and Execute. That's all. The simplicity of the API makes it beautiful to work with. It doesn't hurt that it's also one of the fastest and most efficient ORMs out there.
There is one extra step you have to follow when working with Dapper. And that is, you have to manually write your classes that map to your database. Both Linq to SQL and Entity Framework have tight integration with Visual Studio and will do this for you. But with Dapper I had to create a class for my SQL table called CarModel...
public class CarModel
{
public int CarModelID { get; set; }
public string Make { get; set; }
public string Model { get; set; }
public string ModelUrl { get; set; }
public int Year { get; set; }
}
Here's a simple query which returns a Dictionary<int, CarModel> ...
using (var conn = Db.OpenConnection)
{
return conn.Query<CarModel>("SELECT * FROM CarModel").ToDictionary(k => k.CarModelID);
}
Here's another example where I'm getting the logged in user's UserID with a parameterized query...
public static int? UserId
{
get
{
if (!HttpContext.Current.User.Identity.IsAuthenticated) return null;
using (var conn = Db.OpenConnection)
{
return conn.Query<int>("SELECT UserId FROM UserProfile WHERE UserName = @UserName",
new { @UserName = HttpContext.Current.User.Identity.Name }).FirstOrDefault();
}
}
}
Even though Dapper isn't as rich in features as something like Entity Framework, I still feel like it's saving me time in the long run while also allowing me to write faster and more efficient applications.