As you might know, I’m running a .NET exception service called OneTrueError. When I moved from a NoSQL db to SQL Azure, I had to be able to work with the database in some way. I’m not using OR/Ms any more. They might significantly reduce the bootstrapping, but in the long run, they always tend to make you struggle as the application grows. To me, ORMs is a bit like ASP.NET WebForms, but for data, i.e., it tries to make something that it isn’t. I therefore wanted something that did not take away the control from me nor hide the underlying layer. I still want to work with my domain entities though.
After evaluating different mappers, I decided to write my own. It’s unobtrusive as it’s built as extensions to ADO.NET, instead of hiding ADO.NET. You can easily alternate between ADO.NET and my mapper. A single technology doesn’t work for all cases. You also have full control over the table/class mappings. The mappings are not hidden and you can easily customize them, for instance if the column and property types do not match. There is no support for LINQ statements or any other fancy way of creating queries. You will have to write those using SQL.
Do remember that it’s a mapper and not an ORM. It doesn’t try to solve all problems in the data layer. It works excellent for many cases, but not all. Use plain ADO.NET when justified, i.e., don’t be afraid to mix technologies in your data layer.
How does it work then? First of all, you should read my ADO.NET article since this layer is an extension to that.
Select Statements
Let’s see how we can fetch data with Griffin.Framework.
In the ADO.NET article, you had code like this (using only ADO.NET):
public class MyRepository
{
IDbConnection _connection;
public MyRepository(IDbConnection connection)
{
if (connection == null) throw new ArgumentNullException();
_connection = connection;
}
public IEnumerable<User> FindUsers()
{
using (var command = _connection.CreateCommand())
{
command.CommandText = @"SELECT * FROM Users _
WHERE CompanyId = @companyId AND FirstName LIKE @firstName";
command.AddParameter("companyId", LoggedInUser.companyId);
command.AddParameter("firstName", firstName + "%");
using (var reader = command.ExecuteReader())
{
List<User> users = new List<User>();
while (reader.Read())
{
var user = new User();
Map(reader, user);
users.Add(user);
}
return users;
}
}
}
public void Map(IDataRecord record, User user)
{
user.FirstName = (string)record["FirstName"];
user.Age = (int)record["Age"];
}
}
With Griffin.Framework, you reduce the code to this:
public class MyRepository
{
IDbConnection _connection;
public MyRepository(IDbConnection connection)
{
if (connection == null) throw new ArgumentNullException();
_connection = connection;
}
public IEnumerable<User> FindUsers()
{
using (var command = _connection.CreateCommand())
{
command.CommandText = @"SELECT * FROM Users _
WHERE CompanyId = @companyId AND FirstName LIKE @firstName";
command.AddParameter("companyId", LoggedInUser.companyId);
command.AddParameter("firstName", firstName + "%");
return command.ToList<User>();
}
}
}
See? You still have to write the queries by yourself. For simple queries (only using “AND” and equal sign), we can reduce it even further.
public class MyRepository
{
IDbConnection _connection;
public MyRepository(IDbConnection connection)
{
if (connection == null) throw new ArgumentNullException();
_connection = connection;
}
public IEnumerable<User> FindUsers()
{
return _connection.ToList<User>(new { LoggedInUser.CompanyId, FirstName = firstName + "%" });
}
}
The anonymous object will automatically be converted into the exact same WHERE clause.
First
First will thrown an exception if an entity is not found. In applications where IDs always are provided through code, this is the number one way to fetch items, since you probably have an error somewhere if the entity is not found in that case.
Here is a small example:
public class MyRepository
{
IDbConnection _connection;
public MyRepository(IDbConnection connection)
{
if (connection == null) throw new ArgumentNullException();
_connection = connection;
}
public User GetUser(int id)
{
return _connection.First<User>(new { id });
}
}
If the user is not found, you’ll get an exception which includes the SQL query and all arguments:
Failed to find entity of type ‘Griffin.Data.IntegrationTests.Sqlite.Entites.User’.
Command: SELECT * FROM Users WHERE Id = @Id
Parameters: Id=7f6d4ef8e7044dbc884f961f3d57cac2
FirstOrDefault
Works just as First, but doesn’t thrown an exception if the entity is not found.
public class MyRepository
{
IDbConnection _connection;
public MyRepository(IDbConnection connection)
{
if (connection == null) throw new ArgumentNullException();
_connection = connection;
}
public User GetUser(int id)
{
return _connection.FirstOrDefault<User>(new { id });
}
}
ToList
ToList will populate a list in memory and return it to you.
public IEnumerable<User> FindUsers()
{
using (var command = _connection.CreateCommand())
{
command.CommandText = @"SELECT * FROM Users _
WHERE CompanyId = @companyId AND FirstName LIKE @firstName";
command.AddParameter("companyId", LoggedInUser.companyId);
command.AddParameter("firstName", firstName + "%");
return command.ToList<User>();
}
}
ToEnumerable
Sometimes, you would want to work with larger data amounts, so building a list in memory would be very inefficient. ToEnumerable() uses lazy loading and will not map rows unless they are requested. Hence doing something like:
public IEnumerable<User> FindUsers()
{
using (var command = _connection.CreateCommand())
{
command.CommandText = @"SELECT * FROM Users _
WHERE CompanyId = @companyId AND FirstName LIKE @firstName";
command.AddParameter("companyId", LoggedInUser.companyId);
command.AddParameter("firstName", firstName + "%");
return command.ToEnumerable<User>().Skip(1000).Take(10).ToList();
}
}
...would skip through the first 1000 rows without mapping them and then just map the next 10 rows.
Create/Update/Delete
With plain ADO.NET, you have to write all CRUD statements by yourself, they would look something like:
public class UserRepository : Repository<User>
{
public UserRepository(AdoNetContext context) : base(context)
{
}
public void Create(User user)
{
using (var command = _connection.CreateCommand())
{
command.CommandText = @"INSERT INTO Users (CompanyId, FirstName) _
VALUES(@companyId, @firstName)";
command.AddParameter("companyId", user.CompanyId);
command.AddParameter("firstName", user.FirstName);
command.ExecuteNonQuery();
}
}
public void Update(User user)
{
using (var command = _connection.CreateCommand())
{
command.CommandText = @"UPDATE Users SET CompanyId = @companyId WHERE Id = @userId";
command.AddParameter("companyId", user.CompanyId);
command.AddParameter("userId", user.Id);
command.ExecuteNonQuery();
}
}
public void Delete(int id)
{
using (var command = _connection.CreateCommand())
{
command.CommandText = @"DELETE FROM Users WHERE Id = @userId";
command.AddParameter("userId", id);
command.ExecuteNonQuery();
}
}
}
With Griffin.Framework, they would instead look like this:
public class UserRepository : Repository<User>
{
public UserRepository(AdoNetContext context) : base(context)
{
}
public void Create(User user)
{
_connection.Insert(user);
}
public void Update(User user)
{
_connection.Update(user);
}
public void Delete(int id)
{
_connection.Delete<User>(new { Id = id });
}
}
That lowers the time compared to plain ADO.NET. And again, sometimes you need to do custom queries. As my library is just an extension to ADO.NET, you can mix them depending on the use case.
Mappings
To make this work, we have to have mappings somewhere. For this mapping layer, they are mandatory and are represented by classes. By default, the mapping classes are automatically picked up by the library using reflection. You can however customize how the mappings are loaded by specifying a factory using EntityMappingProvider.Provider = new YourCustomProvider().
If your table looks exactly like your class, you can just create an empty class:
public class UserMapper : EntityMapper<User>
{
public UserMapper : base("Users")
{
}
}
The constructor specifies which table to use.
Column Name / Property Name Mismatch
However, sometimes the column names do not match the property names. In this case, we’ll have to configure the mappings a bit more.
public class UserMapper : EntityMapper<User>
{
public UserMapper : base("Users")
{
}
public override void Configure(IDictionary<string, IPropertyMapping> mappings)
{
base.Configure(mappings);
mappings["Id"].ColumnName = "user_id";
}
}
Column Type / Property Type Mismatch
If the database does not support the same types as .NET, we’ll have to convert the value. Those conversions need to be configured using adapters.
public class UserMapper : EntityMapper<User>
{
public UserMapper : base("Users")
{
}
public override void Configure(IDictionary<string, IPropertyMapping> mappings)
{
base.Configure(mappings);
mappings["Age"].ColumnToPropertyAdapter = _
columnValue => DateTime.Today.Subtract((DateTime)columnValue);
}
}
You can also create two way conversions:
public class UserMapper : EntityMapper<User>
{
public UserMapper : base("Users")
{
}
public override void Configure(IDictionary<string, IPropertyMapping> mappings)
{
base.Configure(mappings);
mappings["Id"].ColumnToPropertyAdapter = x => Guid.Parse((string) x);
mappings["Id"].PropertyToColumnAdapter = x => ((Guid) x).ToString("N");
}
}
Value Types / Child Aggregates
In some cases, it doesn’t make sense to create tables for child aggregates as they are never going to be accessed directly. Instead, you just want to store them as part of the root aggregate (as a column value). With the mappers, you can do that easily by using the adapters.
Let’s say that you have the following classes:
public class User
{
public int Id { get; private set; }
public IEnumerable<Address> Addresses { get; private set; }
}
public class Address
{
public string Street { get; private set; }
public string ZipCode { get; private set; }
public string City { get; private set; }
public string State { get; private set; }
}
Instead of creating a table for all addresses, simply add a new text column called “Addresses” and do the following (using JSON.NET):
public class UserMapper : EntityMapper<User>
{
public UserMapper : base("Users")
{
}
public override void Configure(IDictionary<string, IPropertyMapping> mappings)
{
base.Configure(mappings);
mappings["Addresses"].ColumnToPropertyAdapter = _
x => JsonConvert.DeserializeObject<IEnumerable<Address>>((string)x);
mappings["Addresses"].PropertyToColumnAdapter = x => JsonConvert.SerializeObject(x);
}
}
The great thing with that is that you never have to track if any of the child aggregates have been added/changed/removed.
Field vs Property
Private setters or getters are no problem, but sometimes that isn’t enough. You might want to use a field instead, typically if you expose IEnumerable but use a List internally in your class.
We do support that out of the box. Just make sure that the field is named as the property, but with underscore and camel hump style.
public class User
{
private List<Address> _addresses;
public int Id { get; private set; }
public IEnumerable<Address> Addresses { get { return _addresses; } }
}
Transactions
For transactions, you can of course use IDbTransaction implementations like SqlTransaction, etc. But as the transaction is typically handled by a layer on top of the data layer that leaks data layer specific implementation details to the above layer. We’ve instead added a new interface called IUnitOfWork and an UnitOfWorkFactory class.
That means that your business layer would have code like:
using (var uow = UnitOfWorkFactory.Create())
{
var repos = new UserRepository(uow);
var user = repos.Get(userId);
user.LockUser();
repos.Update(user);
uow.SaveChanges();
}
To make that work, you have to configure the UnitOfWorkFactory class by doing something like:
public IUnitOfWork Create()
{
var conString = ConfigurationManager.ConnectionStrings("MyDb").ConnectionString;
var con = new SqlConnection(conString);
con.Open();
return new AdoNetUnitOfWork(con, true);
}
UnitOfWorkFactory.SetFactoryMethod(() => Create());
The unit of work also has an extension method to perform db operations in the transaction:
var user = _unitOfWork.First({ Id = "10" });
_unitOfWork.Create(user);
_unitOfWork.Update(user);
_unitOfWork.Delete(new { FirstName = "Jonas", LastName = "Gauffin"});
To create a plain IDbCommand, you can do like this:
public void DeleteYoung()
{
using (var cmd = _unitOfWork.CreateCommand())
{
cmd.CommandText = "DELETE FROM Users WHERE Age < 11";
cmd.ExecuteNonQuery();
}
}
Asynchronous
The mapper fully supports asynchronous operations. The entire API is available using TPL...
public async Task<User> GetAsync(int userId)
{
return await _connection.FirstAsync<User>({ UserId = userId });
}
...or using commands:
public async Task<User[]> FindAllAsync(int minAge, int maxAge)
{
using (var cmd = _unitOfWork.CreateCommand())
{
cmd.CommandText = "SELECT * FROM User WHERE Age >= @min AND Age <= @max";
cmd.AddParameter("min", minAge);
cmd.AddParameter("max", maxAge);
return await cmd.ToListAsync<User>();
}
}
Exceptions
I’ve put a lot of effort in the exception messages to aid you when something fails. You will for instance always get information about which entity we couldn’t find:
Failed to find entity of type ‘Griffin.Data.IntegrationTests.Sqlite.Entites.User’.
Command: SELECT * FROM Users WHERE Id = @Id
Parameters: Id=7f6d4ef8e7044dbc884f961f3d57cac2
Or if a mapping is incorrect:
Griffin.Data.IntegrationTests.Sqlite.Entites.User: Failed to cast ‘Id’ from ‘System.Int32′.
A quick examination of the mapping for that entity would reveal that ‘Guid’ was expected.
In my opinion, exception messages is the best way of making it easy to solve errors. That in combination with the complexity is the number one reason why most OR/Ms are so hard to work with.
Summary
This mapper is the first part of Griffin.Framework. I’m in progress of merging my most popular frameworks into one library. It makes it easier to support and build more advanced features that require multiple libraries. The next part being merged is Griffin.Networking (a more stable and performant rewrite).
I’ll add complete examples to github when I have a chance.
The library is available in nuget: install-package griffin.framework
