Too many layers: My take on Queries and Layers

These days I reviewed a pull request in one of my client’s projects and shared a thought about reading database entities and layering. I believe that project took layering to the extreme. These are my thoughts.

For read-only database-access queries, reduce the number of layers in an application to avoid excessive mapping between layers and unneeded artifacts.

Too many layers, I guess

The pull request I reviewed added a couple of API endpoints to power a report-like screen. These two endpoints only returned data given a combination of parameters. Think of showing all movies released on a date range with 4 or 5 stars. It wasn’t exactly that, but let’s use that example to prove a point.

That project had database entities, domain objects, results wrapping DTOs, and responses. To add a new read-only API endpoint, we would need a request object, query, query handler, and repository.

Inside the repository, we would need to map database entities to domain entities and value objects. Inside the query handler, we would need to return a result object containing a collection of DTOs. Another mapping. Inside the API endpoint, we would need to return a response object. Yet another mapping. I guess you see where I’m going.

This is the call chain of methods I found in that project:

And these are all the files we would need to add a new API endpoint and its dependencies:

|-Api/
|---src/
|-----Movies/
|-------MovieQueryApi.cs
|-------GetMoviesQueryResponse.cs
|-Application/
|---src/
|-----Movies/
|-------GetMoviesQuery.cs
|-------GetMoviesQueryHandler.cs
|-------GetMoviesQueryResult.cs
|-------MovieDto.cs
|-Domain/
|---src/
|-----Movies/
|-------Movie.cs
|-------Director.cs
|-------Genre.cs
|-Infrastructure.Contracts/
|---src/
|-----Movies/
|-------IMovieRepository.cs
|-Infrastructure.SqlServer/
|---src/
|-----Movies/
|-------MovieRepository.cs

Technically, the objects inside the Domain were already there. By the way, we can create that folder structure with dotnet cli.

That’s layering to the extreme. All those artifacts and about three mapping methods between layers are waaay too much to only read unprocessed entities from a database. Arrrggg! Too much complexity. We’re only reading data, not loading domain objects to call methods on them.

I believe simple things should be simple to achieve.

Query Services: A simpler alternative

As an alternative to those artifacts and mappings, I like to follow the idea from the book Hands-on Domain-Driven Design with .NET Core.

For read-only queries, the HODDD book uses two models:

1. Query Models for the request parameters, and
2. Read Models for the request responses.

Then, it calls the underlying storage mechanism directly from the API layer. Well, that’s too much for my own taste. But I like the simplicity of the idea.

I prefer to use Query Services. They are query handlers that live in the Infrastructure or Persistence layer, call the underlying storage mechanism, and return a read model we pass directly to the API layer. This way, we only have two layers and no mappings between them. We declutter our project from those extra artifacts!

I mean something like this,

And something like this,

|-Api/
|---src/
|-----Movies/
|-------MovieQueryApi.cs
|-Application/
|---src/
|-----Movies/
|-------GetMoviesQueryModel.cs
|-------MoviesReadModel.cs
|-Infrastructure.SqlServer/
|---src/
|-----Movies/
|-------GetMoviesQueryService.cs

We put the input and output models in the Application layer since we want the query service in the Infrastructure layer. Although, the HODDD book places the input and output models and data-access code directly in the API layer. Way simpler in any case!

Voilà! That’s my take on read-only queries, layers, and Domain-Driven Design artifacts. I prefer to keep read-only database access simple and use query services to avoid queries, query handlers, repositories, and the mappings between them. What do you think? Do you also find all those layers and artifacts excessive?

If you want to read more content on Domain-Driven Design, check a case of primitive obsession and my takeaways from the book Domain Modeling Made Functional.

Happy coding!

This Is How I'd Start an Ultralearning Project To Become a Software Engineer

Some days ago, I got a message from someone starting his journey to become a Software Engineer. He found my post with the takeaways from the Ultralearning book and asked for feedback.

On the email, my reader explained that he wanted to become a professional Software Engineer with a one-year ultralearning project. Also, he wrote he had a list of resources compiled and already made some progress.

I want to document my reply to help others and preserve my keystrokes.

This is my long reply and how I would start an Ultralearning project:

1. Set milestones

Keep yourself focused and motivated with milestones.

For example, after 2 or 3 months of studying, make sure to complete an introductory CS course or have some features of a coding project ready.

Often we underestimate what we can do in a year or get easily distracted.

2. Choose Math subjects wisely

This might be controversial. But don’t get too focused on learning advanced Math.

Depending on the business domain you’re working on as a Software Engineer, you might not need a lot of Math. Unless you’re working on Computer Graphics, Finance, or Simulations.

I’d stick to courses on Linear Algebra and Math for Computer Science.

3. Use roadmaps as inspiration

Find lists of subjects to learn from roadmaps.

If you search on Google or DuckDuckGo or GitHub “programming roadmap <insert year here>,” you will find good resources. But you don’t need to learn all those subjects at once. Instead, understand how a particular subject or tool fits into the larger picture and when you need it.

Only zoom in when you need a particular tool.

4. Write an end-to-end coding project

Write a coding project that reads data from a webpage, calls a backend, persists data into a relational database, and displays it back.

You will learn a lot from this simple exercise:

• HTML/CSS,
• a UI library,
• HTTP/REST,
• a backend language,
• SQL, and
• a database engine.

Quite a lot!

ou will be surprised by how many “real” applications boil down to read and write data from and to a database.

5. Be consistent

I know this is cliche at this point. But…

Set a regular study time and put it in a calendar. I find the green squares on my GitHub profile inspiring to keep myself in the loop.

6. Learn the tech and tools companies are hiring for

Probably, you will hear or read people arguing to “learn X instead of Y” or “X pays more than Y.”

Instead of looking for the best-paying languages, use a more tactical approach, find what companies around you (or on LinkedIn) are looking for, and learn those subjects.

Or, in any case, it seems there’s always a shortage of COBOL developers. I’ve read they’re well paid.

7. Keep a journal

Keep track of what you learn, the resources you use, and the subjects you find challenging.

You don’t need anything fancy. A simple .txt file works. Sorry, if you were expecting Notion. I’m a plain-text lover.

I found this advice about the journal on the book “Never Stop Learning” by Bradley R. Staats.

Voilà! That’s how I would approach a ultralearning project to become a Software Engineer. My last piece of advice is you don’t need to learn everything at once. In the beginning, learn a handful of tools and learn them well. But don’t be afraid of learning something else. Later you could start expanding your toolbox and finding what you like the most.

I wrote my own roadmap for intermediate C# developers. It points to C# resources, but its overall subject structure works for other languages too. This is not for absolute beginners.

I tried to challenge myself with mini ultralearning projects. I choose to learn enough React and Go in 30 days.

Happy ultralearning!

Five years ago, I wrote my first blog post

Some days ago I found out this Hacker News question about what blogging has done for blog writers. I realized that I published my first blog post five years ago. I’d like to share what blogging has done for me.

In a past post, I shared how I started blogging and the story behind my first post. Long story short: I didn’t want to throw away some hours of Googling.

1. What has my blog done for me?

I wish I could tell that I could live out of my blog. That’s not the case yet. But it had opened doors here and there.

After sharing some of my posts on my LinkedIn profile, I got an invitation to create text-based programming courses on a new teaching platform. I wrote a couple of C# courses there.

Again from LinkedIn, someone from the Marketing team of a software company reached out to me for a content collaboration. I wrote two sponsor posts here on my blog and others on its company blog.

On another occasion, an acquaintance set me up for an interview for a full-time opportunity as a software engineer. I declined it, but that interview ended up being another content collaboration. I helped that company to start a Medium publication.

2. Skills blogging has taught me

Apart from content collaborations, keeping a blog made learn two skills: online writing and SEO.

I haven’t updated my first post. It’s right there to remind me how I started. At the time, I had zero experience writing online. I only threw some words into an empty file and put it online.

I had to learn to use shorter sentences, descriptive subheadings, and clear structure.

I learned to target my posts to a user search query. Also I learned to distinguish between posts I want to rank and posts where I share some thoughts. This is one of them.

I stopped writing about whatever came to my mind to follow a topic over a series of posts regularly.

3. Sources of inspiration

In all these years, I have received inspiration from others in the process.

In 2020, I found the Guest Writer Program from exceptionnotfound.net and accepted the challenge. I wrote three guest posts there. That experience helped me to better structure and format a blog post. Thanks, Matthew, if you ever read this.

The book Show Your Work by Austin Kleon inspired me to keep writing. Not only do the end results matter, the process to get there, too. I learned that from the book.

I follow the mantra: “If something takes you more than 20 minutes to figure out, it should be a post.” I learned that from a YouTube video, I can’t find any more.

4. Some of my favorite and popular posts

In these five years, I’ve written 152 posts, to be precise. Some blog posts came from my frustrations, curiosity, and learning. Often, I like to think of my blog as my own time capsule and a tool to preserve my keystrokes.

These are some of my favorite posts:

• C# Definitive Guide: This is my roadmap for C# intermediate developers.
• Parsinator: A tale of a PDF parser: This is about Parsinator, a small project I wrote in record time to keep one of my previous employers onboarding new clients.
• A quick guide to LINQ with examples: I wrote this one to help a friend. She was preparing for a technical interview. This is an “all you need to know” post. I ended up expanding it into a full series of posts and a text-based course.
• Unit Testing 101: This is one of the guest posts I originally wrote on exceptionnotfound.net. I expanded it to a whole series of posts about unit testing.

These are some of the most popular ones:

• Two free tools to format SQL queries: I got tired of formatting queries manually, so…
• How to compare DateTime without the time part in SQL Server
• Four naming conventions for unit tests
• How to add a caching layer with ASP.NET Core

Voila! That’s my blogging journey over these five years. I hope you stick around for the “Ten years ago, I published my first post” reflection.

If you ask me where my blog will take me, I’d say: “dunno, let’s find out.”

Thanks to all the heroes who contacted me to point out typos or a wrong variable name in my posts.

If I have helped you with my writing, feel free to contact me to say Hi. And, if you want to support my work, check my courses on Educative or leave a tip on one of my ebooks on my Gumroad page.

Happy reading!

Monday Links: NDC Conference

This is another episode where I share the talks from NDC Conference I watched and liked. This time is about JavaScript, History, and Design.

How JavaScript Happened: A Short History of Programming Languages - Mark Rendle

This is a journey from FORTRAN to ALGOL to LISP to JavaScript. It explains why we still use if for conditional, i for loops, and * for multiplication. Spoiler alert: It’s because of FORTRAN.

Apache Kafka in 1 hour for C# Developers - Guilherme Ferreira

Clusters, Topics, Partitions, producers/consumers? This is a good first-time introduction to Kafka. The presenter uses kafkaflow and confluent-kafka-dotnet for the demo application.

Keynote: Why web tech is like this - Steve Sanderson

I found this one on r/programming (before the Reddit blackout) Informative! It feels like time traveling through operating systems and tools to create a Web page.

Pilot Critical Decision Making skills - Clifford Agius

The lesson from this one is to come up with a list of things that could go wrong and prepare and train for that. Follow TDODAR approach: Time, Diagnosis, Options, Decision, Assign, and Review.

Intentional Code - Minimalism in a World of Dogmatic Design

I like the idea that “software really is literature.” Not in the sense of literate programming but in the sense of a narrative to express idea where every line of code matters. I like the example of how a piece of code improves by only removing a few blank lines.

Another idea I liked is: “You don’t want everything to look the same.” We don’t want all applications to use Domain-Driven Design with Event Sourcing and microservices. Often architectural patterns only add to cognitive load and extra complexity.

The presenter suggests: “sitting and looking at it (at a piece of code) and working out how it makes you feel. And then when you feel something, try to understand why it feels that way.”

Voilà! Another Monday Links. What tech conferences do you follow? Do you also follow NDC Conference? What are your favorite presentations? Until next Monday Links.

In the meantime, don’t miss the previous Monday Links on Personal Moats, Unfair Advantage, and Quitting.

Happy coding!

TIL: How to pass a DataTable as a parameter with OrmLite

These days I use OrmLite a lot. Almost every single day. In one of my client’s projects, OrmLite is the defacto ORM. Today I needed to pass a list of identifiers as a DataTable to an OrmLite SqlExpression. I didn’t want to write plain old SQL queries and use the embedded Dapper methods inside OrmLite. This is what I found out after a long debugging session.

To pass a DataTable with a list of identifiers as a parameter to OrmLite methods, create a custom converter for the DataTable type. Then use ConvertToParam() to pass it as a parameter to methods that use raw SQL strings.

As an example, let’s find all movies from a list of director Ids. I know a simple JOIN will get our backs covered here. But bear with me. Let’s imagine this is a more involved query.

1. Create two entities and a table type

These are the Movie and Director classes,

public class Movie
{
    [AutoIncrement]
    public int Id { get; set; }

    [StringLength(256)]
    public string Name { get; set; }

    [Reference]
    // ^^^^^
    public Director Director { get; set; }
}

public class Director
{
    [AutoIncrement]
    public int Id { get; set; }

    [References(typeof(Movie))]
    public int MovieId { get; set; }
    //         ^^^^^
    // OrmLite expects a foreign key back to the Movie table

    [StringLength(256)]
    public string FullName { get; set; }
}

In our database, let’s define the table type for our list of identifiers. Like this,

CREATE TYPE dbo.IntList AS TABLE(Id INT NULL);

2. Pass a DataTable to a SqlExpression

Now, to the actual OrmLite part,

using NUnit.Framework;
using ServiceStack.DataAnnotations;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace PlayingWithOrmLiteAndDataTables;

public class DataTableAsParameterTest
{
    [Test]
    public async Task LookMaItWorks()
    {
        // 1. Register our custom converter
        OrmLiteConfig.DialectProvider = SqlServerDialect.Provider;
        OrmLiteConfig.DialectProvider.RegisterConverter<DataTable>(new SqlServerDataTableParameterConverter());
        //                                                          ^^^^^

        var connectionString = "...Any SQL Server connection string here...";
        var dbFactory = new OrmLiteConnectionFactory(connectionString);
        using var db = dbFactory.Open();

        // 2. Populate some movies
        var titanic = new Movie
        {
            Name = "Titanic",
            Director = new Director
            {
                FullName = "James Cameron"
            }
        };
        await db.SaveAsync(titanic, references: true);

        var privateRyan = new Movie
        {
            Name = "Saving Private Ryan",
            Director = new Director
            {
                FullName = "Steven Spielberg"
            }
        };
        await db.SaveAsync(privateRyan, references: true);

        var pulpFiction = new Movie
        {
            Name = "Pulp Fiction",
            Director = new Director
            {
                FullName = "Quentin Tarantino"
            }
        };
        await db.SaveAsync(pulpFiction, references: true);

        // 3. Populate datable with some Ids
        var movieIds = new DataTable();
        movieIds.Columns.Add("Id", typeof(int));
        movieIds.Rows.Add(2);
        //              ^^^^^
        // This should be Saving Private Ryan's Id

        // 4. Write the SqlExpression
        // Imagine this is a more complex query. I know!
        var query = db.From<Director>();

        var tableParam = query.ConvertToParam(movieIds);
        //                     ^^^^^
        query = query.CustomJoin(@$"INNER JOIN {tableParam} ids ON Director.MovieId = ids.Id");
        //            ^^^^^
        // We're cheating here. We know the table name! I know.

        // 5. Enjoy!
        var spielberg = await db.SelectAsync(query);
        Assert.IsNotNull(spielberg);
        Assert.AreEqual(1, spielberg.Count);
    }
}

Notice we first registered our SqlServerDataTableParameterConverter. More on that later!

After populating some records, we wrote a query using OrmLite SqlExpression syntax and a JOIN to our table parameter using the CustomJoin(). Also, we needed to convert our DataTable into a parameter with the ConvertToParam() method before referencing it.

We cheated a bit. Our Director class has the same name as our table. If that’s not the case, we could use the GetQuotedTableName() method, for example.

3. Write an OrmLite custom converter for DataTable

And this is our SqlServerDataTableParameterConverter,

// This converter only works when passing DataTable
// as a parameter to OrmLite methods. It doesn't work
// with OrmLite LoadSelectAsync method.
public class SqlServerDataTableParameterConverter : OrmLiteConverter
{
    public override string ColumnDefinition
        => throw new NotImplementedException("Only use to pass DataTable as parameter.");

    public override void InitDbParam(IDbDataParameter p, Type fieldType)
    {
        if (p is SqlParameter sqlParameter)
        {
            sqlParameter.SqlDbType = SqlDbType.Structured;
            sqlParameter.TypeName = "dbo.IntList";
            //                       ^^^^^ 
            // This should be our table type name
            // The same name as in the database
        }
    }
}

This converter only works when passing DataTable as a parameter. That’s why it has a NotImplementedException. I tested it with the SelectAsync() method. It doesn’t work with the LoadSelectAsync() method. This last method doesn’t parameterize internal queries. It will bloat our database’s plan cache. Take a look at OrmLite LoadSelectAsync() source code on GitHub here and here to see what I mean.

To make this converter work with the LoadSelectAsync(), we would need to implement the ToQuotedString() and return the DataTable content as a comma-separated list of identifiers. Exercise left to the reader!

4. Write a convenient extension method

And, for compactness, let’s put that CustomJoin() into a beautiful extension method that infers the table and column name to join to,

public static class SqlExpressionExtensions
{
    public static SqlExpression<T> JoinToDataTable<T>(this SqlExpression<T> self, Expression<Func<T, int>> expression, DataTable table)
    {
        var sourceDefinition = ModelDefinition<T>.Definition;

        var property = self.Visit(expression);
        var parameter = self.ConvertToParam(table);

        // Expected SQL: INNER JOIN @0 ON "Parent"."EvaluatedExpression"= "@0".Id
        var onExpression = @$"ON ({self.SqlTable(sourceDefinition)}.{self.SqlColumn(property.ToString())} = ""{parameter}"".""Id"")";
        var customSql = $"INNER JOIN {parameter} {onExpression}";
        self.CustomJoin(customSql);

        return self;
    }
}

We can use it like,

// Before:
// var query = db.From<Director>();
// var tableParam = query.ConvertToParam(movieIds);
// query = query.CustomJoin(@$"INNER JOIN {tableParam} ids ON Director.MovieId = ids.Id");

// After: 
var query = db.From<Director>();
              .JoinToDataTable<Director>(d => d.MovieId, movieIds);

Voilà! That is what I learned (or hacked) today. Things we only find out when reading the source code of our libraries. Another thought: the thing with ORMs is the moment we need to write complex queries, we stretch out ORM features until they break. Often, we’re better off writing dynamic SQL queries. I know, I know! Nobody wants to write dynamic SQL queries by hand. Maybe ask ChatGPT?

If you want to read more about OrmLite and its features, check how to automatically insert and update audit fields with OrmLite and some lessons I learned after working with OrmLite.

Happy coding!