To kick off our repository we start with a base repository interface.
All domain level repository interfaces will implement this interface as
it will offer the convention we seek in making all repositories
uniform:
Here is how the solution structure looks in Visual Studio. The IRepository is situated in the .Infrastructure project, since it is generic but the ICompetitionRepository can be found in the .Model project since it's domain specific:
Next we move to the .Dapper project and add a concrete implementation of the IRepository interface. In our requirements for the repository we desired convention. All concrete implementations of the domain specific interfaces such as ICompetitionRepository will inherit from this base repository class. This provides the convention that all domain entities that implement IAggregateRoot have CRUD operations and the ID property will always be a GUID.
The CompetitionRepository implementation will look as below.
Assuming that a domain entities properties are primitive types and are
named the same as the corresponding database table our code will "just
work" and the CRUD operations will be implemented by way of convention:
Note how all the CRUD methods are virtual, so in the event we want to take control of a method we can simply override the method.
Some additional methods have been added to provide additional automation/convention of repetitive tasks. The additional features are:
The three add-ons we discussed all point to the static methods that begin with DynamicQuery.[SomeMethod]. The static methods available in this class are:
I wrote these utilities to accompany this blog series as a learning exercise so they are not production ready or performance tested (yet!), but with that said you can find the source code here.
The slight difference when inserting or updating this entity to the database is the property type named Password which has a data type of EncryptedString.
This type is a custom value object in our domain model that provides
string encryption. Since this is not a primitive type we need some way
of mapping the correct property value to the database table column. In
the database we have a Users table with three columns:
Following up our UserRepository example we need to set the value of the EncryptedString property type as follows:
Whenever we want to take control we can use the full range of methods from the Dapper documentation
to meet our requirements. If we need to add any very custom code that
goes beyond what Dapper offers we can just code this manually if we
choose to.
For more detail on how dynamic works there is a great answer to a question at Stackoverflow.com.
I would contest that this is little different from manually mapping
properties from a data reader object such as the following line of code:
In either example if I make a spelling mistake I will get a runtime error regardless!
This is an oversimplification of how NoSQL instances work but it
helps in sounding out how the repository interface works when dealing
with a key-value pair. I run the repository unit test I wrote for the Dapper implementation of the CompetitionRepository to verify this proof of concept.
The DynamicQuery code is also on Github.
The base Repository for mapper can also be found here on Github.
1
2
3
4
5
6
7
8
9
| public interface IRepository<T> where T : EntityBase, IAggregateRoot { void Add(T item); void Remove(T item); void Update(T item); T FindByID(Guid id); IEnumerable<T> Find(Expression<Func<T, bool >> predicate); IEnumerable<T> FindAll(); } |
Next we move to the .Dapper project and add a concrete implementation of the IRepository interface. In our requirements for the repository we desired convention. All concrete implementations of the domain specific interfaces such as ICompetitionRepository will inherit from this base repository class. This provides the convention that all domain entities that implement IAggregateRoot have CRUD operations and the ID property will always be a GUID.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
| public abstract class Repository<T> : IRepository<T> where T : EntityBase, IAggregateRoot { private readonly string _tableName; internal IDbConnection Connection { get { return new SqlConnection(ConfigurationManager.ConnectionStrings[ "SmsQuizConnection" ].ConnectionString); } } public Repository( string tableName) { _tableName = tableName; } internal virtual dynamic Mapping(T item) { return item; } public virtual void Add(T item) { using (IDbConnection cn = Connection) { var parameters = ( object )Mapping(item); cn.Open(); item.ID = cn.Insert<Guid>(_tableName, parameters); } } public virtual void Update(T item) { using (IDbConnection cn = Connection) { var parameters = ( object )Mapping(item); cn.Open(); cn.Update(_tableName, parameters); } } public virtual void Remove(T item) { using (IDbConnection cn = Connection) { cn.Open(); cn.Execute( "DELETE FROM " + _tableName + " WHERE ID=@ID" , new { ID = item.ID }); } } public virtual T FindByID(Guid id) { T item = default (T); using (IDbConnection cn = Connection) { cn.Open(); item = cn.Query<T>( "SELECT * FROM " + _tableName + " WHERE ID=@ID" , new { ID = id }).SingleOrDefault(); } return item; } public virtual IEnumerable<T> Find(Expression<Func<T, bool >> predicate) { IEnumerable<T> items = null ; // extract the dynamic sql query and parameters from predicate QueryResult result = DynamicQuery.GetDynamicQuery(_tableName, predicate); using (IDbConnection cn = Connection) { cn.Open(); items = cn.Query<T>(result.Sql, ( object )result.Param); } return items; } public virtual IEnumerable<T> FindAll() { IEnumerable<T> items = null ; using (IDbConnection cn = Connection) { cn.Open(); items = cn.Query<T>( "SELECT * FROM " + _tableName); } return items; } } |
1
2
3
4
| public sealed class Competitionepository : Repository<Competition>, ICompetitionRepository { public CompetitionRepository() : base ( "Competitions" ) { } } |
Some additional methods have been added to provide additional automation/convention of repetitive tasks. The additional features are:
- An extension method for Dapper that dynamically generates the INSERT SQL query.
- An extension method for Dapper that dynamically generates the UPDATE SQL query.
- A utility method that will dynamically generate simple and ONLY simple lambda expressions into SQL.
1
2
3
4
5
6
7
8
9
10
11
12
13
| public static class DapperExtensions { public static T Insert<T>( this IDbConnection cnn, string tableName, dynamic param) { IEnumerable<T> result = SqlMapper.Query<T>(cnn, DynamicQuery.GetInsertQuery(tableName, param), param); return result.First(); } public static void Update( this IDbConnection cnn, string tableName, dynamic param) { SqlMapper.Execute(cnn, DynamicQuery.GetUpdateQuery(tableName, param), param); } } |
- DynamicQuery.GetInsertQuery();
- DynamicQuery.GetUpdateQuery();
- DynamicQuery.GetDynamicQuery();
I wrote these utilities to accompany this blog series as a learning exercise so they are not production ready or performance tested (yet!), but with that said you can find the source code here.
Why not just use an ORM?
Most of the functionality described so far you also get when using an ORM. So why go to all this trouble to get the same thing? We really start to see the benefit of this implementation when things become a little more complex.Mapping Properties when Saving
For example, the User domain entity looks as follows:
1
2
3
4
5
| public sealed class User : EntityBase, IAggregateRoot { public string Username { get ; set ; } public EncryptedString Password { get ; set ; } } |
- ID
- Username
- Password
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| public sealed class UserRepository : Repository<User>, IUserRepository { public UserRepository() : base ( "Users" ) { } internal override dynamic Mapping(User item) { return new { ID = item.ID, Username = item.Username, Password = item.Password.EncryptedValue }; } } |
Mapping Properties when Retrieving Data
As we've discussed, if our entity matches the database table structure like-for-like then everything just works. But what happens when this is not the case? What if a single domain entity is made up of data across three database tables?Following up our UserRepository example we need to set the value of the EncryptedString property type as follows:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| public override User FindByID(Guid id) { User item = null ; using (IDbConnection cn = Connection) { cn.Open(); var result = cn.Query( "SELECT * FROM Users WHERE ID=@ID" , new { ID = id }).SingleOrDefault(); if (result != null ) { item = new User(); item.ID = result.ID; item.Username = result.Username; /* The custom mapping */ item.Password = new EncryptedString(result.Password); } } return item; } |
The key point is that we never have to fight a
framework when we need explicit control. We use convention to make the
simple cases "just work" so we can focus on getting the more complex
stuff right.
Isn't it bad to use Dynamic?
The use of the dynamic type in C# will often polarise developers into love or hate categories. There are arguments relating to performance and that the use of dynamic is more error prone since possible runtime errors will not be picked up by the compiler at compile time in the same way static types are.Performance
The performance benchmarks from the Dapper documentation indicate that using dynamic in the data access code doesn't result in a performance hit.For more detail on how dynamic works there is a great answer to a question at Stackoverflow.com.
Lack of static typing
In our examples we used dynamic to map properties such as:
1
2
3
4
5
6
| return new { ID = item.ID, Username = item.Username, Password = item.Password.EncryptedValue }; |
1
| item.Username = reader[ "Username" ].ToString(); |
Future move to NoSql?
In the requirements there was a possibility moving the database to a cloud computing/NoSQL platform. In order to future proof the repository design I wrote a simple NoSql flavour version of the base repository just to double check my interface design makes sense before it becomes too late to change. Here is how the code looks:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| public abstract class Repository<T> : IRepository<T> where T : EntityBase, IAggregateRoot { private Dictionary<Guid, T> _db = new Dictionary<Guid,T>(); public void Add(T item) { item.ID = Guid.NewGuid(); _db.Add(item.ID, item); } public void Remove(T item) { _db.Remove(item.ID); } public void Update(T item) { _db[item.ID] = item; } public T FindByID(System.Guid id) { if (_db.ContainsKey(id)) return _db[id]; return default (T); } public IEnumerable<T> Find(Expression<Func<T, bool >> predicate) { return _db.Values.Where(predicate.Compile()).AsQueryable(); } public IEnumerable<T> FindAll() { return _db.Values.AsQueryable(); } } |
Get the Source Code
You can see the full project code on Github.The DynamicQuery code is also on Github.
The base Repository for mapper can also be found here on Github.
0 comments:
Post a Comment