Consider using simple models, instead

I want to share with you the pattern which I use very often to build classes in my web applications. It is different and relatively more straightforward in comparison to most enterprise patterns. I don't claim it's suitable for anyone but me -- I write web applications. It works exceptionally well for the cases I encounter, unlike the repository pattern, which is excellent if you have vast amounts of business logic, or command/query separation, which is very suitable for desktop-like experiences.

There are two guiding principles beyond it, which I find applicable generally: simplicity, or the art of maximizing the work not done; cohesion, keeping related code together to decrease the cognitive cost of coding.

First of all, remember that I build web applications. For me, a backend server either serves APIs or web pages and in most cases, the main business logic is a straight SQL query or a cache lookup.

I usually adopt an MVC pattern with ViewModels -- not the MVVM two-way binding monstrosity, ViewModels only serve as transport to get the data to the views. This is the main control flow for a typical request.

For most pages, business logic falls into two categories, either validation or simple database operations. The former is best implemented through a filter, middleware, or other aspect-oriented language feature. This makes it testable. The latter runs on the database and can only be tested through an integration test.

As a side note, you might think that an ORM makes it possible to test these operations locally, and you would be wrong, I'm sorry to report. You would be validating the ORM and not the actual query that -- generated or not -- is what runs. In other words, you would be exercising a mock and no business logic. The sad fact is that databases (or caches) run a large part of the actual business logic for most web pages!

In other words, what you actually want to test is, in large part, locked away in a SQL query. This is why the repository pattern, or similar ways of decoupling "business logic" from "database calls" is not very useful in simple cases: the database calls are the business logic.

In these simple -- yet very pervasive -- cases, I use the following pattern. Start with a simple DTO:

public class Widget
{
    public int? Id { get; private set; }
    public string Name { get; set; }
    public string PartNumber { get; set; }
    // ... other properties here
}

I kept the class mutable. This is because I plan to make this record updateable, and frankly, immutability gets in the way of keeping that as simple as possible. Only Id is kept immutable for reasons we'll see below.

Let's implement a simple CRUD, starting with getting a list of widgets.

public class Widget
{
    // ... properties here
    public static List<Widget> All()
    {
        using (var conn = Common.GetConn())
        {
            return conn.Query<Widget>("Select * From Widgets");
        }
    }
}

As you can see, it's a straight Dapper query in a static method, right next to the properties that Dapper maps to. This is quite arguably the simplest possible way to get this data out of the database and into a class list. As such, it's my go-to coding solution, until an actual real, non-theoretical necessity emerges to do differently.

Single reads are implemented similarly.

public class Widget
{
    // ... properties and other methods here
    public static Widget GetById(int id)
    {
        using (var conn = Common.GetConn())
        {
            return conn.QuerySingleOrDefault<Widget>("Select * From Widgets Where Id = @Id", id);
        }
    }
}

What about commands, i.e., Creates, Updates, and Deletes? Deletes are also static and take an id parameter -- generally, no one wants to pull a whole object out of the database just to delete it and if you do happen to have an instance already handy, passing the id is easy enough.

Creates and updates I usually merge into a single Save method. If a class has the Id property set, then we assume an update; if it is unset, then it is a new object to be inserted. Of course, the developer end-user cannot set the Id because of its private accessor, but Dapper can. Only classes that come from the actual database have an Id that will be used in the update.

This needs not to be a static method (although a static variant is always possible).

public class Widget
{
    // ... properties and other methods here
    public void Save()
    {
        var sql = "";
        if (Id.HasValue())
            sql = "Update Widgets Set Name = @Name, PartNumber = @PartNumber /* other sets here */ Where Id = @Id; Select @Id";
        else
            sql = "Insert Into Widgets (Name, PartNumber /* etc */) Values (@Name, @PartNumber /* etc */); Select ScopeIdentity()";

        using (var conn = Common.GetConn())
        {
            this.Id = conn.QuerySingle<int>(sql, this);
        }
    }
}

This is the pattern. Further commands are likely to be implemented as property manipulation, followed by a Save call. Other queries depend on the size of the table in question, and they can be either custom SQL queries or perhaps LinqToObjects if the results of All() are cached in memory.

In conclusion, I've shown a simple way to implement Models as simple, self-contained entities that are perfectly adequate in large part of the use cases of web applications. I do not claim this way is the "one true way," but it works for me, and as any abstraction, please do not consider it a panacea.

Hopefully, whether you agree or less with me, you will spend some time reflecting on whether you really need that fat ORM-based, dependency-injected, data-mapped solution next time you write a simple model class.