TIL: Five lessons while working with OrmLite

Back in the day, for my Advent of Posts I shared some lessons on Hangfire and OrmLite. In this year, for one of my client’s project I’ve been working with OrmLite a lot. Let me expand on those initial lessons and share some others.

1. IgnoreOnUpdate attribute

When using SaveAsync() or any update method, OrmLite omits properties marked with the [IgnoreOnUpdate] attribute in the generated SQL statement. Source

For example,

public class Movie
{
    public string Name { get; set; }
    
    [IgnoreOnUpdate]
    // ^^^^^
    public string OrmLiteDoesNotUpdateThisOne { get; set; }
}

I used this attribute when inserting and updating audit fields to avoid messing with creation dates when updating records.

Also OrmLite has similar attributes for insertions and queries: [IgnoreOnInsertAttribute] and [IgnoreOnSelectAttribute].

2. QueryFirst vs SqlScalar

OrmLite QueryFirst() method requires an explicit transaction as a parameter. Source Unlike QueryFirst(), SqlScalar() uses the same transaction from the input database connection. Source

I learned this because I had a DoesIndexExist() method inside a database migration and it failed with the message “ExecuteReader requires the command to have a transaction…“

This is what I had to change,

private static bool DoesIndexExist<T>(IDbConnection connection, string tableName, string indexName)
{
    var doesIndexExistSql = @$"
      SELECT CASE WHEN EXISTS (
        SELECT * FROM sys.indexes
        WHERE name = '{indexName}'
        AND object_id = OBJECT_ID('{tableName}')
      ) THEN 1 ELSE 0 END";
    
    // Before
    //
    // return connection.QueryFirst<bool>(isIndexExistsSql);
    //                   ^^^^^
    // Exception: ExecuteReader requires the command to have a transaction...

    // After
    var result = connection.SqlScalar<int>(doesIndexExistSql);
    //                      ^^^^^
    return result > 0;
}

3. Create Indexes

Apart from reading and writing records, OrmLite can modify the database schema, for example to create tables and indexes.

To create an index for a table, we could either annotate fields or classes. For example,

[CompositeIndex(unique: false, fieldsNames: "ReleaseYear", "Name", Name = "AnOptionalIndexName")]
// ^^^^^
public class Movie
{
    public int ReleaseYear { get; set; }

    [Index]
    // ^^^^^
    public string Name { get; set; }
}

Also, OrmLite has a CreateIndex() method that receives an expression, like this,

_connection.CreateIndex<Movie>(m => m.Name);
// or
_connection.CreateIndex<Movie>(m => new { m.ReleaseYear, m.Name });

By default, CreateIndex() creates indexes with names like: idx_TableName_FieldName. Source We can omit the index name if we’re fine with this naming convention.

4. Tag queries to easy troubleshooting

To identify the source of queries, OrmLite has two methods: TagWith() and TagWithCallSite().

For example,

var movies =  _connection.From<Movie>()
                    // Some filters here...
                    .Take(10)
                    .TagWith("AnAwesomeQuery")
                    // Or
                    //.TagWithCallSite();

With TagWith(), OrmLite includes a comment at the top of the generated SQL query with the identifier we pass.

For the previous tagged query, this is the generated SQL statement,

-- AnAwesomeQuery

SELECT TOP 10 "Id", "Name", "ReleaseYear" 
FROM "Movie"

With TagWithCallSite(), Ormlite uses the path and line number of the file that made that database call instead.

This is a similar trick to the one we use to debug dynamic SQL queries. It helps up to traceback queries once we found them in our database plan cache.

5. LoadSelectAsync and unparameterized queries

OrmLite has two convenient methods: LoadSelect() and LoadSelectAsync(). They find some records and load their child references.

Let’s write the Movie and Director classes,

public class Movie
{
    public string Name { get; set; }

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

public class Director
{
    [References(typeof(Movie))]
    // ^^^^^^
    public int MovieId { get; set; }

    public string FullName { get; set; }
}

Now let’s use LoadSelectAsync(),

var query = _connection.From<Movie>()
                        // Some filters here
                        .Take(10);
var movies = await _connection.LoadSelectAsync(query);
//                             ^^^^^
// It loads movies and their child directors

When using LoadSelect() and LoadSelectAsync(), OrmLite doesn’t parameterize the internal query used to load the child entities. Arrrggg!

I’m not sure if it’s a bug or a feature. But, to load child entities, OrmLite “inlines” the parameters used to run the parent query. We will see in the plan cache of our database lots of unparameterized queries.

See it by yourself in OrmLite source code, here and here.

After finding out about this behavior, I ended up ditching LoadSelectAsync() and using SelectAsync() instead, like this,

var moviesQuery = _connection.From<Movie>()
                        // Some filters here
                        .Take(10);
var movies = await _connection.SelectAsync(moivesQuery);
if (!movies.Any())
{
    return Enumerable.Empty<Movie>();
}

var directorsQuery = _connection.From<Director>()
                        .Where(d => Sql.In(d.MovieId, moviesQuery.Select<Movie>(d => d.Id)));
var directors = await _connection.SelectAsync(directorsQuery);

foreach (var m in movies)
{
    m.Director = directors.Where(r => r.MovieId == m.Id);
}

Probably there’s a better solution, but that was my workaround to avoid a flooded plan cache. I could afford an extra roundtrip to the database and I didn’t want to write SQL queries by hand. C’mon!

Voilà! These are some of the lessons I’ve learned while working with OrmLite. Again, things we only find out when we adventure to read our libraries source code.

To read more content on OrmLite, check how to pass a DataTable as parameter to an OrmLite query and how to join to a subquery with OrmLite.

Happy coding!

TIL: How to join to subqueries with OrmLite

Another day working with OrmLite. This time, I needed to support a report page with a list of dynamic filters and sorting fields. Instead of writing a plain SQL query, I needed to write a SqlExpression that joins to a subquery. OrmLite doesn’t support that. This is what I learned (or hacked) today.

Let’s imagine we need to write an SQL query for a report to show all directors based on filters like name, birthdate, and other conditions. Next to each director, we need to show their movie count and other counts. For me, it was reservations and rooms. But the idea is still the same.

1. Using a SQL query with a CTE

Since we needed to support filters based on the user’s input, the best solution would be to write a dynamic SQL query. I know, I know! That’s tedious.

If we have the Director and Movie tables, we could write a query like this,

WITH MovieCount AS (
	SELECT DirectorId
	   , COUNT(*) Count
	   /* More aggregations here */
	FROM Movie
	/* Some filters here */
	GROUP BY DirectorId
	)
SELECT d.*, m.Count
FROM Director d
LEFT JOIN MovieCount m
	ON d.Id = m.DirectorId
WHERE d.Country = 'USA' /* More filters here */
/* Sorting by other filters here */
ORDER BY m.Count DESC

I’m using a common table expression, CTE. I have already used them to optimize queries with ORDER BY.

For this example, a simple JOIN without any CTE would work. But let me prove a point and finish this post.

2. Using OrmLite SqlExpression

Let’s use these Director and Movie classes to represent the one-to-many relationship between directors and their movies,

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

    [Reference]
    public List<Movie> Movies { get; set; }

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

    [StringLength(255)]
    public string Country { get; set; }
}

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

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

    [References(typeof(Director))]
    public int DirectorId { get; set; }
}

While trying to translate that query to OrmLite expressions, I realized OrmLite doesn’t support joining to subqueries. Arrrggg!

I rolled up my sleeves and started to take a deeper look.

I ended up hacking this,

using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite;

namespace JoiningToSubqueries;

public class JoinTetsts
{
    [Fact]
    public async Task ItWorksItWorks()
    {
        var connectionString = "...Any SQL Server connection string here...";
        var dbFactory = new OrmLiteConnectionFactory(connectionString);
        using var db = dbFactory.Open();

        // 0. Create Movie and Director tables
        db.CreateTable<Director>();
        db.CreateTable<Movie>();

        // 1. Populate some data
        var jamesCameron = new Director
        {
            FullName = "James Cameron",
            Country = "Canada",
            Movies = new List<Movie>
            {
                new Movie
                {
                    Name = "Titanic"
                }
            }
        };
        await db.SaveAsync(jamesCameron, references: true);

        var stevenSpielberg = new Director
        {
            FullName = "Steven Spielberg",
            Country = "USA",
            Movies = new List<Movie>
            {
                new Movie
                {
                    Name = "Raiders of the Lost Ark"
                },
                new Movie
                {
                    Name = "Jurassic Park",
                }
            }
        };
        await db.SaveAsync(stevenSpielberg, references: true);

        var georgeLucas = new Director
        {
            FullName = "George Lucas",
            Country = "USA",
            Movies = new List<Movie>
            {
                new Movie
                {
                    Name = "Star Wars: A New Hope"
                }
            }
        };
        await db.SaveAsync(georgeLucas, references: true);

        // 2. Write a subquery to do the counting
        var movieCountPerDirector = db.From<Movie>()
                // We could add some filters here...
                .GroupBy(x => x.DirectorId)
                .Select(x => new
                {
                    x.DirectorId,
                    Count = Sql.Custom("COUNT(*)")
                });

        // 2. Write the parent query to filter and sort
        var query = db.From<Director>()
            .LeftJoin(movieCountPerDirector, (d, m) => d.Id == m.DirectorId, subQueryAlias: "mc")
            // ^^^^^
            // It receives a subquery, join expression
            // and alias
            //
            // We could add some filters here...
            .Where(d => d.Country == "USA")
            .Select(d => new
            {
                d,
                MovieCount = Sql.Custom("mc.Count")
                //                      ^^^^
                // Same alias as subQueryAlias parameter
            })
            // We could change the sorting column here too...
            .OrderBy(Sql.Desc("mc.Count"));

        var directors = await db.SelectAsync<DirectorAndMovieCount>(query);

        Assert.Equal(2, directors.Count);
        Assert.Contains(directors, d => d.FullName == "Steven Spielberg");
        Assert.Contains(directors, d => d.FullName == "George Lucas");
    }
}

public class DirectorAndMovieCount
{
    public int Id { get; set; }

    public string FullName { get; set; }

    public string Country { get; set; }

    public int MovieCount { get; set; }
}

After creating the two tables and adding some movies, we wrote the aggregation part inside the CTE with a normal SqlExpression. That’s the movieCountPerDirector variable.

Then, we needed the JOIN between movieCountPerDirector and the parent query to apply all the filters and sorting. We wrote,

var query = db.From<Director>()
    .LeftJoin(movieCountPerDirector,
                (d, m) => d.Id == m.DirectorId,
                subQueryAlias: "mc")
    // ...    

We wrote a LeftJoin() that received a subquery, a joining expression, and an alias.

We might use aliases on the tables to avoid name clashes on the JOIN expression.

3. LeftJoin with another SqlExpression

And this is the LeftJoin() method,

public static partial class SqlExpressionExtensions
{
    public static SqlExpression<T> LeftJoin<T, TSubquery>(
        this SqlExpression<T> expression,
        SqlExpression<TSubquery> subquery,
        Expression<Func<T, TSubquery, bool>> joinExpr,
        string subqueryAlias)
    {
        // This is to "move" parameters from the subquery
        // to the parent query while keeping the right
        // parameter count and order.
        // Otherwise, we could have a parameter named '@0'
        // on the parent and subquery that refer to
        // different columns and values.
        var subqueryParams = subquery.Params.Select(t => t.Value!).ToArray();
        var subquerySql = FormatFilter(expression, subquery.ToSelectStatement(), filterParams: subqueryParams);

        // This is a hacky way of replacing the original
        // table name from the join condition with the
        // subquery alias
        // From:
        //      "table1"."Id" = "table2"."Table1Id"
        // To:
        //      "table1"."Id" = "mySubqueryAlias"."Table1Id"
        var originalCondition = expression.Visit(joinExpr).ToString();

        var definition = ModelDefinition<TSubquery>.Definition;
        var aliasCondition = definition.Alias == null
                                ? originalCondition
                                : originalCondition!.Replace(definition.Alias, subqueryAlias);

        // For example,
        // LEFT JOIN (SELECT Column1 FROM ...) cte ON parent.Id = cte.parentId
        expression = expression.CustomJoin<TSubquery>($"LEFT JOIN ({subquerySql}) {subqueryAlias} ON {aliasCondition}");

        return expression;
    }

    private static string FormatFilter<T>(SqlExpression<T> query, string sqlFilter, params object[] filterParams)
    {
        if (string.IsNullOrEmpty(sqlFilter))
        {
            return string.Empty;
        }

        for (var i = 0; i < filterParams.Length; i++)
        {
            var pLiteral = "{" + i + "}";
            var filterParam = filterParams[i];

            if (filterParam is SqlInValues sqlParams)
            {
                if (sqlParams.Count > 0)
                {
                    var sqlIn = CreateInParamSql(query, sqlParams.GetValues());
                    sqlFilter = sqlFilter.Replace(pLiteral, sqlIn);
                }
                else
                {
                    sqlFilter = sqlFilter.Replace(pLiteral, SqlInValues.EmptyIn);
                }
            }
            else
            {
                var p = query.AddParam(filterParam);
                sqlFilter = sqlFilter.Replace(pLiteral, p.ParameterName);
            }
        }
        return sqlFilter;
    }

    private static string CreateInParamSql<T>(SqlExpression<T> query, IEnumerable values)
    {
        var sbParams = StringBuilderCache.Allocate();
        foreach (var item in values)
        {
            var p = query.AddParam(item);

            if (sbParams.Length > 0)
                sbParams.Append(",");

            sbParams.Append(p.ParameterName);
        }
        var sqlIn = StringBuilderCache.ReturnAndFree(sbParams);
        return sqlIn;
    }
}

Let’s go through it!

It starts by copying the parameters from the subquery into the parent query. Otherwise, we could end up with parameters with the same name that refer to different values.

OrmLite names parameters using numbers, like @0. On the subquery, @0 could refer to another column as the @0 on the parent query.

Then, it converts the joining expression into a SQL string. We used the Visit() method for that. Then, if the subquery has an alias, it replaces the table name with that alias on the generated SQL fragment for the join expression. And it builds the final raw SQL and calls CustomJoin().

I brought the FormatFilter() and CreateInParamSql() methods from OrmLite source code. They’re private on the OrmLite source code.

Voilà! That is what I learned (or hacked) today. Again, things we learn when we read the source code of our libraries. We used the Visit(), CustomJoin(), and two helper methods we brought from the OrmLite source code to make this work.

We only used LEFT JOIN, but we can extend this idea to support INNER JOIN.

As an alternative to this hacky solution, we could write a dynamic SQL query. Next idea! Or we could create an indexed view to replace that counting subquery with a normal join. We could roll a custom method JoinToView() to append a WITH NO_EXPAND to the actual JOIN. I know everybody can’t afford a SQL Server Enterprise edition.

For more OrmLite content, check how to automatically insert and update audit fields with OrmLite, how to pass a DataTable as a parameter to a SqlExpression and some lessons I learned after working with OrmLite.

Happy coding!

Monday Links: CQRS, Negotiating, and Project Managers

One conference and four articles I found interesting in the last month.

CQRS pitfalls and patterns - Udi Dahan

I share the points from this presentation about overarchitecting simple applications when there isn’t a compelying reason for that. I’ve witnessed developers and managers using and promoting Domain Driven Design as the golden hammer to write all aplications, even simple CRUD-like ones. The speaker shares that eventually-consistent CQRS is not for that type of applications.

Althought this talk might seem about coding, it’s more about bussiness and designing software.

Trying to become a better developer by learning more about aviation

I have always been intrigued by planes. We started from the Wright brothers to planes flying by themselves. I have always been interested by software, procedures, and processes that keep planes in the air.

We, as software engineers, have a lot to learn from pilots. For example, “Aviate, Navigate, Communicate” is one of the principles pilots follow during incidents. Their number one priority is keeping the plane flying. We could take that principle to the software engineering world.

Read full article

How to sabotage your salary negotiations efforts before you even start

Interviewing is another skill to master by itself. One thing I’ve found everywhere online is never say a number first. This article shows common mistakes and sample scripts when negotiating salaries.

Read full article

Lessons learned as a software developer turned project manager

I really liked this one! I’ve been in teams where project managers have no idea about building software and they only focus on running meetings and other “ceremonies.” I wish all project managers in software companies had a development background. I know I’m asking for the moon.

Some quotes from this article:

• “the most difficult challenge in a technical project is the communication between parties”
• “the scope of the project and what needs to be done should be clear to everyone”
• “don’t ever, ever, call your colleagues or developers resources or FTE’s (full time equivalent), they’re humans, not beans”

Read full article

How To Opt Out Of The Career Ladder

If you don’t believe in the career ladder, this one is for you. It contains a questionnaire to help taking the first steps into a different path. “being clear with yourself on what role you actually want work to play in your life is an important foundational step to exploring what’s next”

Read full article

Voilà! Another Monday Links. Have you seen developers switching to project management too? Do you have any tips to negotiate salaries? Until next Monday Links.

In the meantime, don’t miss the previous Monday Links on NDC Conference

Happy coding!

How to automatically format SQL files with Git and Poor Man's T-SQL Formatter

I believe we shouldn’t discuss formatting and linting during code reviews. That should be automated. With that in mind, these days, I learned how to automatically format SQL files with Git and Poor Man’s T-SQL Formatter for one of my client’s projects.

I already shared about two free tools to format SQL files. Poor Man’s T-SQL Formatter is one of the two. It’s free and open source.

1. Format SQL files on commits

I wanted to format my SQL files as part of my development workflow. I thought about a pre-commit Git hook for that. I was already familiar with Git hooks since I use one to put task numbers from branch names into commit messages.

After searching online, I found a Bash script to list all created, modified, and renamed files before committing them. I used Phind, “the AI search engine for developers.” These are the query I used:

• “How to create a git commit hook that lists all files with .sql extension?” and as a follow-up,
• “What are all possible options for the parameter –diff-filter on the git diff command?”

Also, I found out that Poor Man’s T-SQL Formatter is available as a Node.js command utility.

Using these two pieces, this is the pre-commit file I came up with,

#!/bin/sh

files=$(git diff --cached --name-only --diff-filter=ACMR)
[ -z "$files" ] && exit 0

for file in "${files[@]}"
do
    if [[ $file == *.sql ]]
    then
        echo "Formatting: $file"

        # 1. Prettify it
        sqlformat -f "$file" -g "$file" --breakJoinOnSections --no-trailingCommas --spaceAfterExpandedComma

        # 2. Add it back to the staging area
        git add $file
    fi
done

exit 0

I used these three options: --breakJoinOnSections, --no-trailingCommas, and --spaceAfterExpandedComma to place ONs after JOINs and commas on a new line.

2. Test the pre-commit hook

To test this Git hook, I created an empty repository, saved the above Bash script into a pre-commit file inside the .git/hooks folder, and installed the poor-mans-t-sql-formatter-cli package version 1.6.10.

For the actual SQL file, I used the query to find StackOverflow posts with many “thank you” answers, Source,

select ParentId as [Post Link], count(id)
from posts
where posttypeid = 2 and len(body) <= 200
  and (body like '%hank%')
group by parentid
having count(id) > 1
order by count(id) desc;

This is where all the magic happened when committing the previous SQL file,

By the way, I use some Git alias as part of my development workflow.

And this is the formatted SQL file,

SELECT ParentId AS [Post Link]
	, count(id)
FROM posts
WHERE posttypeid = 2
	AND len(body) <= 200
	AND (body LIKE '%hank%')
GROUP BY parentid
HAVING count(id) > 1
ORDER BY count(id) DESC;

Voilà! That’s how to format SQL files automatically with Git. The command line version of Poor Man’s T-SQL Formatter is not that fast. But it’s still faster than copying a SQL file, firing a browser with an online linter, formatting it, and pasting it back.

Poor Man’s T-SQL Formatter might not be perfect, but with a simple change in our script, we can bring any other SQL formatter we can call from the command line.

After this trick, I don’t want to leave or read another comment like “please format this file” during code review.

For more content, check my guide to Code Reviews, my Visual Studio setup for C#, and the lessons I’ve learned as a code reviewer.

Happy coding!

A business case against massive unrequested refactorings

Blindly following coding principles is a bad idea.

“Leave the basecamp cleaner,” “Make the change easy then make the easy change”…

Often, we follow those two principles and start huge refactoring sessions with good intentions but without considering the potential consequences.

Let me share two stories of refactoring sessions that led to unintended consequences and the lesson behind them.

Changing Entities and Value Objects

At a past job, a team member decided to refactor the entire solution before working on his task.

He changed every Domain Entity, Value Object, and database table. What he found wasn’t “scalable” in his experience.

The project was still in its early stage and the rest of the team was waiting for his task.

One week later, we were still discussing about names, folder structure, and the need for that refactoring in the first place.

We all were blocked waiting for him to finish the mess he had created.

Changing Class and Table Names

At another job, our team’s architect decided to work over the weekend.

And the next thing we knew next Monday morning was that almost all class and table names had been changed. The architect decided to rename everything. He simply didn’t like the initial naming conventions. Arrrggg!

We found an email in our inboxes listing the things he had broken along the way.

We spent weeks migrating user data from the old database schema to the new one.

These are two examples of refactoring sessions that went sideways. Nobody asked those guys to change anything in the first place.

Even there was no need or business case for that in the first place.

I have a term for these refactoring sessions: massive unrequested refactoring.

The Need for Refactoring

I’m not saying we shouldn’t refactor our code.

I believe in the “leave the basecamp cleaner than the way you found it” mantra.

But, before embarking on a massive refactoring, let’s ask ourselves if it’s truly necessary and if the team can afford it, not only in terms of money but also time and dependencies.

Often, we get too focused on naming variables, functions, and classes to see the bigger picture and the overall project in perspective.

“Perfect is the enemy of good.”

And if there isn’t a viable alternative, let’s split that massive refactoring into separate, focused, and short Pull Requests that can be reviewed in a single review session without much back and forth.

The best refactorings are the small ones that slowly and incrementally improve the health of the overall project. One step at a time. Not the massive unrequested ones.

Voilà! That’s my take on massive unrequested refactorings. Have you ever done one too? What impact did it have? Did it turn out well? Remember, all code we write should move the project closer to its finish line. Often, massive unrequested refactorings don’t do that.

In my two stories, those refactoring sessions ended up blocking people and creating more work.

These refactorings remind me of the analogy that coding is like living in a house. A massive unrequested refactoring would be like a full home renovation while staying there!

Happy coding!