Alright, this post is somewhat of a rant of the title is a bit misleading, in this circumstance, my boss, can mean project lead, client requirement etc.
Also, as a colleague just pointed out, this is not a rant about micro ORMs like Dapper, but only regarding the big ones like nhibernate or entityframework.
My colleagues will be able to testify to this, I whine a lot about ORMs and I thought it’d might be a good idea to get these thoughts out in the open.
First off, a couple of good things, well at least one good thing. After the initial setup, working with an ORM is extremely efficient (from a developer point of view), the setup can be a bit daunting and the first issue I have is that some frameworks tend to play by convention over configuration rules, I am strongly against this. Conventions can be good, if they are widely understood, accepted and followed, however what basically happens whenever conventions kick in, is magic. As a developer, I dislike magic, we’ve all heard and seen magic numbers throughout code and how terrible that is. Even if by chance no one ends up ruining something, it’s just harder to see and understand what is going on.
if(statusId == 2) ...
What does that do? No one knows, a comment might be present but shouldn’t be necessary to understand code, for business logic, sure, but not for the code itself. What does the number 2 mean?
Okay, that was getting a bit off topic. But the solution to this is quite easy, throw that number into a constant or an enum (if there is more than one value).
But not everything is as easy to solve or understand as that, especially when it’s a framework doing this.
At work we’re using nhibernate for the project I’m on and how it works is we make a mapping between a C# class and a table in the database, this is done using an XML file, the first (and worst) problem with an xml file is, it’s harder to check for usages and refactor, particular renaming of properties. This has of course been solved and in enter fluent nhibernate, which allows us to write our mappings in C#, strongly typed etc. This leads to many a line like this.
Map(x => x.ProductId, "ProductId");
Map(x => x.ProductName, "ProductName");
Can’t there be a less verbose way of doing this? Certainly, we can setup a convention which essentially does that work for us, behind our back. This works due to, well conventions, so as long as our property name and column name matches, we’re good to go. In general this is not a problem and just saves the developer a lot of time writing boring boilerplate stuff, but when shit hits the fan (or new developers join the project), we’re in for quite a ride.
My biggest concern is when writing queries to get data out of the database, it seems that we (developers) are lazy as fuck and fear doing a bit of work, especially in something is new and crazy as SQL. It must be so much better to use a third party framework that can and will change syntax and behavior “every” version. Most people working with software, or have studied it, know SQL, at least enough to write a simple SELECT … FROM … JOIN.
Let’s see an example of a typical boring Customer, Order, OrderLine system, the system is up and running and everything is well, but we need to add another simple view, only showing a list of every product a given customer has ever ordered.
Given this signature let’s try a very simple (bad) nhibernate implementation.
public List<ProductDto> GetProducts(int customerId)
{
var results = new List<ProductDto>();
var ordersFromCustomer = Session.Get<Customer>(customerId).AllOrders;
foreach(var order in ordersFromCustomer)
{
foreach(var orderLine in order.OrderLines)
{
var productId = orderLine.Product.ProductId;
if(!results.Any(x => x.Id == productId))
results.Add(new ProductDto(){ Id = productId, Name=orderLine.Product.ProductName });
}
}
return results;
}
This looks innocent enough, right? But depending on how the mappings are written, as a customer makes more and more orders and we add more properties we could potentially load everything from the database, and in the process also spend a lot of time hydrating the properties on our objects.
No matter how bad that performs, it works and if it’s possible to change things within a couple of days if the performance is too bad, sure, go ahead and throw it in production. That didn’t take more than a couple of minutes to write and that was not in an IDE.
Alright, we’ve discovered, that this doesn’t perform as well as we’d hoped, but it has run for a short while until we’ve gotten more data, so now we need to optimize it, we can start looking at cache, lazy loading etc, but that could potentially bring other issues to the table, it’s better to only optimize this one method.
I see a couple of ways to do this. First we can simply rewrite it using nhibernate criterias, projections and all sorts, which basically ends up writing a regular join and only selecting the needed properties. Don’t get me wrong, this works and it works really well, but unless you’re really proficient in the given framework (which may be changed at any time) it will take a while to write. I can’t do it by hand and it will probably take me 2-3 hours to get it to work. This option is also very error prone if the framework or structure changes, but let’s not worry about that, we live in a perfect world 🙂
The next thing we could do is create a SQL view, which does the simple join, something like this.
SELECT DISTINCT p.ProductId, p.ProductName, o.CustomerId FROM OrderLines AS ol
INNER JOIN Orders AS o ON ol.OrderId = o.OrderId
INNER JOIN Products AS p ON ol.ProductId = p.ProductId
Now we have a simple view, create a new entity specific for this scenario and map it to this view, now let’s see how the method would look.
public List<ProductDto> GetProducts(int customerId)
{
var results = new List<ProductDto>();
var productsFromCustomer = Session.QueryOver<UniqueProducts>().Where(x => x.CustomerId == customerId).List();
foreach(var product in productsFromCustomer)
{
results.Add(new ProductDto(){ Id = product.ProductId, Name=product.ProductName });
}
return results;
}
Alright, now it’s a lot simpler and we’ve let our database handle the data, doing the join and only finding distinct products.
If we use nhibernate, this is the approach I would personally take. The last option is to forgo the entire nhibernate layer and manually wiring up a sql connection, sending a command etc, either using a view with an added WHERE o.CustomerId == customerId or a stored procedure.
It will certainly be tricker to do regular debugging if all of the work is in the database, however I am of the persuasion that the database should handle the data queries and let the application take care of the business logic. Most developers know SQL, and I think the above SQL statement would work on most SQL engines, not just Microsoft SQL Server. There is a very small chance that we will change the underlying database for the same data, but a framework like nhibernate might be swapped out for something else, as of writing this the development on nhibernate has stopped and we’ve already had several issues where fluent nhibernate simply does not support our entity structure.
The last point, that I can think of at this very moment is that not everybody has used a specific framework before. Hiring a .NET developer means that they will know how to write code in .NET, C# for example. If they have some form of education they will have worked with SQL. Bringing in too many frameworks can overload anybody if they’re new to them.
Beddets Blog 