Skip to content
forked from DapperLib/Dapper

Dapper - a simple object mapper for .Net

License

Notifications You must be signed in to change notification settings

Simon-Carr/Dapper

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Dapper - a simple object mapper for .Net

Build status

Release Notes

Located at stackexchange.github.io/Dapper

Packages

MyGet Pre-release feed: https://www.myget.org/gallery/dapper

Package NuGet Stable NuGet Pre-release Downloads MyGet
Dapper Dapper Dapper Dapper Dapper MyGet
Dapper.Contrib Dapper.Contrib Dapper.Contrib Dapper.Contrib Dapper.Contrib MyGet
Dapper.EntityFramework Dapper.EntityFramework Dapper.EntityFramework Dapper.EntityFramework Dapper.EntityFramework MyGet
Dapper.EntityFramework.StrongName Dapper.EntityFramework.StrongName Dapper.EntityFramework.StrongName Dapper.EntityFramework.StrongName Dapper.EntityFramework.StrongName MyGet
Dapper.Rainbow Dapper.Rainbow Dapper.Rainbow Dapper.Rainbow Dapper.Rainbow MyGet
Dapper.SqlBuilder Dapper.SqlBuilder Dapper.SqlBuilder Dapper.SqlBuilder Dapper.SqlBuilder MyGet
Dapper.StrongName Dapper.StrongName Dapper.StrongName Dapper.StrongName Dapper.StrongName MyGet

Features

Dapper is a NuGet library that you can add in to your project that will extend your IDbConnection interface.

It provides 3 helpers:

Execute a query and map the results to a strongly typed List

public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

Example usage:

public class Dog
{
    public int? Age { get; set; }
    public Guid Id { get; set; }
    public string Name { get; set; }
    public float? Weight { get; set; }

    public int IgnoredProperty { get { return 1; } }
}

var guid = Guid.NewGuid();
var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });

Assert.Equal(1,dog.Count());
Assert.Null(dog.First().Age);
Assert.Equal(guid, dog.First().Id);

Execute a query and map it to a list of dynamic objects

public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

This method will execute SQL and return a dynamic list.

Example usage:

var rows = connection.Query("select 1 A, 2 B union all select 3, 4").AsList();

Assert.Equal(1, (int)rows[0].A);
Assert.Equal(2, (int)rows[0].B);
Assert.Equal(3, (int)rows[1].A);
Assert.Equal(4, (int)rows[1].B);

Execute a Command that returns no results

public static int Execute(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

Example usage:

var count = connection.Execute(@"
  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", new {a=1, b=2 });
Assert.Equal(2, count);

Execute a Command multiple times

The same signature also allows you to conveniently and efficiently execute a command multiple times (for example to bulk-load data)

Example usage:

var count = 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 } }
  );
Assert.Equal(3, count); // 3 rows inserted: "1,1", "2,2" and "3,3"

This works for any parameter that implements IEnumerable for some T.

Performance

A key feature of Dapper is performance. The following metrics show how long it takes to execute a SELECT statement against a DB (in various config, each labeled) and map the data returned to objects.

The benchmarks can be found in Dapper.Tests.Performance (contributions welcome!) and can be run via:

dotnet run -p .\benchmarks\Dapper.Tests.Performance\ -c Release -f netcoreapp3.1 -- -f * --join

Output from the latest run is:

BenchmarkDotNet=v0.12.1, OS=Windows 10.0.19041.208 (2004/?/20H1)
Intel Core i7-7700HQ CPU 2.80GHz (Kaby Lake), 1 CPU, 8 logical and 4 physical cores
.NET Core SDK=3.1.201
  [Host]   : .NET Core 3.1.3 (CoreCLR 4.700.20.11803, CoreFX 4.700.20.12001), X64 RyuJIT
  ShortRun : .NET Core 3.1.3 (CoreCLR 4.700.20.11803, CoreFX 4.700.20.12001), X64 RyuJIT
ORM Method Return Mean StdDev Error Gen 0 Gen 1 Gen 2 Allocated
Belgrade ExecuteReader Post 94.46 ÎĽs 8.115 ÎĽs 12.268 ÎĽs 1.7500 0.5000 - 8.42 KB
Hand Coded DataTable dynamic 105.43 ÎĽs 0.998 ÎĽs 1.508 ÎĽs 3.0000 - - 9.37 KB
Hand Coded SqlCommand Post 106.58 ÎĽs 1.191 ÎĽs 1.801 ÎĽs 1.5000 0.7500 0.1250 7.42 KB
Dapper QueryFirstOrDefault<dynamic> dynamic 119.52 ÎĽs 1.320 ÎĽs 2.219 ÎĽs 3.6250 - - 11.39 KB
Dapper 'Query<dynamic> (buffered)' dynamic 119.93 ÎĽs 1.943 ÎĽs 2.937 ÎĽs 2.3750 1.0000 0.2500 11.73 KB
Massive 'Query (dynamic)' dynamic 120.31 ÎĽs 1.340 ÎĽs 2.252 ÎĽs 2.2500 1.0000 0.1250 12.07 KB
Dapper QueryFirstOrDefault<T> Post 121.57 ÎĽs 1.564 ÎĽs 2.364 ÎĽs 1.7500 0.7500 - 11.35 KB
Dapper 'Query<T> (buffered)' Post 121.67 ÎĽs 2.913 ÎĽs 4.403 ÎĽs 1.8750 0.8750 - 11.65 KB
PetaPoco 'Fetch<T> (Fast)' Post 124.91 ÎĽs 4.015 ÎĽs 6.747 ÎĽs 2.0000 1.0000 - 11.5 KB
Mighty Query<T> Post 125.23 ÎĽs 2.932 ÎĽs 4.433 ÎĽs 2.2500 1.0000 - 12.6 KB
LINQ to DB Query<T> Post 125.76 ÎĽs 2.038 ÎĽs 3.081 ÎĽs 2.2500 0.7500 0.2500 10.62 KB
PetaPoco Fetch<T> Post 127.48 ÎĽs 4.283 ÎĽs 6.475 ÎĽs 2.0000 1.0000 - 12.18 KB
LINQ to DB 'First (Compiled)' Post 128.89 ÎĽs 2.627 ÎĽs 3.971 ÎĽs 2.5000 0.7500 - 10.92 KB
Mighty Query<dynamic> dynamic 129.20 ÎĽs 2.577 ÎĽs 3.896 ÎĽs 2.0000 1.0000 - 12.43 KB
Mighty SingleFromQuery<T> Post 129.41 ÎĽs 2.094 ÎĽs 3.166 ÎĽs 2.2500 1.0000 - 12.6 KB
Mighty SingleFromQuery<dynamic> dynamic 130.59 ÎĽs 2.432 ÎĽs 3.677 ÎĽs 2.0000 1.0000 - 12.43 KB
Dapper 'Contrib Get<T>' Post 134.74 ÎĽs 1.816 ÎĽs 2.746 ÎĽs 2.5000 1.0000 0.2500 12.29 KB
ServiceStack SingleById<T> Post 135.01 ÎĽs 1.213 ÎĽs 2.320 ÎĽs 3.0000 1.0000 0.2500 15.27 KB
LINQ to DB First Post 151.87 ÎĽs 3.826 ÎĽs 5.784 ÎĽs 3.0000 1.0000 0.2500 13.97 KB
EF 6 SqlQuery Post 171.00 ÎĽs 1.460 ÎĽs 2.791 ÎĽs 3.7500 1.0000 - 23.67 KB
DevExpress.XPO GetObjectByKey<T> Post 172.36 ÎĽs 3.758 ÎĽs 5.681 ÎĽs 5.5000 1.2500 - 29.06 KB
Dapper 'Query<T> (unbuffered)' Post 174.40 ÎĽs 3.296 ÎĽs 4.983 ÎĽs 2.0000 1.0000 - 11.77 KB
Dapper 'Query<dynamic> (unbuffered)' dynamic 174.45 ÎĽs 1.988 ÎĽs 3.340 ÎĽs 2.0000 1.0000 - 11.81 KB
DevExpress.XPO FindObject<T> Post 181.76 ÎĽs 5.554 ÎĽs 9.333 ÎĽs 8.0000 - - 27.15 KB
DevExpress.XPO Query<T> Post 189.81 ÎĽs 4.187 ÎĽs 8.004 ÎĽs 10.0000 - - 31.61 KB
EF Core 'First (Compiled)' Post 199.72 ÎĽs 3.983 ÎĽs 7.616 ÎĽs 4.5000 - - 13.8 KB
NHibernate Get<T> Post 248.71 ÎĽs 6.604 ÎĽs 11.098 ÎĽs 5.0000 1.0000 - 29.79 KB
EF Core First Post 253.20 ÎĽs 3.033 ÎĽs 5.097 ÎĽs 5.5000 - - 17.7 KB
NHibernate HQL Post 258.70 ÎĽs 11.716 ÎĽs 17.712 ÎĽs 5.0000 1.0000 - 32.1 KB
EF Core SqlQuery Post 268.89 ÎĽs 19.349 ÎĽs 32.516 ÎĽs 6.0000 - - 18.5 KB
EF 6 First Post 278.46 ÎĽs 12.094 ÎĽs 18.284 ÎĽs 13.5000 - - 44.18 KB
EF Core 'First (No Tracking)' Post 280.88 ÎĽs 8.192 ÎĽs 13.765 ÎĽs 3.0000 0.5000 - 19.38 KB
NHibernate Criteria Post 304.90 ÎĽs 2.232 ÎĽs 4.267 ÎĽs 11.0000 1.0000 - 60.29 KB
EF 6 'First (No Tracking)' Post 316.55 ÎĽs 7.667 ÎĽs 11.592 ÎĽs 8.5000 1.0000 - 50.95 KB
NHibernate SQL Post 335.41 ÎĽs 3.111 ÎĽs 4.703 ÎĽs 19.0000 1.0000 - 78.86 KB
NHibernate LINQ Post 807.79 ÎĽs 27.207 ÎĽs 45.719 ÎĽs 8.0000 2.0000 - 53.65 KB

Feel free to submit patches that include other ORMs - when running benchmarks, be sure to compile in Release and not attach a debugger (Ctrl+F5).

Alternatively, you might prefer Frans Bouma's RawDataAccessBencher test suite or OrmBenchmark.

Parameterized queries

Parameters are passed in as anonymous classes. This allow you to name your parameters easily and gives you the ability to simply cut-and-paste SQL snippets and run them in your db platform's Query analyzer.

new {A = 1, B = "b"} // A will be mapped to the param @A, B to the param @B

List Support

Dapper allows you to pass in IEnumerable<int> and will automatically parameterize your query.

For example:

connection.Query<int>("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = new int[] { 1, 2, 3 } });

Will be translated to:

select * from (select 1 as Id union all select 2 union all select 3) as X where Id in (@Ids1, @Ids2, @Ids3)" // @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3

Literal replacements

Dapper supports literal replacements for bool and numeric types.

connection.Query("select * from User where UserTypeId = {=Admin}", new { UserTypeId.Admin });

The literal replacement is not sent as a parameter; this allows better plans and filtered index usage but should usually be used sparingly and after testing. This feature is particularly useful when the value being injected is actually a fixed value (for example, a fixed "category id", "status code" or "region" that is specific to the query). For live data where you are considering literals, you might also want to consider and test provider-specific query hints like OPTIMIZE FOR UNKNOWN with regular parameters.

Buffered vs Unbuffered readers

Dapper's default behavior is to execute your SQL and buffer the entire reader on return. This is ideal in most cases as it minimizes shared locks in the db and cuts down on db network time.

However when executing huge queries you may need to minimize memory footprint and only load objects as needed. To do so pass, buffered: false into the Query method.

Multi Mapping

Dapper 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.

Example:

Consider 2 classes: Post and User

class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public User Owner { get; set; }
}

class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Now let us say that we want to map a query that joins both the posts and the users table. Until now if we needed to combine the result of 2 queries, we'd need a new object to express it but it makes more sense in this case to put the User object inside the Post object.

This is the use case for multi mapping. You tell dapper that the query returns a Post and a User object and then give it a function describing what you want to do with each of the rows containing both a Post and a User object. In our case, we want to take the user object and put it inside the post object. So we write the function:

(post, user) => { post.Owner = user; return post; }

The 3 type arguments to the Query method specify what objects dapper should use to deserialize the row and what is going to be returned. We're going to interpret both rows as a combination of Post and User and we're returning back a Post object. Hence the type declaration becomes

<Post, User, Post>

Everything put together, looks like this:

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

Assert.Equal("Sams Post1", post.Content);
Assert.Equal(1, post.Id);
Assert.Equal("Sam", post.Owner.Name);
Assert.Equal(99, post.Owner.Id);

Dapper is able to split the returned row by making an assumption that your Id columns are named Id or id. If your primary key is different or you would like to split the row at a point other than Id, use the optional splitOn parameter.

Multiple Results

Dapper allows 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";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
   var returns = multi.Read<Return>().ToList();
   ...
}

Stored Procedures

Dapper fully supports stored procs:

var user = cnn.Query<User>("spGetUser", new {Id = 1},
        commandType: CommandType.StoredProcedure).SingleOrDefault();

If you want something more fancy, you can do:

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");

Ansi Strings and varchar

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:

Query<Thing>("select * from Thing 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.

Type Switching Per Row

Usually you'll want to treat all rows from a given table as the same data type. However, there are some circumstances where it's useful to be able to parse different rows as different data types. This is where IDataReader.GetRowParser comes in handy.

Imagine you have a database table named "Shapes" with the columns: Id, Type, and Data, and you want to parse its rows into Circle, Square, or Triangle objects based on the value of the Type column.

var shapes = new List<IShape>();
using (var reader = connection.ExecuteReader("select * from Shapes"))
{
    // Generate a row parser for each type you expect.
    // The generic type <IShape> is what the parser will return.
    // The argument (typeof(*)) is the concrete type to parse.
    var circleParser = reader.GetRowParser<IShape>(typeof(Circle));
    var squareParser = reader.GetRowParser<IShape>(typeof(Square));
    var triangleParser = reader.GetRowParser<IShape>(typeof(Triangle));

    var typeColumnIndex = reader.GetOrdinal("Type");

    while (reader.Read())
    {
        IShape shape;
        var type = (ShapeType)reader.GetInt32(typeColumnIndex);
        switch (type)
        {
            case ShapeType.Circle:
            	shape = circleParser(reader);
            	break;
            case ShapeType.Square:
            	shape = squareParser(reader);
            	break;
            case ShapeType.Triangle:
            	shape = triangleParser(reader);
            	break;
            default:
            	throw new NotImplementedException();
        }

      	shapes.Add(shape);
    }
}

User Defined Variables in MySQL

In order to use Non-parameter SQL variables with MySql Connector, you have to add the following option to your connection string:

Allow User Variables=True

Make sure you don't provide Dapper with a property to map.

Limitations and caveats

Dapper caches information about every query it runs, this allows it to materialize objects quickly and process parameters quickly. The current implementation caches this information in a ConcurrentDictionary object. Statements that are only used once are routinely flushed from this cache. Still, if you are generating SQL strings on the fly without using parameters it is possible you may hit memory issues.

Dapper's simplicity means that many feature that ORMs ship with are stripped out. It worries about the 95% scenario, and gives you the tools you need most of the time. It doesn't attempt to solve every problem.

Will Dapper work with my DB provider?

Dapper has no DB specific implementation details, it works across all .NET ADO providers including SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL and SQL Server.

Do you have a comprehensive list of examples?

Dapper has a comprehensive test suite in the test project.

Who is using this?

Dapper is in production use at Stack Overflow.

About

Dapper - a simple object mapper for .Net

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages

  • C# 99.9%
  • Other 0.1%