Best of 2023

In 2023, I realized that I’ve been blogging for five years. I started this blog to keep some notes online. Since then, I’ve used my blog to share my learned lessons, answer questions, and rant out loud. I consider my blog as my time capsule.

Last year, I wrote 23 posts. I tried to write one post every other week.

I wrote a series of posts about NullReferenceException, “the billion dollar mistake,” and expanded it into a mini-course on Educative.

I updated my ASP.NET posts to use .NET 6.0, the most recent LTS version the last year.

In one of my client’s projects, I worked with ORMLite. I wrote about passing a datatable as parameter and joining to subqueries. I was too lazy to write SQL queries by hand for those two edge cases. Also, I shared five lessons I learned while working with ORMLite.

I got an interesting question through my contact page. I could shared my advice on how to start an ultralearning project.

Five posts you read the most

These are the five posts I wrote in 2023 you read the most. In case you missed any of them, here they are:

1. Goodbye, NullReferenceException: What it is and how to avoid it.
2. Goodbye, NullReferenceException: Option and LINQ.
3. Goodbye, NullReferenceException: Nullable Operators and References.
4. Too many layers: My take on Queries and Layers. While working with one of my clients, I had to write a lot of response objects and mapping methods to implement read-only API endpoints. This post contains my thoughts on taking layering to the extreme.
5. Goodbye, NullReferenceException: Separate State in Separate Objects. This is how to apply the “make illegal state unrepresentable” mantra to avoid the NullReferenceException.

Last year, I continued working as an independent contractor and content writer. I continued writing for NCache official blog. I started offering onsite courses and training in my city. Unfortunately, I saw a lot of coworkers being laid off. I hope they all had found “greener pastures.”

Voilà! That’s my 2023 in review and your five favorite posts. I hope you enjoyed them as much as I did writing them.

If any of my posts have helped you and you want to support my work, check my courses on Educative, download my ebooks and buy me a coffee on my Gumroad page.

Don’t miss my best of 2021 and best of 2022.

Thanks for reading, and happy coding in 2024!

TIL: How to color a website based on its URL. A visual aid and time saver

These days, I spent a while debugging an issue. After a couple of minutes of scratching my head, I realized I was looking at log entries in the wrong environment. I know! A facepalm moment. I decided to look for a way to change the colors of a browser tab or a website based on the URL I visited. This is what I found.

Coloring a website per URL

After a quick search, I found the URLColors extension in GitHub. It adds an opaque rectangle on top of a website. We only need to configure a keyword for the URL and a hex color. Optionally, it can make the rectangle blink.

For example, this is how I colored Hacker News,

// <site>, <color>, <flash|no>, <timerInSeconds>, <border-width>, <opacity>
news.ycombinator.com, #b58900, no, 0, 10px, 0.5

I used this extension to color the OpenSearch dashboard and other websites I work with. I use the Solarized theme and different color temperatures and rectangle width per environment.

This is what an OpenSearch dashboard looks like,

I go with a red and thick rectangle that blinks for Production-related environments.

Coloring Management Studio bar per connection string

I use a similar trick with SQL Server Management Studio. When connecting to a new server, under the “Options” button, we can change the color of the status bar,

Voilà! No more changes in the Production environment by mistake. No more time wasted looking at the wrong website. Colors are helpful for that.

Even we can change Visual Studio title bar color with the SolutionColor extension.

For more productivity tricks, check How to declutter sites with uBlock Origin filters and how to automatically format SQL files with Git.

Happy coding!

TIL: How to declutter sites with uBlock Origin filters

These days while procastinating on HackerNews, I found this submission. It points to a GitHub repo with some uBlock Origin filters to clean up websites.

I learned that I not only can block elements in a page with uBlock Origin, but also restyle them. Ding, ding, ding! These are the uBlock Origin filters I’m using to declutter some site I visit often.

1. uBlock Origin filters to restyle elements

A uBlock Origin filter to restyle an element looks like this,

<domain>##<selector>:style(<new-css-here>)

Here are the filters I used to restyle HackYourNews and HackerNews,

hackyournews.com##body:style(width: 960px; margin: 0 auto;)
hackyournews.com##.title:style(font-size: 18pt !important;)
hackyournews.com##.ratings:style(font-size: 12pt !important;)
hackyournews.com##.subtext:style(font-size: 14pt !important;)

news.ycombinator.com###hnmain:style(background-color: #fdf6e3; width: 960px !important; margin: 0 auto !important;)
news.ycombinator.com##.rank:style(font-size: 14pt !important;)
news.ycombinator.com##.titleline:style(font-size: 16pt !important;)
news.ycombinator.com##.sitebit.comhead:style(font-size: 12pt !important;)
news.ycombinator.com##.subtext:style(font-size: 12pt !important;)
news.ycombinator.com##.spacer:style(height: 12px !important;)
news.ycombinator.com##.toptext:style(font-size: 12pt !important;)
news.ycombinator.com##.comment:style(font-size: 14pt !important;)
news.ycombinator.com##span.comhead:style(font-size: 12pt !important;)
news.ycombinator.com##.morelink:style(font-size: 14pt !important;)

2. How to install custom uBlock Origin filters in Brave

To install these filters in Brave, let’s navigate to brave://settings/shields/filters, paste the filters, and hit “Save.”

This is how HackerNews looked without my filters,

And this is how it looks after restyling it,

I reduced the page width and increased font size for more readability.

For other sites, I install these extensions: Modern Wiki to restyle Wikipedia, StackOverflow Focus, and Distraction-Free YouTube.

Voilà! That’s how to use uBlock Origin filters to declutter websites. I like clean and minimalistic designs. Before learning about uBlock Origin filters, I started to dabble into browser extension development to restyle sites. With these filters, it’s easier.

What site would you like to declutter with this trick?

For more productivity content, check how to replace keywords in file with Bash, how to rename C# project files with Git, and how to format SQL files with Git and Poor Man’s T-SQL Formatter.

Happy coding!

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!