A Chain comparison to Dapper
I was asked to provide a comparison between Dapper and Chain to better illustrate its capabilities. For this purpose, I'm starting with the Dapper readme:
Tortuga Chain is a set of NuGet libraries that you can add in to your project that will provide connection management, SQL generation, and object mapping.
In Chain the connections are managed for you, so instead of writing this:
//Dapper
IEnumerable<Dog> dog;
var guid = Guid.NewGuid();
using (var connection = new SqlConnection(s_ConnectionString))
{
connection.Open();
dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });
}
Assert.AreEqual(1, dog.Count());
Assert.IsNull(dog.First().Age);
Assert.AreEqual(guid, dog.First().Id);You just write this:
//Chain
var guid = Guid.NewGuid();
var dog = s_DataSource.Sql("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid }).ToCollection<Dog>().Execute(); ;
Assert.AreEqual(1, dog.Count());
Assert.IsNull(dog.First().Age);
Assert.AreEqual(guid, dog.First().Id);But this example isn't very realalistic. So lets add some records:
//Dapper
var originalDog = new Dog() { Age = 2, Name = "Fido", Weight = 2.5f };
Guid key;
using (var connection = new SqlConnection(s_ConnectionString))
{
const string insertSql = "INSERT INTO dbo.Dog (Age, Name, Weight) OUTPUT Inserted.Id VALUES (@Age, @Name, @Weight);";
key = connection.ExecuteScalar<Guid>(insertSql, originalDog);
}
//And then re-read it back
Dog fetchedDog;
using (var connection = new SqlConnection(s_ConnectionString))
{
const string selectSql = "SELECT Age, Name, Weight FROM dbo.Dog WHERE Id = @Id;";
fetchedDog = connection.Query<Dog>(selectSql, new { Id = key }).Single();
}
Assert.AreEqual(originalDog.Age, fetchedDog.Age);
Assert.AreEqual(originalDog.Name, fetchedDog.Name);
Assert.AreEqual(originalDog.Weight, fetchedDog.Weight);That's a lot of SQL to write. And while we belive in the power of SQL, we don't always like writing it:
//Chain
var originalDog = new Dog() { Age = 2, Name = "Fido", Weight = 2.5f };
var key = s_DataSource.Insert("dbo.Dog", originalDog).ToGuid().Execute();
//And then re-read it back
var fetchedDog = s_DataSource.GetByKey("dbo.Dog", key).ToObject<Dog>().Execute();
Assert.AreEqual(originalDog.Age, fetchedDog.Age);
Assert.AreEqual(originalDog.Name, fetchedDog.Name);
Assert.AreEqual(originalDog.Weight, fetchedDog.Weight);Sometimes you don't know what to expect, so Chain offers three differnt ways of getting dynamic results:
//DataTable - When you want to bind to WinForms or WPF data grids.
DataTable dt = s_DataSource.Sql("select 1 A, 2 B union all select 3, 4").ToDataTable().Execute();
//Table - When you want a lightweight alternative to DataTable
Table table = s_DataSource.Sql("select 1 A, 2 B union all select 3, 4").ToTable().Execute();
//Dynamic Objects - When you want the convience of dynamic typing
List<dynamic> rows = s_DataSource.Sql("select 1 A, 2 B union all select 3, 4").ToDynamicCollection().Execute();var sql = @"
set nocount on
create table #t(i int)
set nocount off
insert #t
select @a a union all select @b
set nocount on
drop table #t";
//Truly returning no results
s_DataSource.Sql(sql, new { a = 1, b = 2 }).Execute();
//Or if you need the rows affected count
var affected = s_DataSource.Sql(sql, new { a = 1, b = 2 }).AsRowsAffected().Execute();Want to conveniently and efficiently execute a command multiple times (for example to bulk-load data)?
//Dapper
connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"Well you can't do that in Chain:
//Chain
s_DataSource.Insert("dbo.MyTable", new { colA = 1, colB = 1 }).Execute();
s_DataSource.Insert("dbo.MyTable", new { colA = 2, colB = 2 }).Execute();
s_DataSource.Insert("dbo.MyTable", new { colA = 3, colB = 3 }).Execute();But we do support bulk inserts.
s_DataSource.InsertBulk("dbo.MyTable", myCollection).Execute();A key feature of Chain is performance. The following metrics are based on unoffical runs against Frans Bouma's RawDataAccessBencher:
- Fetching 31465 rows
- Fetching 1 row
Results per framework. Values are given as: 'mean (standard deviation)'
==============================================================================
Handcoded materializer using DbDataReader : 160.50ms (4.88ms)
Handcoded materializer using DbDataReader (GetValues(array), boxing) : 166.47ms (2.27ms)
Chain v0.7.5927.4601 with Compiled Materializers : 168.82ms (5.02ms)
LINQ to DB v1.0.7.3 (v1.0.7.3) (normal) : 173.59ms (4.34ms)
Raw DbDataReader materializer using object arrays : 176.37ms (1.40ms)
LINQ to DB v1.0.7.3 (v1.0.7.3) (compiled) : 179.14ms (16.55ms)
PetaPoco Fast v4.0.3 : 184.00ms (3.39ms)
LLBLGen Pro v5.0.0.0 (v5.0.0), Poco typed view with QuerySpec : 192.11ms (8.95ms)
LLBLGen Pro v5.0.0.0 (v5.0.0), Poco typed view with Linq : 192.27ms (1.93ms)
PetaPoco v4.0.3 : 211.58ms (16.01ms)
Dapper v1.40.0.0 : 212.84ms (3.96ms)
ServiceStack OrmLite v4.0.50.0 (v4.0.50.0) : 217.17ms (18.20ms)
Entity Framework v6.0.0.0 (v6.1.40302.0) : 221.27ms (5.46ms)
Linq to Sql v4.0.0.0 (v4.6.1038.0) : 229.89ms (16.24ms)
Entity Framework v7.0.0.0 (v7.0.0.11116) : 290.73ms (6.71ms)
LLBLGen Pro v5.0.0.0 (v5.0.0), DataTable based TypedView : 368.16ms (26.71ms)
Massive using dynamic class : 623.04ms (3.96ms)
Oak.DynamicDb using dynamic Dto class : 676.64ms (22.29ms)
Results per framework. Values are given as: 'mean (standard deviation)'
==============================================================================
Handcoded materializer using DbDataReader (GetValues(array), boxing) : 0.19ms (0.01ms) per individual fetch
Handcoded materializer using DbDataReader : 0.19ms (0.01ms) per individual fetch
Chain v0.7.5927.4601 with Compiled Materializers : 0.21ms (0.01ms) per individual fetch
Dapper v1.40.0.0 : 0.21ms (0.02ms) per individual fetch
Raw DbDataReader materializer using object arrays : 0.28ms (0.02ms) per individual fetch
Massive using dynamic class : 0.29ms (0.02ms) per individual fetch
Oak.DynamicDb using dynamic Dto class : 0.36ms (0.15ms) per individual fetch
ServiceStack OrmLite v4.0.50.0 (v4.0.50.0) : 0.41ms (0.15ms) per individual fetch
LINQ to DB v1.0.7.3 (v1.0.7.3) (compiled) : 0.41ms (0.03ms) per individual fetch
LLBLGen Pro v5.0.0.0 (v5.0.0), Poco typed view with QuerySpec : 0.48ms (0.01ms) per individual fetch
LINQ to DB v1.0.7.3 (v1.0.7.3) (normal) : 0.49ms (0.02ms) per individual fetch
PetaPoco Fast v4.0.3 : 0.51ms (0.04ms) per individual fetch
LLBLGen Pro v5.0.0.0 (v5.0.0), DataTable based TypedView : 0.72ms (0.02ms) per individual fetch
Entity Framework v7.0.0.0 (v7.0.0.11116) : 0.79ms (0.03ms) per individual fetch
Entity Framework v6.0.0.0 (v6.1.40302.0) : 1.76ms (0.45ms) per individual fetch
Linq to Sql v4.0.0.0 (v4.6.1038.0) : 1.98ms (0.02ms) per individual fetch
LLBLGen Pro v5.0.0.0 (v5.0.0), Poco typed view with Linq : 2.00ms (0.81ms) per individual fetch
PetaPoco v4.0.3 : 4.34ms (0.04ms) per individual fetch
We plan on officially submitting our framwork to Frans Bouma's RawDataAccessBencher test suite later this year.
Parameters are passed in as anonymous classes, normal classes, or dictionaries. This allow you to build your parameters the way you want to.
Let's say you that your database has two tables: Employee and EmployeeDetail. There's a one-to-one map between them, so you often need update them both at the same time. In Chain, you'd write:
using (var trans = s_DataSource.BeginTransaction())
{
trans.Update("HR.Employee", employeeModel).Execute();
trans.Update("HR.EmployeeDetail", employeeModel).Execute();
trans.Commit();
}Chain is smart enough to know which properties on employeeModel applies to the 'Employee table and which applies to the EmployeeDetail table.
How you ask?
Well Chain just asks. When you touch a table, Chain will download the table's schema. Using that information, it can produce accurate SQL that doesn't depend on manually configured XML.
Chain allow you to pass in IEnumerable and will automatically parameterize your query, but only for GetByKey operations (sorry).
//List support is only available for primary keys.
var posts = s_DataSource.GetByKey("dbo.Posts", 1, 2, 3).ToCollection<Post>().Execute();EDIT: DeleteByKey and UpdateByKey are now supported.
Chain's behavior is to execute your sql and buffer the entire reader on return. At this time does not support streaming objects.
Chain allows you to map a single row to multiple objects. This is a key feature if you want to avoid extraneous querying and eager load associations.
In Dapper, you can use this syntax:
//Dapper
var sql =
@"select * from #Posts p
left join #Users u on u.Id = p.OwnerId
Order by p.Id";
var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = data.First();
post.Content.IsEqualTo("Sams Post1");
post.Id.IsEqualTo(1);
post.Owner.Name.IsEqualTo("Sam");
post.Owner.Id.IsEqualTo(99);important note Dapper assumes your Id columns are named "Id" or "id", if your primary key is different or you would like to split the wide row at point other than "Id", use the optional 'splitOn' parameter.
We think that's a bit more complicated than we like, especially if you want to do that to multiple properties. So we introduced the [Decompose] attribute:
//Chain
public class Post
{
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int OwnerId { get; set; }
[Decompose("Owner")]
public User Owner { get; set; }
}
var data = s_DataSource.From("dbo.PostsWithOwnersView").ToCollection<Post>().Execute();
var post = data.First();
Assert.AreEqual("Sams Post1", post.Content);
Assert.AreEqual(1, post.Id);
Assert.AreEqual("Sam", post.Owner.Name);
Assert.AreEqual(99, post.Owner.Id);Both Dapper and Chain allow you to process multiple result grids in a single query.
Example:
var sql = @"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";
var selectedId = 1;
//Dapper
using (var connection = new SqlConnection(s_ConnectionString))
{
using (var multi = connection.QueryMultiple(sql, new { id = selectedId }))
{
var customer = multi.Read<Customer>().SingleOrDefault();
var orders = multi.Read<Order>().ToList();
var returns = multi.Read<Return>().ToList();
}
}
//Chain
var tableSet = s_DataSource.Sql(sql, new { id = selectedId }).ToTableSet("Customer", "Order", "Return").Execute();
var customer = tableSet["Customer"].ToObjects<Customer>().SingleOrDefault();
var orders = tableSet["Order"].ToObjects<Order>();
var returns = tableSet["Return"].ToObjects<Return>();As you can see, we're not a fan of using blocks. But we can do better if C# 7 gives us multi-value returns.
Dapper fully supports stored procs:
//Dapper
var user = cnn.Query<User>("spGetUser", new {Id = 1},
commandType: CommandType.StoredProcedure).SingleOrDefault();If you want something more fancy, you can do:
//Dapper
var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure);
int b = p.Get<int>("@b");
int c = p.Get<int>("@c"); Chain supports them too:
//Chain
var user = s_DataSource.Procedure("dbo.spGetUser", new { Id = 1 }).ToObject<User>(RowOptions.AllowEmptyResults).Execute();
//With OUT parameters
var p = new List<SqlParameter>();
p.Add(new SqlParameter("@a", 11));
p.Add(new SqlParameter("@b", SqlDbType.Int) { Direction = ParameterDirection.Output });
p.Add(new SqlParameter("@c", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue });
s_DataSource.Procedure("dbo.spMagicProc", p).Execute();
int b = (int)p[1].Value;
int c = (int)p[2].Value;
Assert.AreEqual(10, b);
Assert.AreEqual(5, c);Dapper supports varchar params, if you are executing a where clause on a varchar column using a param be sure to pass it in this way:
//Dapper
Query<Thing>("select * from Users where Name = @Name", new {Name = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true });On SQL Server it is crucial to use the unicode when querying unicode and ansi when querying non unicode. That's why Chain does it for you:
//Chain
var users = s_DataSource.From("dbo.Users", new { Name = "abcde" }).ToCollection<User>().Execute();As we mentioned before, Chain asks your database for its schema. So it (almost) always remembers when to use ANSI strings and when to use Unicode. (When using raw SQL, you'll still need to explicitly indicate your parameter's DB type.)
Chain defaults to reflection based materializers. If you need more performance, you can use compiled materializers. But do note that compiled materializers caches information about every query it runs. So if you are generating SQL strings on the fly without using parameters, stop. There's no real reason to do that and you'll not only exhaust your application's memory, but also screw up the execution plan cache on your database.
Chain's simplicity means that some features that ORMs ship with are stripped out. Specifically, it can't materialize complex object graphs.
Chain manages your connection's lifecycle, but it needs a little help from you. Create one DataSource per connection and share it freely accross your data access layer.
Yes, but it works better if are using SQL Server, SQLite, PostgreSQL, or Access. (Oracle and MySQL coming soon.)
While Chain does have a generic data source that works with any .NET ADO provider, most of the functionality we showed today is dependent on having a deep knowledge of the database. In many cases we're going to generate different SQL for SQL Server or SQLite to take advantage of their unique capabilites.
Not yet, but we're adding them to the Wiki as we go along.