Intro

  At the moment I am looking for a job, so in my research into coding test platforms, the problems that cannot be solved with just a little thinking and some loops are 80% covered by just 8 categories of problems:

  1. Optimization with overlapping subproblems (coin change, longest increasing subsequence, knapsack variants, matrix chain) - solved with Dynamic Programming (memoization/tabulation).
  2. Pathfinding, connectivity, or level-order processing in grids/graphs (number of islands, shortest path in maze, rotten oranges) - solved with Graph Traversal: BFS and DFS.
  3. Hierarchical data processing (tree diameter, in order traversal, BST validation, lowest common ancestor) - solved with Tree Traversals and Properties (pre/in/post-order, level-order, recursion/DFS on trees).
  4. Exhaustive search with pruning (permutations, subsets, N-Queens, sudoku solver, word search) - solved with Backtracking.
  5. Local optimal choices that work globally (jump game, task scheduler, fractional knapsack) - solved by Greedy Algorithms.
  6. Efficient "top-k" or priority processing (merge k sorted lists, kth largest element, task scheduler with cooldown) - solved with Heaps / Priority Queues.
  7. Efficient union of sets / cycle detection in graphs (redundant connection, number of provinces, Kruskal’s MST) - solved with Union-Find (Disjoint Set Union) with path compression & union-by-rank.
  8. Searching in sorted/monotonic spaces (search in rotated sorted array, minimum in rotated array, binary search on answer for capacity problems) - solved by Binary Search (advanced variants).

  Today I will be covering depth first search (DFS) in tree/graph traversals.

When to use DFS

  Both DFS and BFS (Breadth First Search) are ways of exploring connected elements, but DFS comes more natural for people. You go as deep as you can, then backtrack when you get stuck. Unlike BFS, DFS explores exhaustively and doesn't care about levels. DFS is mostly used to find tree properties (depth, diameter), path finding, validation and most graph connectivity problems.

Recognize a problem that can be solved with DFS by these signs:

  • it has a natural recursive structure (do the same thing for child/neighbor)
  • you need to find a path or check the existence of one (and not necessarily the shortest one)
  • the solution requires processing children/subtrees before their parents (height, diameter, subtree sum, bottom-up computation)
  • is the problem about computing or validating a property along a path (path sum exists, is binary search tree (BST) valid, balanced tree, cycle exists)
  • the input is a tree, binary tree, BST, N-ary tree, trie, or graph, and we do not need level-by-level processing or shortest-path distance

Basically you've got some kind of connected structure and you don't care about what happens on specific levels or you need to compare all paths to find the best.

Example 1

The poster problem for DFS is maze path finding as it lends itself perfectly for the "go as deep as possible and backtrack" idea. Here is the problem statement:

Given a 2D grid representing a maze (0 = open path, 1 = wall), determine if there is a path from the top-left cell (0,0) to the bottom-right cell (m-1, n-1). You can move in 4 directions (up, down, left, right), but cannot go through walls or outside the grid. Assume (0,0) and (m-1,n-1) are open (0).

Example grid:

var maze = new int[][]
{
    [0, 0, 1, 0],
    [0, 0, 0, 0],
    [0, 0, 1, 0],
    [1, 0, 0, 0]
};

Let's look at the naive solution (recursion without visited marking):

bool HasPath(int[][] maze, int row, int col)
{
    int m = maze.Length;
    int n = maze[0].Length;

    if (row < 0 || row >= m || col < 0 || col >= n || maze[row][col] == 1)
        return false;

    if (row == m - 1 && col == n - 1)
        return true;

    // Try all 4 directions
    return HasPath(maze, row - 1, col) ||  // up
           HasPath(maze, row + 1, col) ||  // down
           HasPath(maze, row, col - 1) ||  // left
           HasPath(maze, row, col + 1);    // right
}

var result = HasPath(maze, 0, 0);
Console.WriteLine(result);

Here we try all possible directions to get to a solution and the result is, because there are clearly several paths to the end... a StackOverflowException! This is the error thrown when the entire stack used internally for function recursion has been used up. In other words, we recursed ourselves out of memory. The problem is that we are going in loops forever: up, down, then up again and so on. The solution is marking the visited cells so that we don't do that:

bool HasPath(int[][] maze, int row, int col, 
    /* marking visited */bool[][] visited = null)
{
    int m = maze.Length;
    int n = maze[0].Length;

    // Initialize visited array on the first call
    if (visited == null)
    {
        visited = new bool[m][];
        for (int i = 0; i < m; i++)
            visited[i] = new bool[n];
    }

    if (row < 0 || row >= m || col < 0 || col >= n || maze[row][col] == 1)
        return false;

    if (row == m - 1 && col == n - 1)
        return true;

    // avoid cycles by not visiting the same cell again
    if (visited[row][col])
        return false;

    // mark the cell as visited
    visited[row][col] = true;

    // Try all 4 directions (with visited tracking)
    return HasPath(maze, row - 1, col, visited) ||  // up
           HasPath(maze, row + 1, col, visited) ||  // down
           HasPath(maze, row, col - 1, visited) ||  // left
           HasPath(maze, row, col + 1, visited);    // right
}

var result = HasPath(maze, 0, 0);
Console.WriteLine(result);

The result now is True, as expected.

Now, the path that this version of the code found is really inefficient, too, because of the way the choice of the direction is made, however, in "big O", the complexity is still time O(m*n) and space O(m*n) (because of the visited array). So remember that efficiency in theoretical terms is not the same as efficiency in engineering terms.

It's a nice idea to ponder on how would you alter the algorithm so that it doesn't always try to go back the way it came.

This code guarantees termination (a critical issue in recursive algorithms), avoids redundancy and finds a path - which is not necessarily optimal. Classic DFS. And it is depth first because it goes how far it can go before "backtracking" to a previous position and trying a new path. A breadth first search is obviously possible, but it would take a lot more resources. It would be the best solution for a requirement of finding the fastest path out of the maze, though.

Theory

Now you may have noticed that in this post about trees and graphs I presented something that works with arrays. That's because there is a lot - and I mean a lot - of theory related to graphs in computer science. Trees, too, but they are a special case of graphs, so... remember that when you walk outside looking for shade. OK, enough shade thrown on the computer scientists! It's just very easy to make fun of their hard work.

So let's delve a little in that theory by describing a few terms that will help us understand a true graph problem:

  • A graph is a collection of points (called vertices or nodes) connected by lines (called edges)
    • Vertices / Nodes are the things you care about (people, cities, web pages, computers, tasks, etc.)
    • Edges are the relationships or connections between them (friendship, road, hyperlink, network cable, dependency, etc.)
  • An undirected graph is a graph where the edges don't have a direction or rather they are always bidirectional. If A is connected to B, then B is connected to A.
  • A directed graph (or a digraph) is a graph where edges have a direction. If an edge connects A to B, you need another edge to connect B to A.
  • A connected graph is a graph where all the nodes are connected to at least another node.
  • A cycle is a path that starts and ends at the same node.
  • An acyclic graph is a graph with no cycles.
  • A tree is a fully connected graph with no cycles. Trees are found everywhere because they represent hierarchies: parent-child relationships.
    • every tree is an acyclic graph, but not the other way around. An acyclic graph can have more than one components (separated groups of connected nodes). A tree has N-1 edges, where N is the number of nodes.
  • A binary tree is a tree where a node has at most two children, usually called the left and the right child. Binary tree nodes are assholes, they think of one child as the right one and the other is just left out.
    • they are used a lot in computer science and algorithms, but we're not going to explain them here. As usual, follow the links in the blog to find out more, if you're interested.
    • we will need to know what a binary tree is for when we hear the term BST (Binary search tree) which is used for data structures with fast search, insert and delete.
  • A binary search tree (BST) is a tree where:
    • the values of all the nodes in the left subtree are smaller than the value of the root node
    • the values of all the nodes in the right subtree are larger than the value of the root node
    • all subtrees are valid BSTs

Usually in these problems we get a class called TreeNode which holds an integer value and the two child nodes, something like this:

class TreeNode {
  public int Value {get;set;}
  public TreeNode Left {get;set;}
  public TreeNode Right {get;set;}
}

Use a struct or a record to show you're fancy, but then you will have to explain what's the difference between structs, records and classes.

Other problems use adjacency lists or some other ways of storing graph data.

With this nomenclature in mind, let's see some examples.

Example 2

Problem statement: Given the root of a binary tree, determine if it is a valid binary search tree.

The solution is simple:

bool IsValidBSTDfs(TreeNode node, int min=int.MinValue, int max=int.MaxValue)
{
    if (node == null) return true;

    if (node.Value <= min || node.Value >= max) return false;

    return IsValidBSTDfs(node.Left, min, node.Value)
        && IsValidBSTDfs(node.Right, node.Value, max);
}

I didn't provide a naive solution, because this is a very simple problem, but one of the common errors developers make in trying to solve it is to assume that if the node value of the current node is between left and right and then you recurse, the tree is a valid BST. However, that leads to cases where a value in the left subtree is larger than the root node or a smaller one in the right tree. Always check the requirements!

The time complexity of this is O(n), because it goes through all of the nodes once. The space complexity is O(h), where h is the height of the tree, with worse case scenario O(n) if a tree is composed of nodes with just one child and h=n.

Now you might ask how is this O(h) in space if there is a class for each node? Remember, the O notation measures the complexity of the solution, not of the problem. By recursing through h levels (even if that means it adds and removes n elements on the stack) the stack never increases with more than h elements.

Since we are talking about checking all nodes, a breadth first search is also possible. I will discuss this in the BFS post, but also in another post that I plan to do on how to NOT use the native language recursion and control the entire stack/queue yourself. When you do that, the code gets a little bit more complex, but then the difference between DFS and BFS becomes the choice between a stack and a queue. Stay tuned, it's going to be an instructive post.

Example 3

Problem statement: Given a directed graph (as adjacency list: List<List<int>> graph), detect if it contains a cycle. Nodes are labeled 0 to V-1, where V = graph.Count. A cycle exists if there's a path that starts and ends at the same node (following directions).

In this situation the data structure changes again. The node doesn't have a value, it just has a list of directions towards other nodes and is defined by its index. The structure holding this is a list of lists, where the index in the list represents the node and the value in the list is a list of indexes towards other nodes.

Let's try some code:

bool HasCycle(List<List<int>> graph, int node, bool[] visited=null)
{
    visited ??= new bool[graph.Count];
    if (visited[node]) return true;
    visited[node] = true;

    foreach (int neighbor in graph[node])
    {
        if (HasCycle(graph, neighbor, visited))
        {
            return true;
        }
    }

    return false;
}

List<List<int>> graph = [
    [1,2],
    [2],
    []
];
Console.WriteLine(HasCycle(graph, 0)); // Output: True

The idea here is simple: start with each neighbor of each node then recurse. If you find any node that has been visited before, you have a cycle. Or do you? The code above has a conceptual bug. Can you find it? Look at the example and tell me, is the output correct?

The answer is no. One can reach the same node multiple ways (in our case the index 2 node), but because it's a directed graph, it might not be necessarily a cycle. In our case, node 0 connects to node 1, which then connects to node 2, but node 0 also connects to 2 directly. There is no way to get from node 2 back to 0, though, so there is no cycle.

How would you solve it? The solution - in terms of code - is very simple, but one has to have seen the problem before or have a lot of time to find it. That's why I think this kind of problems are terrible for determining if you're a good developer, but I digress. Here is the working solution:

bool HasCycle(List<List<int>> graph, int node, int[] visited=null)
{
    visited ??= new int[graph.Count];
    visited[node] = 1;

    foreach (int neighbor in graph[node])
    {
        if (visited[neighbor] == 1) return true;
        if (visited[neighbor] == 0 && HasCycle(graph, neighbor, visited))
        {
            return true;
        }
    }
    visited[node] = 2;
    return false;
}

List<List<int>> graph = [
    [1,2],
    [2],
    []
];
Console.WriteLine(HasCycle(graph, 0)); // Output: False

graph =
[
    [1],
    [2],
    [0]
];
Console.WriteLine(HasCycle(graph, 0)); // Output: True

Simply replace the type of the visited array to int, mark a node as "visiting" with 1, then as "visited" with 2. You ignore visited nodes, but if you find one that is in visiting state as a neighbor, then you're in a cycle.

Note: this would be a lot more readable with a three state Enum, and if you feel like it go for it. People will appreciate making the code more readable. But it will take you some extra time to write up the Enum definition, so this is - in my eyes - another reason why these tests measure the wrong thing.

The time complexity of this algorithm is O(V+E) where V is the number of vertices/nodes and E is the number of edges. The space complexity is O(V), as we create an array of size V as part of the solution.

Example 4

This one is very hard to figure out, but easy to implement. You just have to have a feeling for these things to get past the more obvious gotchas.

Problem statement: 

  • You are given a directed acyclic graph (DAG) with n nodes numbered from 0 to n-1.
  • The graph is given as an adjacency list: graph[i] = list of nodes you can go to directly from node i.
  • Each node has a value given in array values[0..n-1].
  • Return the maximum sum of node values you can collect by following any path in the graph

Example:

int[] values = [1, -2, 3, 4];
int[][] graph = [[1, 2], [3], [3], []];

This problem is deceptive for multiple reasons:

  • It looks like "longest path" so people think NP-hard
  • But it's a DAG, so longest path is solvable in linear time
  • Many people waste time trying brute force / backtracking / permutations
  • Many try to keep track of visited nodes unnecessarily (not needed in DAG)
  • The correct insight is subtle for many: "longest path ending at each node" is easy to compute with DP + DFS
  • Negative values make greedy impossible
  • Large n forbids O(n²) solutions
  • You must memoize properly or you'll exceed the accepted time limit
  • The DP state "maximum path ending at node X" is not the first thing most people think of

The post is long enough already, so here is the code:

int[] values = [1, -2, 3, 4];
int[][] graph = [[1, 2], [3], [3], []];

int MaxPathSum(int[] values, IList<IList<int>> graph)
{
    int n = values.Length;
    int?[] memo = new int?[n];   // longest path ending at i

    int globalMax = int.MinValue;

    for (int i = 0; i < n; i++)
    {
        globalMax = Math.Max(globalMax, Dfs(i, values, graph, memo));
    }

    return globalMax;
}

int Dfs(int node, int[] values, IList<IList<int>> graph, int?[] memo)
{
    if (memo[node].HasValue)
        return memo[node].Value;

    int best = values[node];   // just this node

    foreach (int nei in graph[node])
    {
        // take the best path ending at nei + current node
        int candidate = values[node] + Dfs(nei, values, graph, memo);
        best = Math.Max(best, candidate);
    }

    memo[node] = best;
    return best;
}

Console.WriteLine(MaxPathSum(values, graph));

A bonus in this implementation is methods using IList and accepting arrays. You've forgotten that arrays implement IList, didn't you?

Conclusion

Graph theory is a very large and important category in computer science, so explaining all of it simply in a post or two is impossible. But I hope I've clarified a lot of stuff related to the field, at least in regards to interview questions. DFS is one of the most versatile tools in your interview toolbox. Once you get comfortable with the recursive pattern (visit node -> recurse on children/neighbors -> backtrack), you’ll start seeing it everywhere: trees, graphs, backtracking, path problems, cycle detection, and more.

Intro

  At the moment I am looking for a job, so I was forced to enter the world of technical interviews and the weird places like Hackerrank and Leetcode. They absolutely suck, but they have some interesting problems that generations of developers have tried to solve. In my mind there are two kinds of problems: the ones that can be solved with a little thinking and the ones that require some sort of a priori computer science knowledge. Since I have not had a formal software development education and since no matter how many jobs I've had, they always ask me about algorithms, but then I never have to use them, the ones requiring such esoterics always scare me.

  Compounding this is the fact that documentation about such concepts is made for people in computer science courses by people teaching computer science courses, which are usually some kind of math people at heart. 90% of the effort of understanding any of that is reading through their obtuse formalizations. So I've decided I would help myself and other fellow programmers understand these concepts in a way that can be understood.

  In my research into these coding test platforms, the problems that cannot be solved with just a little thinking and some loops are 80% covered by just 8 categories of problems:

  1. Optimization with overlapping subproblems (coin change, longest increasing subsequence, knapsack variants, matrix chain) - solved with Dynamic Programming (memoization/tabulation).
  2. Pathfinding, connectivity, or level-order processing in grids/graphs (number of islands, shortest path in maze, rotten oranges) - solved with Graph Traversal: BFS and DFS.
  3. Hierarchical data processing (tree diameter, in order traversal, BST validation, lowest common ancestor) - solved with Tree Traversals and Properties (pre/in/post-order, level-order, recursion/DFS on trees).
  4. Exhaustive search with pruning (permutations, subsets, N-Queens, sudoku solver, word search) - solved with Backtracking.
  5. Local optimal choices that work globally (jump game, task scheduler, fractional knapsack) - solved by Greedy Algorithms.
  6. Efficient "top-k" or priority processing (merge k sorted lists, kth largest element, task scheduler with cooldown) - solved with Heaps / Priority Queues.
  7. Efficient union of sets / cycle detection in graphs (redundant connection, number of provinces, Kruskal’s MST) - solved with Union-Find (Disjoint Set Union) with path compression & union-by-rank.
  8. Searching in sorted/monotonic spaces (search in rotated sorted array, minimum in rotated array, binary search on answer for capacity problems) - solved by Binary Search (advanced variants).

  Today I will be covering Dynamic Programming (DP), which solves optimizations with overlapping subproblems. And no, I will not be making crass jokes about the abbreviation.

When to use Dynamic Programming

  Recognize a problem that can be solved with DP by these signs:

  • it is too slow to solve with brute force
  • the solution for the problem can be built from best solutions to subproblems
  • the same subproblems (yielding the same result) need solving multiple times

Example 1

  The poster problem for this category is Fibonacci numbers. It's funny, but when I first learned about this series of numbers I was in school, and I knew more math than programming. When you start with the mathematical definition of the Fibonacci function, the naive computer implementation is terrible, so learning Dynamic Programming with this seems reasonable. But after decades away from school, when presented with the problem, the implementation cannot be more different.

  Here is the general definition: a function F(x) which for 0 and 1 returns a defined number, then for any other x, it is computed as F(x-1)+F(x-2). Let's write this in C#, just so you see how silly it is:

int F(int x)
{
    if (x == 0) return 0;
    if (x == 1) return 1;
    return F(x - 1) + F(x - 2);
}

Readable, functional, faithful to the mathematical function, but completely inefficient. Let's add a counter to see how many times this function gets executed:

(int result,int count) F(int x)
{
    if (x == 0) return (0,1);
    if (x == 1) return (1,1);
    var (r1, c1) = F(x - 1);
    var (r2, c2) = F(x - 2);
    return (r1+r2, c1 + c2);
}

// F(10) returns (55, 89)
// F(20) returns (6765, 10946)
// F(30) returns (832040, 1346269)

Now let's think about the problem as a software developer. The client needs the last value of a series of numbers that start with 0, 1, and then add the last two numbers in the series to get the next one. It's an iteration function. The result, when you think about it in these terms, becomes obvious:

// loop solution
(int result, int count) F(int x)
{
    if (x == 0) return (0, 1);
    if (x == 1) return (1, 1);
    var (x1,x2) = (0,1);
    var c = 1;
    for (int i = 2; i <= x; i++)
    {
        (x1,x2) = (x2, x1 + x2);
        c++;
    }
    return (x2, c);
}

// F(30) returns (832040, 30)

So we optimized a function to run 30 times instead of 1.35 million times. Quite an improvement, and all it took was thinking like a software engineer, not like a math geek. This type of solution is called "bottom up dynamic programming" or "tabulation" and is usually the most efficient one. However, there is another solution called "top-down dynamic programming" or "memoization" which is what some computer science guys expect. Let's take a look:

var memo = new Dictionary<int, int>();

int F(int n)
{
    if (n <= 1) return n;
    if (memo.ContainsKey(n)) return memo[n];

    memo[n] = F(n - 1) + F(n - 2);
    return memo[n];
}

Here we used the naive structure of the code, preserving the math semantics, but cached the results of the function for known values. This is actually the kind of stuff they expect from you, even if it's clearly less efficient! Because that's what they teach in school as an example. It's the printing of "hello, world!" and the unit test for a calculator class that only knows how to add two numbers all over again.

They will also ask about complexity or the "Big O notation". If you don't know what that is, it's a fancy notation for describing the magnitude of time and space used by the algorithm. The naive implementation is O(Fibonacci(n+1)) in time, which is funny, as it describes the complexity with the function you're supposed to implement. The tabulation and the memoization versions are O(n) in time, or "linear", the complexity increases linearly with n. The space complexity is theoretically O(1) or "constant" for the naive and the tabulation version, while the memo version is O(n) because we store all n values in the dictionary for no good reason.

I said theoretically, because practically, for the naive and memo versions, there is also extra overhead because of the recursive nature of the implementation. The data for each execution will be stored in the stack, which adds another O(n) in space and is also limited in size. Learn about Stack vs. Heap in programming and well as the Big O notation separately, if you want to drill down.

Theory

The definition sounds like this: Dynamic programming usually refers to simplifying a decision by breaking it down into a sequence of decision steps over time

Two ways to do it:

  • Top-down + Memoization - Start from the big problem, recurse down, but cache results in a dictionary/array so each unique subproblem is solved only once.
  • Bottom-up + Tabulation - Start from the smallest subproblems, build a table iteratively up to the full problem. Usually uses less stack and is faster in practice.

The memoization approach is often easier and more natural, but every problem that can be solved with memoization can usually be solved with tabulation as well. If a problem has a correct recursive formulation with overlapping subproblems, you can always rewrite it as an iterative bottom-up solution by carefully filling a table (or multiple tables) in the right dependency order. The vast majority of DP problems taught and asked in interviews (Fibonacci, knapsack, coin change, longest common subsequence (LCS), edit distance, matrix chain multiplication, longest increasing subsequence, house robber, word break, regex matching, etc.) have clean bottom-up solutions.

Common Pitfalls & Trade-Offs

  • Initialize dp correctly! (Often dp[0]=0 or 1, others int.MaxValue or -1)
  • Watch for off-by-one (array size amount+1)
  • Memo dict vs array: array is faster and uses less memory for integer keys 0..N
  • Space: top-down can use O(N) stack + O(N) memo; bottom-up usually just O(N)
  • When amount N is huge (>10^5 - 10^6), DP might not fit - look for math or greedy instead

Example 2

Let's take another example, the exact change coin problem. 

Problem: Given coins[] of different denominations and int amount, return the fewest coins needed to make exactly amount. Infinite supply of each coin. If impossible, return -1.

The memoization solution comes easier, because it's an iterative fix of the naive implementation:

Dictionary<int, List<int>?> memo = []; // memo: Memoization dictionary to store results for subproblems

IList<int> CoinChange(int[] coins, int amount)
{
    var result = FindMinCoins(coins, amount);
    return result ?? [];
}

List<int>? FindMinCoins(int[] coins, int remaining)
{
    if (remaining == 0)
        return [];

    if (remaining < 0)
        return null;

    if (memo.ContainsKey(remaining)) // memo: Check if the result for this amount is already computed
        return memo[remaining];

    List<int>? best = null;

    foreach (int coin in coins)
    {
        var subResult = FindMinCoins(coins, remaining - coin);
        if (subResult != null)
        {
            var candidate = new List<int>(subResult) { coin };
            if (best == null || candidate.Count < best.Count)
            {
                best = candidate;
            }
        }
    }

    memo[remaining] = best; // memo: Store the computed result in the memoization dictionary
    return best;
}

var result = CoinChange([1, 5, 10, 25], 30);
Console.WriteLine("["+string.Join(", ", result)+"]"); // returns [25, 5]

The lines with "memo:" comments are the difference between the naive and memoized versions.

So what did we do here? We started with the total amount of money and tried every single coin. Once a coin is used, the amount decreases with the value of the coin. If the amount if negative, we overspent and return null for an unusable solution. If the amount is 0, then we return an empty array and stop the recursive processing. If the amount is larger than zero, we now have to solve the exact same problem, but with a smaller amount.

Let's see the tabulation solution.

IList<int> CoinChange(int[] coins, int amount)
{
    if (amount == 0) return [];

    int[] dp = new int[amount + 1];     // dp[i] will hold the minimum number of coins needed for amount i
    int[] prev = new int[amount + 1];   // to reconstruct the solution

    Array.Fill(dp, int.MaxValue);
    dp[0] = 0;

    for (int i = 1; i <= amount; i++)
    {
        foreach (int coin in coins)
        {
            if (i >= coin && dp[i - coin] != int.MaxValue)
            {
                int candidate = dp[i - coin] + 1;
                if (candidate < dp[i])
                {
                    dp[i] = candidate;
                    prev[i] = coin;          // remember which coin we used
                }
            }
        }
    }

    if (dp[amount] == int.MaxValue)
        return [];          // impossible

    // Reconstruct the list of coins
    List<int> result = [];
    int current = amount;

    while (current > 0)
    {
        int coin = prev[current];
        result.Add(coin);
        current -= coin;
    }

    return result;
}

var result = CoinChange([1, 5, 10, 25], 30);
Console.WriteLine("["+string.Join(", ", result)+"]"); // returns [25, 5]

A lot more daunting, but simple once you "get it":

  • We store everything in arrays, where the index is the amount. I guess one could use dictionaries instead, but for small amounts - like the ones used in interview questions - the size of the array is less important than the efficiency of execution of the index in a static array.
  • We fill the array with int.MaxValue, so that any other value is preferable
  • We use the prev array to store the coin we last used for every calculation - smart solution to allow us to reconstruct the list of coins
  • when we just calculate and store the number of coins for each amount up to the desired amount.

It's just like the Fibonacci thing, only more complicated.

Conclusion

Use Dynamic Programming when a problem can be split into smaller pieces that get executed multiple times, yielding the same result each time. The top down approach is more intuitive, but the bottom up approach is usually more efficient.

and has 0 comments

Intro

  Another new feature in .NET that I absolutely love: Raw string literals.

  You probably know about normal strings: they start with a double quote and end with a double quote and every special character, including a double quote, has to be escaped with a backslash. You also know about verbatim strings: they start with @ and then a double quote and can contain any string, including new line characters, with the only exception being the double quote character which must be doubled to be escaped. Great for JSON with single quotes, not so great for XML, for example, but still better than escaping everything with slashes. You might even know about interpolated strings, starting with a $ and supporting templates. I wrote a blog post about it. It can be used with all the other types of strings.

  If you worked with markup language - used in web rich text editors and blogs and instant messengers - you might have used a special syntax for indicating "blocks of code". You do it by enclosing a line of code with backticks (`) or by using three backticks, followed by a new line, then multiple lines of code, then three other backticks to close the block. You can even use something like ```csharp to indicate that the syntax highlighting is supposed to be for C#.

Well, this feature has finally been added to C# itself, just that instead of backticks you use double quotes and you use a // lang = ... comment above it to declare the highlighting - even if Visual Studio and other editors know to recognize common structures like XML and JSON and stuff like that.

Details

This is great for so many things, but I love that it improves readability and allows syntax checking of the content. Check this out:

I specified that this is Regex, so it automatically warned me that I missed a closing parenthesis.

It's really cool for XMLs:

Although for some reason it didn't do syntactic highlighting, look how nice it looks: no doubled or escaped double quotes. You can read the XML, you can copy paste it as it is. This is a thing of beauty. Also, note that the whitespace between the content and the literal string block delimiters is ignored! This didn't happen with verbatim strings, much to my chagrin. In the example above, the first character of the resulting string is less-than (<) and the last is greater-than (>)

But wait... what happens if you want to have three double quotes in the literal string? Why? Because you can. Did you find the Achilles heel for literal strings? No! Because you can have a minimum of three double quotes to declare a literal string. You want three double quotes in the literal? Fine, start and end the literal string with four double quotes!

This leave me to the example in the first image. One can use a ton of double quotes that will not only declare a literal string, but also visually delimit it from the surrounding text. This is the future! If you have more string content than double quotes, something must be really wrong.

More reading

Raw string literal feature specification

and has 0 comments

  Intro

  Finally a technical blog post after so long, right? Well, don't get used to it 😝

  I just learned about a .NET 6 feature, improved in .NET 9, that can help organize your logging, making it both more efficient and readable in the process. This is Compile-time logging source generation, a way to define logger messages in partial classes using attributes decorating method stubs. Source code generators will then generate the methods, which will be efficient, have a readable name and not pollute your code with a log of logging logic.

  There are some constraints that you must follow:

  • Logging methods must be partial and return void.
  • Logging method names must not start with an underscore.
  • Parameter names of logging methods must not start with an underscore.
  • Logging methods cannot be generic.
  • If a logging method is static, the ILogger instance is required as a parameter
  • Code must be compiled with a modern C# compiler, version 9 (made available in .NET 5) or later

As a general rule, the first instance of ILogger, LogLevel, and Exception are treated specially in the log method signature of the source generator. Subsequent instances are treated like normal parameters to the message template.

Details

Here is an example:

public static partial class Log
{
    [LoggerMessage(
        EventId = 0,
        Level = LogLevel.Critical,
        Message = "Could not open socket to `{HostName}`")]
    public static partial void CouldNotOpenSocket(
        this ILogger logger, string hostName);
}

// use like this:
_logger.CouldNotOpenSocket(hostName);

There is support to specifying any of the parameters required in the logger message as method parameters, if you want a hybrid approach.

You can use both static and instance methods - as long as they conform to the rules above.

You can use other attributes to define sensitive logging parameters, a thing called Redaction, like this:

// if you have a log message that has a parameter that is considered private:
[LoggerMessage(0, LogLevel.Information, "User SSN: {SSN}")]
public static partial void LogPrivateInformation(
    this ILogger logger,
    [MyTaxonomyClassifications.Private] string SSN);

// You will need to have a setting similar to this:
using Microsoft.Extensions.Telemetry;
using Microsoft.Extensions.Compliance.Redaction;

var services = new ServiceCollection();
services.AddLogging(builder =>
{
    // Enable redaction.
    builder.EnableRedaction();
});

services.AddRedaction(builder =>
{
    // configure redactors for your data classifications
    builder.SetRedactor<StarRedactor>(MyTaxonomyClassifications.Private);
});

public void TestLogging()
{
    LogPrivateInformation("MySSN");
}

// output will be: User SSN: *****

The generator gives warnings to help developers do the right thing.

You can supply alternative names for the template placeholders and use format specifiers.

More to read

Message Templates

Intro

Welcome to sparse arrays, a JavaScript concept that completely escaped me until now. Let's dig into it.

I wanted to create a game board, an array of 8 rows, themselves arrays of 8 elements so I unthinkingly did a const row=Array(8). I used a console.table on it and it was displayed in a stupid way, but I blamed it on Google devs and moved on. I didn't understand something was amiss until I did a .forEach on a row array that had only three pieces on it and it was executed just three times. What was going on?

Explanation

Well, when you use the Array(integer) constructor you get a sparse array. It has a length, it has items, but it is not a contiguous memory space with N slots for values. The same thing happens when you omit an element from an array declaration, like [1,,2] which is a sparse array different from [1, undefined, 2]. A normal array, in this context, is called a dense array.

In other words, sparse arrays work differently in for and forEach loops! It's almost like an empty object with a length property that has array methods working for it. Think about this code for example:

const arr = { length: 8 }
arr[1]='something';

// displays 8 lines, where only the second is 'something' and the rest are undefined
for (let i=0; i<arr.length; i++) console.log(arr[i]);

// simulated forEach - displays only 2 lines: something and 8
Object.values(arr).forEach(v=>console.log(v));

The simplest way to transform a sparse array into a dense array is to iterate it, like this: [...sparseArray]. You can create a dense array with Array.from( { length:8 } ), however new Array(8) or Array(8) will return a sparse array.

If you look in the docs, sparse arrays are often used with .fill, like this: Array(8).fill(undefined), which will return an array of 8 undefined elements.

Going deeper

So, wait... now there are two types of arrays, they function differently, they must be different classes, right? Actually, no! These are just concepts that apply to Array. Let's see some code:

const x = [1]; // dense array
x.length=10; // sparse array, all slots except the first are 'empty'.
x.fill(2); // dense array, all slots are filled with the value 2
x[1]=undefined; // dense array, second slot contains undefined
delete x[3]; // sparse array, fourth slot is 'empty'
// 1 in x == true;
// 3 in x == false;

What is this madness? This is something new introduced by nasty people who just want to make everything complicated, right? Actually, no. Arrays were always sparse, from the very beginning of the language in 1995. The concept itself was formalized in 2009 for ES5, though, where it was explicitly said that for..in and for..of and forEach, map, filter, etc, will skip empty slots.

Conclusion

JavaScript arrays are misnomers and different from the previous concept of array, used in languages like C or .NET, a fixed and contiguous memory space to store elements based on a positional index. JavaScript arrays are complex objects that allow insertion and removal of items, resizing and, yes, sparsity.

You can work in JavaScript for years and years and still get blindsighted by something like this...

  Array.from is a JavaScript function that until now, I've used exclusively to transform an iterable into an array. I had never considered the powerful factory functionality that it provides, because its declared signature is deceptively simple: Array.from(arrayLike, mapFn, thisArg). So you get something that's like an array (an interable, right?) then you map it (phaw, like I don't know how to map an array!) and a thisArg probably sets the this context for the function (like I don't know how to bind a function!)

  And yet...

  The first "array-like" parameter  doesn't need to be an iterable, it can be an object with a length. Meaning that you can just do something a number of times.

  Example for creating a range: 

// Sequence generator function (commonly referred to as "range", cf. Python, Clojure, etc.)
const range = (start, stop, step) =>
  Array.from(
    { length: Math.ceil((stop - start) / step) },
    (_, i) => start + i * step,
  );

  Getting a sample from an array of string:

const sample = (arr, count) =>
  Array.from(
    { length: count },
    ()=>arr[Math.floor(Math.random()*arr.length)]
  );

  How about generating a random string?

const chars = 'abcdefghijklmnopqrstuvwxyz';
const randomString = (length) =>
  Array.from({ length }, () => chars[Math.floor(Math.random() * chars.length)]).join('');

// OR using the sample function above

const randomString = (length) =>
  sample('abcdefghijklmnopqrstuvwxyz',length).join('');

  And if you don't want an array, but something "array-like", like a NodeList?

class MyClass {}

Array.from.call(MyClass, ["foo", "bar", "baz"]);
// MyClass {0: 'foo', 1: 'bar', 2: 'baz', length: 3}

  I admit Array.from(a,b,c) is functionally equivalent to Array.from(a).map(b.bind(c)), but it's more efficient, as the array is constructed only once. The power of this function comes from its interpretation of the "array-like" first argument. It takes iterable objects and/or containing a length property, thus allowing a more concise, functional way of writing code and avoiding unnecessary loops.

Hope it helps!

  Yep, it's is that easy, thanks to code added by the Chromium devs. You just make sure the focus is on the HTTPS error page, then type "thisisunsafe". A lot more details here: thisisunsafe - Bypassing chrome security warnings.

  Is it a good idea? Probably not. Will it be remove by Google devs some time in the future? Probably yes. But sometimes you just need to access that site and don't care about other stuff.

  Hope it helps!

  A while ago I wrote a browser extension called Bookmark Explorer that no one used because it had such a banal name, but it was pretty cool. I just let it die when Google took it down for being a manifest V2 extension. I plan to modernize it and make it work for modern browsers. It is now also renamed as...

Bookmark Surfer Daedalus!

  OK, it's still a silly name, but you know how naming is the hardest part, right?

  This blog post will become the official web page for the extension.

  But what does it do? Well, it allows to easily navigate in bookmark folders. Let's say you have one of those folders with hundreds of links or you, like me, open a hundred YouTube videos that you plan to eventually watch (yeah, right!) and your main memory consumption is keeping those tabs in the background. Now all you have to do is just put all of these into bookmark folders and open the first one. From it, you can quickly go forward or backward with either mouse or key combinations. The next page in the folder will be preloaded while you read the current one, so that you switch faster.

  Features:

  • right click on a link and add it to the Read Later folder without having to open it (the extension opens it in the background to get the final URL and the title of the page, then closes it immediately)
  • click on extension button to get a popup with arrows forward/backward to navigate in whichever folder the current page is bookmarked in. You also get a button to move the current page to the end of the folder, so as to read it later than the others.
  •  use Ctrl-Shift-K/L for backward/forward navigation. You can also change the key shortcuts in the browser.
  • define patterns that determine what makes a URL unique. For example some web sites have different pages with the exact same URL, but a parameter changed, like YouTube or some blog platforms. Maybe even hashes. You can define the pattern for those domains. The normal pattern is just the domain and the path, ignoring query parameters and hashes.
  • manage the bookmarks in the current folder. You can select them individually, delete them, move them to the end or the start and so on.
  • see the bookmarks that you deleted - whether with the extension or some other mechanism - and choose which one to permanently remove or restore to their original location.
  • notifications of pages that you bookmarked multiple times
  • you can export the URLs of the selected items in an entire folder

  This extension is essential for people who read a lot on the Internet, like researchers or simply people who love information.

Intro

In the .NET world one of the most used method of accessing databases is with Entity Framework (EF), an Object Relational Mapper (ORM) that is tightly integrated with the language syntax. Using the Language Integrated Queries (LINQ) native to .NET languages, it makes data access feel like working with normal .NET collections, without much knowledge of SQL. This has its benefits and drawbacks that I will try not to rant about here. But one of the issues that it consistently creates is confusion regarding the structure of the software project, levels of abstractions and ultimately unit testing.

This post will try to explain why the repository abstraction is ALWAYS useful. Note that many people use repository as a term for abstracted data access, while there is also a repository software pattern which relates to similar things, but it's not the same thing. In here, I will call a repository a series of interfaces abstracting the implementation details of data access and ignore the design pattern completely.

History

Feel free to skip this if you are aware of it, but I have to first address how we got to the idea of repositories to begin with.

In prehistory, code was just written as is, with no structure, everything in, doing what you wanted it to do or at least hoping to. There was no automated testing, just manual hacking and testing until it worked. Each application was written in whatever was on hand, with concerns about hardware requirements more important than code structure, reuse or readability. That's what killed the dinosaurs! True fact.

Slowly, patterns started to emerge. For business applications in particular, there was this obvious separation of the business code, the persistence of data and the user interface. These were called layers and soon separated into different projects, not only because they covered different concerns, but also because the skills necessary to build them were especially different. UI design is very different from code logic work and very different from SQL or whatever language or system was used to persist data.

Therefore, the interaction between the business and the data layer was done by abstracting it into interfaces and models. As a business class, you wouldn't ask for the list of entries in a table, you would require a filtered list of complex objects. It would be the responsibility of the data layer to access whatever was persisted and map it to something understandable to business. These abstractions started to be called repositories.

On the lower layers of data access, patterns like CRUD quickly took over: you defined structured persistence containers like tables and you would create, read, update or delete records. In code, this kind of logic will get abstracted to collections, like List, Dictionary or Array. So there was also a current of opinion that repositories should behave like collections, maybe even be generic enough to not have other methods than the actual create, read, update and delete.

However, I strongly disagree. As abstractions of data access from business, they should be as far removed from the patterns for data access as possible, instead being modelled based on the business requirements. Here is where the mindset of Entity Framework in particular, but a lot of other ORMs, started clashing with the original idea of repository, culminating with calls to never use repositories with EF, calling that an antipattern.

More layers

A lot of confusion is generated by parent-child relationships between models. Like a Department entity with People in it. Should a department repository return a model containing people? Maybe not. So how about we separate repositories into departments (without people) and people, then have a separate abstraction to map then to business models?

The confusion actually increases when we take the business layer and separate it into sublayers. For example, what most people call a business service is an abstraction over applying specific business logic only to a specific type of business model. Let's say your app works with people, so you have a model called Person. The class to handle people will be a PeopleService, which will get business models from the persistence layer via a PeopleRepository, but also do other things, including a mapping between data models and business models or specific work the relates only to people, like calculating their salaries. However, most business logic uses multiple types of models, so services end up being mapping wrappers over repositories, with little extra responsibility.

Now imagine that you are using EF to access the data. You already have to declare a DbContext class that contains collections of entities that you map to SQL tables. You have LINQ to iterate, filter and map them, which is efficiently converted into SQL commands in the background and give you what you need, complete with hierarchical parent-child structures. That conversion also takes care of mapping of internal business data types, like specific enums or weird data structures. So why would you even need repositories, maybe even services?

I believe that while more layers of abstraction may seem like pointless overhead, they increase the human understanding of the project and improve the speed and quality of change. There is a balance, obviously, I've seen systems architected with the apparent requirement that all software design patterns be used everywhere. Abstraction is only useful if it improves code readability and separation of concerns.

Reason

One of the contexts where EF becomes cumbersome is unit testing. DbContext is a complicated system, with a lot of dependencies that one would have to manually mock with great effort. Therefore Microsoft came with an idea: in memory database providers. So in order to test anything, you just use an in memory database and be done with it.

Note that on Microsoft pages this method of testing is now marked with "not recommended". Also note that even in those examples, EF is abstracted by repositories.

While in memory database tests work, they add several issues that are not easy to address:

  • setting up an in memory DbContext requires all of the dependencies to existing entities
  • setting up and starting the memory database for each test is slow
  • in order to get valid database output you need to set up a lot more than what you want to atomically test

Therefore, what ends up happening is that people set up everything in the database within a "helper" method, then create tests that start with this inscrutable and complex method to test even the smallest functionality. Any code that contains EF code will be untestable without this setup.

So one reason to use repositories is to move the testing abstraction above DbContext. Now you don't need a database at all, just a repository mock. Then test your repo itself in integration tests using a real database. The in memory database is very close to a real database, but it is slightly different, too.

Another reason, which I admit I've rarely seen be of actual value in real life, is that you might want to change the way you access the data. Maybe you want to change to NoSql, or some memory distributed cache system. Or, which is much more likely, you started with a database structure, perhaps a monolithic database, and now you want to refactor it into multiple databases with different table structures. Let me tell you right off the bat that this will be IMPOSSIBLE without repositories.

And specific to Entity Framework, the entities that you get are active records, mapped to the database. You make a change in one and save the changes for another and you suddenly get the first entity updated in the db, too. Or maybe you don't, because you didn't include something, or the context has changed.

The proponents of EF always hype the tracking of entities as a very positive thing. Let's say you get an entity from the database, you then do some business, then you update the entity and save it. With a repo you would get the data, then do business, then get the data again in order to perform a little update. EF would keep it in memory, know it wasn't updated before your change, so it would never read it twice. That's true. They are describing a memory cache for the database that is somehow aware of database changes and keeps track of everything you handle from the database, unless instructed otherwise, bidirectionally maps database entries to complex C# entities and tracks changes back and forth, while being deeply embedded in the business code. Personally, I believe this plethora of responsibilities and lack of separation of concerns is a lot more damaging than any performance gained by using it. Besides, with some initial effort, all that functionality can still be abstracted in a repository, or maybe even another layer of memory caching for a repository, while keeping clear borders between business, caching and data access.

In fact, the actual difficulty in all of this is determining the borders between systems that should have separate concerns. For example, one can gain a lot of performance by moving filtering logic to stored procedures in the database, but that loses testability and readability of the algorithm used. The opposite, moving all logic to code, using EF or some other mechanism, is less performant and sometimes unfeasible. Or where is the point where data entities become business entities (see the example above with Department and Person)?

Perhaps the best strategy is to first define these borders, then decide on which technology and design are going to fit into that.

My conclusion

I believe that service and repository abstractions should always be used, even if the repository is using Entity Framework or other ORM underneath. It all boils down to separation of concerns. I would never consider Entity Framework a useful software abstraction since it comes with so much baggage, therefore a repository much be used to abstract it in code. EF is a useful abstraction, but for database access, not in software.

My philosophy of software writing is that you start with application requirements, you create components for those requirements and abstract any lower level functionality with interfaces. You then repeat the process at the next level, always making sure the code is readable and it doesn't require understanding of the components using or the ones used at the current level. If that's not the case, you've separated concerns badly. Therefore, as no business application ever had requirements to use a specific database or ORM, the data layer abstraction should hide all knowledge of those.

What does business want? A filtered list of people? var people = service.GetFilteredListOfPeople(filter); nothing less, nothing more. and the service method would just do return mapPeople(repo.GetFilteredListOfPeople(mappedFilter)); again nothing less or more. How the repo gets the people, saves the people or does anything else is not the concern of the service. You want caching, then implement some caching mechanism that implements IPeopleRepository and has a dependency on IPeopleRepository. You want mapping, implement the correct IMapper interfaces. And so on.

I hope I wasn't overly verbose in this article. I specifically kept code examples out of it, since this is more of a conceptual issue, not a software one. Entity Framework may be the target of most of my complaints here, but this applies to any system that magically helps you in small things, but breaks the important ones.

Hope that helps!

A few years ago I wrote about this, but in less detail. Here is a more refined version of the same idea.

Intro

Unit tests are both boon and bane to developers. They allow quick testing of functionality, readable examples of use, fast experimentation of scenarios for just the components involved. But they also can become messy, need maintenance and update with every code change and, when done lazily, can't hide bugs rather than reveal them.

I think the reason unit testing is so difficult is because it's associated with testing, something other than code writing, and also that unit tests are written in a way opposite to most other code we write.

In this post I will give you a simple pattern of writing unit tests that will enhance all the benefits, while eliminating most of the cognitive dissonance with normal code. Unit tests will remain readable and flexible, while reducing duplicate code and adding no extra dependencies.

How to unit test

But first, let's define a good unit test suite.

To properly test a class, it has to be written in a certain way. In this post we will cover classes using constructor injection for dependencies, which is my recommended way of doing dependency injection.

Then, in order to test it, we need to:

  • cover positive scenarios - when the class does what it's supposed to do, with various combinations of setup and input parameters to cover the whole functionality
  • cover negative scenarios - when the class fails in the correct way when the setup or input parameters are wrong
  • mock all external dependencies
  • keep all of the test setup, action and assertion in the same test (what is normally called the Arrange-Act-Assert structure)

But that's easier said than done, because it also implies:

  • setting up the same dependencies for every test, thus copying and pasting a lot of code
  • setting up very similar scenarios, with just one change between two tests, again repeating a lot of code
  • generalizing and encapsulating nothing, which is what a dev would normally do in all of their code
  • writing a lot of negative cases for few positive cases, which feels like having more testing code than functional code
  • having to update all of these tests for every change to the tested class

Who loves that?

Solution

The solution is to use the builder software pattern to create fluid, flexible and readable tests in the Arrange-Act-Assert structure, while encapsulating setup code in a class complementing the unit test suite for a specific service. I call this the MockManager pattern.

Let's start with a simple example:

// the tested class
public class Calculator
{
    private readonly ITokenParser tokenParser;
    private readonly IMathOperationFactory operationFactory;
    private readonly ICache cache;
    private readonly ILogger logger;

    public Calculator(
        ITokenParser tokenParser,
        IMathOperationFactory operationFactory,
        ICache cache,
        ILogger logger)
    {
        this.tokenParser = tokenParser;
        this.operationFactory = operationFactory;
        this.cache = cache;
        this.logger = logger;
    }

    public int Calculate(string input)
    {
        var result = cache.Get(input);
        if (result.HasValue)
        {
            logger.LogInformation("from cache");
            return result.Value;
        }
        var tokens = tokenParser.Parse(input);
        IOperation operation = null;
        foreach(var token in tokens)
        {
            if (operation is null)
            {
                operation = operationFactory.GetOperation(token.OperationType);
                continue;
            }
            if (result is null)
            {
                result = token.Value;
                continue;
            }
            else
            {
                if (result is null)
                {
                    throw new InvalidOperationException("Could not calculate result");
                }
                result = operation.Execute(result.Value, token.Value);
                operation = null;
            }
        }
        cache.Set(input, result.Value);
        logger.LogInformation("from operation");
        return result.Value;
    }
}

This is a calculator, as is tradition. It receives a string and returns an integer value. It also caches the result for a specific input, and logs some stuff. The actual operations are being abstracted by IMathOperationFactory and the input string is translated into tokens by an ITokenParser. Don't worry, this is not a real class, just an example. Let's look at a "traditional" test:

[TestMethod]
public void Calculate_AdditionWorks()
{
    // Arrange
    var tokenParserMock = new Mock<ITokenParser>();
    tokenParserMock
        .Setup(m => m.Parse(It.IsAny<string>()))
        .Returns(
            new List<CalculatorToken> {
                CalculatorToken.Addition, CalculatorToken.From(1), CalculatorToken.From(1)
            }
        );

    var mathOperationFactoryMock = new Mock<IMathOperationFactory>();

    var operationMock = new Mock<IOperation>();
    operationMock
        .Setup(m => m.Execute(1, 1))
        .Returns(2);

    mathOperationFactoryMock
        .Setup(m => m.GetOperation(OperationType.Add))
        .Returns(operationMock.Object);

    var cacheMock = new Mock<ICache>();
    var loggerMock = new Mock<ILogger>();

    var service = new Calculator(
        tokenParserMock.Object,
        mathOperationFactoryMock.Object,
        cacheMock.Object,
        loggerMock.Object);

    // Act
    service.Calculate("");

    //Assert
    mathOperationFactoryMock
        .Verify(m => m.GetOperation(OperationType.Add), Times.Once);
    operationMock
        .Verify(m => m.Execute(1, 1), Times.Once);
}

Let's unpack it a little. We had to declare a mock for every constructor dependency, even if we don't actually care about the logger or the cache, for example. We also had to set up a mock method that returns another mock, in the case of the operation factory.

In this particular test we wrote mostly setup, one line of Act and two lines of Assert. Moreover, if we want to test how the cache works inside the class we would have to copy paste the entire thing and just change the way we setup the cache mock.

And there are the negative tests to consider. I've seen many a negative test doing something like: "setup just what is supposed to fail. test that it fails", which introduces a lot of problems, mainly because it might fail for completely different reasons and most of the time these tests are following the internal implementation of the class rather than its requirements. A proper negative test is actually a fully positive test with just one wrong condition. Not the case here, for simplicity.

So, without further ado, here is the same test, but with a MockManager:

[TestMethod]
public void Calculate_AdditionWorks_MockManager()
{
    // Arrange
    var mockManager = new CalculatorMockManager()
        .WithParsedTokens(new List<CalculatorToken> {
            CalculatorToken.Addition, CalculatorToken.From(1), CalculatorToken.From(1)
        })
        .WithOperation(OperationType.Add, 1, 1, 2);

    var service = mockManager.GetService();

    // Act
    service.Calculate("");

    //Assert
    mockManager
        .VerifyOperationExecute(OperationType.Add, 1, 1, Times.Once);
}

Unpacking, there is no mention of cache or logger, because we don't need any setup there. Everything is packed and readable. Copy pasting this and changing a few parameters or some lines is no longer ugly. There are three methods executed in Arrange, one in Act and one in Assert. Only the nitty gritty mocking details are abstracted away: there is no mention of the Moq framework here. In fact, this test would look the same regardless of the mocking framework one decides to use.

Let's take a look at the MockManager class. Now this will appear complicated, but remember that we only write this once and use it many times. The whole complexity of the class is there to make unit tests readable by humans, easily to understand, update and maintain.

public class CalculatorMockManager
{
    private readonly Dictionary<OperationType,Mock<IOperation>> operationMocks = new();

    public Mock<ITokenParser> TokenParserMock { get; } = new();
    public Mock<IMathOperationFactory> MathOperationFactoryMock { get; } = new();
    public Mock<ICache> CacheMock { get; } = new();
    public Mock<ILogger> LoggerMock { get; } = new();

    public CalculatorMockManager WithParsedTokens(List<CalculatorToken> tokens)
    {
        TokenParserMock
            .Setup(m => m.Parse(It.IsAny<string>()))
            .Returns(tokens);
        return this;
    }

    public CalculatorMockManager WithOperation(OperationType operationType, int v1, int v2, int result)
    {
        var operationMock = new Mock<IOperation>();
        operationMock
            .Setup(m => m.Execute(v1, v2))
            .Returns(result);

        MathOperationFactoryMock
            .Setup(m => m.GetOperation(operationType))
            .Returns(operationMock.Object);

        operationMocks[operationType] = operationMock;

        return this;
    }

    public Calculator GetService()
    {
        return new Calculator(
                TokenParserMock.Object,
                MathOperationFactoryMock.Object,
                CacheMock.Object,
                LoggerMock.Object
            );
    }

    public CalculatorMockManager VerifyOperationExecute(OperationType operationType, int v1, int v2, Func<Times> times)
    {
        MathOperationFactoryMock
            .Verify(m => m.GetOperation(operationType), Times.AtLeastOnce);
        var operationMock = operationMocks[operationType];
        operationMock
            .Verify(m => m.Execute(v1, v2), times);
        return this;
    }
}

All of the required mocks for the test class are declared as public properties, allowing any customization of a unit test. There is a GetService method, which will always return an instance of the tested class, with all of the dependencies fully mocked. Then there are With* methods which atomically set up various scenarios and always return the mock manager, so that they can be chained. You can also have specific methods for assertion, although in most cases you will be comparing some output with an expected value, so these are here just to abstract away the Verify method of the Moq framework.

A MockManager base class

Mock managers are very useful and make for readable code and nice tests, but they can be tiresome to write. When you want to test hundreds of classes, writing a mock manager for all becomes really annoying. Luckily, you can use a base class that makes this really easy!

So let's rewrite the CalculatorMockManager class with this base class:

public class CalculatorMockManager
    : MockManagerBase<Calculator>
{
    private readonly Dictionary<OperationType, Mock<IOperation>> operationMocks = [];

    public CalculatorMockManager WithParsedTokens(List<CalculatorToken> tokens)
    {
        GetMock<ITokenParser>()
            .Setup(m => m.Parse(It.IsAny<string>()))
            .Returns(tokens);
        return this;
    }

    public CalculatorMockManager WithOperation(OperationType operationType, int v1, int v2, int result)
    {
        var operationMock = new Mock<IOperation>();
        operationMock
            .Setup(m => m.Execute(v1, v2))
            .Returns(result);

        GetMock<IMathOperationFactory>()
            .Setup(m => m.GetOperation(operationType))
            .Returns(operationMock.Object);

        operationMocks[operationType] = operationMock;

        return this;
    }

    public CalculatorMockManager VerifyOperationExecute(OperationType operationType, int v1, int v2, Func<Times> times)
    {
        GetMock<IMathOperationFactory>()
            .Verify(m => m.GetOperation(operationType), Times.AtLeastOnce);
        var operationMock = operationMocks[operationType];
        operationMock
            .Verify(m => m.Execute(v1, v2), times);
        return this;
    }
}

The first thing we notice is that the base class is a generic one, taking as a generic parameter the type of the class we want to test. Then there are no more properties for mocks, the methods setting up mocks use a GetMock<T> method instead. And finally there is no GetService method.

How does it work? Well, when GetService is called, using reflection we find the constructor parameters and find a value to use in them. By default a mock will be generated for each, which then can be accessed with the GetMock<T> method. However, there are two methods that are virtual, allowing to customize the resolution of either the constructor parameter itself or that of its mock. Moreover, you can just decorate a property of the mock manager class with an attribute, and that property value will be used as the constructor parameter of that type.

And if you really liked the idea of Mock properties, then you can define them as read only properties that call GetMock. Here are the base class and the attribute used to decorate properties as constructor parameter providers:

/// <summary>
/// Base class for mock managers
/// </summary>
/// <typeparam name="TSubject"></typeparam>
public abstract class MockManagerBase<TSubject>
    where TSubject : class
{
    protected readonly Dictionary<Type, Mock> mocks = [];
    private TSubject _service;
    private Dictionary<Type, PropertyInfo> _properties;

    public TSubject GetService()
    {
        if (_service is not null) return _service;
        var subjectType = typeof(TSubject);
        var ctors = subjectType.GetConstructors();

        //Currently supports only services with 1 ctor
        var theCtor = ctors.Single();

        var services = new ServiceCollection();
        foreach (var serviceType in theCtor.GetParameters())
        {
            var paramType = serviceType.ParameterType;
            object paramInstance = CreateInstance(paramType);
            services.AddSingleton(paramType, paramInstance);
        }

        var serviceProvider = services.BuildServiceProvider();
        _service = ActivatorUtilities.GetServiceOrCreateInstance<TSubject>(serviceProvider);
        return _service;
    }

    /// <summary>
    /// Override this to have custom values for constructor parameters
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    protected virtual object CreateInstance(Type type)
    {
        var instance = GetFromProperty(type);
        if (instance is null)
        {
            Mock mock = CreateMock(type);
            mocks[type] = mock;
            instance = mock.Object;
        }
        return instance;
    }

    /// <summary>
    /// Override this to have custom Mocks for constructor parameters
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    protected virtual Mock CreateMock(Type type)
    {
        var mockType = typeof(Mock<>).MakeGenericType(type);
        var mock = GetFromProperty(mockType) ?? Activator.CreateInstance(mockType);
        return mock as Mock;
    }

    private object GetFromProperty(Type type)
    {
        _properties ??= this.GetType()
            .GetProperties(BindingFlags.Public | BindingFlags.Instance)
            .Where(prop => prop.GetCustomAttribute<ConstructorParameterProviderAttribute>() is not null)
            .ToDictionary(prop => prop.PropertyType, prop => prop);
        if (!_properties.TryGetValue(type, out PropertyInfo prop)) return null;
        return prop.GetValue(this);
    }

    /// <summary>
    /// Get the mock for type <typeparamref name="T"/>
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public Mock<T> GetMock<T>()
        where T : class
    {
        GetService(); // ensure mocks are created
        return mocks[typeof(T)] as Mock<T>;
    }
}
/// <summary>
/// Mark a property in a <see cref="MockManagerBase{TSubject}"/> as a provider 
/// for a type or the mock of a type used in constructor injection
/// </summary>
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class ConstructorParameterProviderAttribute : Attribute {}

The test methods should remain unchanged, unless you need a Mock instance outside the mock manager methods, and then you use GetMock.

Here is a more complex mock manager:

public class ExampleMockManager
    : MockManagerBase<Example>
{
    [ConstructorParameterProvider]
    public ExampleDbContext Db { get; } = new MockDbContext();

    protected override object CreateInstance(Type type)
    {
        if (type == typeof(string))
        {
            return DateOnly.FromDateTime(DateTime.Now).ToString("o");
        }
        return base.CreateInstance(type);
    }

    protected override Mock CreateMock(Type type)
    {
        if (type == typeof(IFancyService))
        {
            var mock = CreateFancyMock<IFancyService>();
            return mock;
        }
        return base.CreateMock(type);
    }
}

public class Example
{
    public Example(
        string name, 
        IFancyService fancyService,
        ExampleDbContext dbContext,
        INormalService normalService)
    {
        // ...
    }
}

In this mock manager, the Db property is used to populate the ExampleDbContext constructor parameter, an override of CreateInstance will generate the string as the date of today and an override of CreateMock will create a different type of mock, but just for IFancyService, INormalService gets the regular mock. This is a contrived example, as the following rewrite does exactly the same thing in less code that is much more readable:

public class ExampleMockManager
    : MockManagerBase<Example>
{
    [ConstructorParameterProvider]
    public ExampleDbContext Db { get; } = new MockDbContext();

    [ConstructorParameterProvider]
    public Mock<IFancyService> FancyMock { get; } = new();

    [ConstructorParameterProvider]
    public string Today => DateOnly.FromDateTime(DateTime.Now).ToString("o");
}

Conclusion

This pattern now aligns test writing with code writing:

  • abstract the things you don't care about in any context
  • write once and use many times
  • humanly readable, self documenting code
  • small methods with low cyclomatic complexity
  • intuitive code writing

Writing a unit test now is trivial and consistent:

  1. instantiate the mock manager of the class you want to test (or write one based on the steps above)
  2. compose specific scenarios for the test (with auto complete for existing already covered scenario steps)
  3. execute the method you want to test with test parameters
  4. check everything is as expected

The abstraction doesn't stop at the mocking framework. The same pattern can be applied in every programming language! The mock manager construct will be very different for TypeScript or JavaScript or something else, but the unit test would pretty much look the same way.

Hope this helps!

Here is the scenario:

  • you have two tables: a source of recent data and an SQL Server destination table with the same structure that has to be updated with that data
  • they are both on the same server (we assume that if the data source is external you have copied it into a local temporary table)
  • the destination table is in active use
  • the source destination may be in active use also
  • the tables are large, locking them and waiting for the merge to finish is not an option
  • the difference between the tables is usually small. Not so small as to be irrelevant, but small relative to the size of the tables
  • we don't care about full data consistency (any intermediate state between the current and the updated one is acceptable)

There are some quirks in SQL Server that affect our task, mainly that if you try to modify 5000 rows or more of a table, then the locking will escalate from row locks to a table lock, turning it unusable during the merge. Also, for operations involving a lot of data, the log of the database will increase to accommodate that size, then the changes will be persisted and the log invalidated. This takes a lot of disk space and if anything happens during the operation, the entire operation will be rolled back, with all of the data in the log having to be restored, which also takes a lot of time, blocking the database.

The first idea is to find rows that are different, new or missing and just sync 5000 of them at a time. One can do this with a MERGE or classic INSERT, UPDATE, DELETE operations. And this works, but it has a major flaw: after the first 5000 rows have been found and synchronized, the next operation will go through them again anyway to find the next 5000 rows, and again, and again, and again. The execution time will increase exponentially with every new batch of rows.

What we actually need is to take a list of changes and apply them in batches, kind of like an artificial transaction log. The following stored procedure will take two parameters: the full schema name of the source table and the full schema name of the destination table. It will create a log of changes, take 4900 of them at a time and apply them to the destination table. The only restrictions are that the destination table has to have primary key columns and the source and destination tables have the same columns. For performance reasons, it's best that the source table also has the same primary keys or at least an index on the same columns. The usage would look like EXEC usp_MergeTables 'SomeSchemaIncludingDbo.SourceTable','MaybeSomeOtherSchema.DestinationTable'

I will explain what it does after the code:

-- this is for batched merge (del, upd, ins) of the data of a table into another (nolock/readpast for reduced locking of source and destination tables)

CREATE OR ALTER PROC usp_MergeTables(@SourceTable NVARCHAR(256),@DestinationTable NVARCHAR(256))
AS
BEGIN

SET NOCOUNT ON

IF NOT EXISTS(SELECT * 
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id=t.schema_id
WHERE REPLACE(REPLACE(@SourceTable,']',''),'[','')=s.name+'.'+t.name)
BEGIN

  DECLARE @Err1 VARCHAR(100) = 'Source table '+@SourceTable+' not found!'
  ;THROW 50404,@Err1,1

END

SELECT CAST(s.name as NVARCHAR(Max)) as schemaName,CAST(t.name as NVARCHAR(Max)) as tableName,CAST(c.name as NVARCHAR(Max)) as columnName,c.is_computed,c.is_identity, 
CASE 
  WHEN tp.name IN ( 'varchar', 'char', 'varbinary' ) THEN tp.name + 
                  CASE WHEN c.max_length = -1 THEN '(max)'       
                         ELSE '(' + CAST(c.max_length AS VARCHAR(4)) + ')' END       
  --types that have an unicode character type that requires length to be halved 
  WHEN tp.name IN ( 'nvarchar', 'nchar' ) THEN tp.name + 
                   CASE WHEN c.max_length = -1 THEN '(max)'       
                         ELSE '(' + CAST(c.max_length / 2 AS VARCHAR(4)) + ')'       
                                                              END
   --types with a datetime precision 
  WHEN tp.name IN ( 'time', 'datetime2', 'datetimeoffset' ) THEN tp.name + 
                                            '(' + CAST(c.scale AS VARCHAR(4)) + ')' 
  --types with a precision/scale 
  WHEN tp.name IN ( 'numeric', 'decimal' ) 
  THEN tp.name + '(' + CAST(c.precision AS VARCHAR(4)) + ',' +
                                             CAST(c.scale AS VARCHAR(4)) + ')'
  --timestamp should be reported as rowversion 
  WHEN tp.name = 'timestamp' THEN 'rowversion' 
  --and the rest. Note, float is declared with a bit length, but is 
  --represented as either float or real in types  
  ELSE tp.name 
END as typeName,
ISNULL(( SELECT pk.is_primary_key FROM sys.indexes pk
INNER JOIN sys.index_columns ic
ON ic.object_id = pk.object_id
AND ic.index_id = pk.index_id
AND c.column_id=ic.column_id
WHERE t.object_id = pk.object_id
AND pk.is_primary_key = 1
),0) as is_primary_key
INTO #tgtColumns
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id=t.schema_id
INNER JOIN sys.all_columns c
ON t.object_id=c.object_id
INNER JOIN sys.types tp
ON c.system_type_id=tp.system_type_id
AND c.user_type_id=tp.user_type_id
WHERE REPLACE(REPLACE(@DestinationTable,']',''),'[','')=s.name+'.'+t.name

IF NOT EXISTS(SELECT * FROM #tgtColumns)
BEGIN

  DECLARE @Err2 VARCHAR(100) = 'Destination table '+@DestinationTable+' not found!'
  ;THROW 50404,@Err2,2

END

IF NOT EXISTS(SELECT * FROM #tgtColumns WHERE is_primary_key=1)
BEGIN

  DECLARE @Err3 VARCHAR(100) = 'Destination table '+@DestinationTable+' has no primary keys!'
  ;THROW 50404,@Err3,3

END

DECLARE @operSql NVARCHAR(Max)
DECLARE @delSql NVARCHAR(Max)
DECLARE @updSql NVARCHAR(Max)
DECLARE @insSql NVARCHAR(Max)
DECLARE @identityInsertOn NVARCHAR(Max)=''
DECLARE @identityInsertOff NVARCHAR(Max)=''

IF EXISTS(SELECT * FROM #tgtColumns WHERE is_identity=1)
BEGIN

  SET @identityInsertOn=CONCAT(N'
SET IDENTITY_INSERT ',@DestinationTable,N' ON
')
  SET @identityInsertOff=CONCAT(N'
SET IDENTITY_INSERT ',@DestinationTable,N' OFF
')

END


SELECT @operSql = CONCAT(N'DROP TABLE IF EXISTS #oper

SELECT *
   INTO #oper
FROM (
SELECT ',STRING_AGG(CONCAT(N'ISNULL(src.[',c.columnName,N'],tgt.[',c.columnName,N']) as [',c.columnName,N']'),N', '),N',
  CASE
    WHEN ',STRING_AGG(CONCAT(N'src.[',c.columnName,N'] IS NULL'),N' AND '),N' THEN ''DEL''
    WHEN ',STRING_AGG(CONCAT(N'tgt.[',c.columnName,N'] IS NULL'),N' AND '),N' THEN ''INS''
    WHEN (
    	SELECT * FROM ',@SourceTable,N' R
    	WHERE ',STRING_AGG(CONCAT('R.[',c.columnName,N'] = src.[',c.columnName,N']'),N' AND '),N'
    	FOR XML PATH(''Row''), ELEMENTS XSINIL
      ) <> (
    	SELECT * FROM ',@DestinationTable,N' R
    	WHERE ',STRING_AGG(CONCAT('R.[',c.columnName,N'] = tgt.[',c.columnName,N']'),N' AND '),N'
    	FOR XML PATH(''Row''), ELEMENTS XSINIL
      ) THEN ''UPD''
   END as __oper__
   FROM ',@SourceTable,N' src (NOLOCK)
   FULL OUTER JOIN ',@DestinationTable,N' tgt (NOLOCK)
   ON ',STRING_AGG(CONCAT('src.[',c.columnName,N'] = tgt.[',c.columnName,N']'),N' AND '),'
) x
WHERE __oper__ IS NOT NULL

CREATE INDEX temp_id ON #oper(',STRING_AGG(CONCAT('[',c.columnName,N']'),N', '),N')
CREATE INDEX temp_oper ON #oper(__oper__)')
FROM #tgtColumns c
WHERE c.is_primary_key=1

SELECT @delSql = CONCAT(N'

DECLARE @batch TABLE(',STRING_AGG(CONCAT('[',c.columnName,N'] ',c.typeName),N', '),N',
                   PRIMARY KEY(',STRING_AGG(CONCAT('[',c.columnName,N']'),N', '),N'))

DECLARE @ROWS INT = 1

WHILE (@ROWS>0)
BEGIN

  DELETE TOP (4900) tgt
    OUTPUT ',STRING_AGG(CONCAT('deleted.[',c.columnName,N']'),N', '),N'
	INTO @batch
  FROM ',@DestinationTable,N' tgt (READPAST)
  INNER JOIN #oper src
    ON ',STRING_AGG(CONCAT('src.[',c.columnName,N'] = tgt.[',c.columnName,N']'),N' AND '),N'
  WHERE src.__oper__=''DEL''

  SET @ROWS=@@ROWCOUNT

  DELETE o
  FROM #oper o
  INNER JOIN  @batch b
  ON ',STRING_AGG(CONCAT('o.[',c.columnName,N'] = b.[',c.columnName,N']'),N' AND '),N'
  
  DELETE FROM @batch

  IF (@ROWS=0)
    SELECT @ROWS=COUNT(*) FROM #oper WHERE __oper__=''DEL''

END')
FROM #tgtColumns c
WHERE c.is_primary_key=1


SELECT @updSql = CONCAT(N'

SET @ROWS = 1

WHILE (@ROWS>0)
BEGIN

  UPDATE tgt
  SET ',(SELECT STRING_AGG(CONCAT('[',c.columnName,N'] = src.[',c.columnName,N']'),N', ')
FROM #tgtColumns c
WHERE c.is_primary_key=0 AND c.is_computed=0),N' OUTPUT ',STRING_AGG(CONCAT('inserted.[',c.columnName,N']'),N', '),N'
	INTO @batch
  FROM ',@DestinationTable,N' tgt (READPAST)
  INNER JOIN ( 
    SELECT TOP (4900) s.*
    FROM #oper o
	INNER JOIN ',@SourceTable,N' s
	ON ',STRING_AGG(CONCAT('s.[',c.columnName,N'] = o.[',c.columnName,N']'),N' AND '),N'
    WHERE __oper__=''UPD''
  ) src
    ON ',STRING_AGG(CONCAT('src.[',c.columnName,N'] = tgt.[',c.columnName,N']'),N' AND '),N'

  SET @ROWS=@@ROWCOUNT

  DELETE o
  FROM #oper o
  INNER JOIN  @batch b
  ON ',STRING_AGG(CONCAT('o.[',c.columnName,N'] = b.[',c.columnName,N']'),N' AND '),N'
  
  DELETE FROM @batch

  IF (@ROWS=0)
    SELECT @ROWS=COUNT(*) FROM #oper WHERE __oper__=''UPD''

END')
FROM #tgtColumns c
WHERE c.is_primary_key=1


SELECT @insSql = CONCAT(N'

SET @ROWS = 1

WHILE (@ROWS>0)
BEGIN

  INSERT INTO ',@DestinationTable,N'(',(SELECT STRING_AGG(CONCAT('[',c.columnName,N']'),N', ')
FROM #tgtColumns c
WHERE c.is_computed=0),N') OUTPUT ',STRING_AGG(CONCAT('inserted.[',c.columnName,N']'),N', '),N'
    INTO @batch
  SELECT TOP (4900) ',(SELECT STRING_AGG(CONCAT('s.[',c.columnName,N']'),N', ')
FROM #tgtColumns c
WHERE c.is_computed=0),N'
  FROM #oper o
  INNER JOIN ',@SourceTable,N' s
  ON ',STRING_AGG(CONCAT('s.[',c.columnName,N'] = o.[',c.columnName,N']'),N' AND '),N'
  WHERE __oper__=''INS''

    SET @ROWS=@@ROWCOUNT
	
  DELETE o
  FROM #oper o
  INNER JOIN  @batch b
  ON ',STRING_AGG(CONCAT('o.[',c.columnName,N'] = b.[',c.columnName,N']'),N' AND '),N'
  
  DELETE FROM @batch

  IF (@ROWS=0)
    SELECT @ROWS=COUNT(*) FROM #oper WHERE __oper__=''INS''

END

DROP TABLE #oper
')
FROM #tgtColumns c
WHERE c.is_primary_key=1

DROP TABLE #tgtColumns

--PRINT @operSql
--PRINT @delSql
--PRINT @updSql
--PRINT @identityInsertOn
--PRINT @identityInsertOff
--PRINT @insSql

DECLARE @sql NVARCHAR(Max) = CONCAT(@operSql, @delSql, @identityInserton, @updSql, @insSql, @identityInsertOff)
EXEC sp_sqlexec @sql

END

OK, this is a large thing, but the principles used are simple:

  • first we create a table containing information about the columns of the tables: schema, table, column name, whether the column is primary key or computed, and the type name. The schema and table name are not used, but may be useful for debugging. Note that this SP doesn't check the tables have the same number and type of columns. That's on you to ensure.
  • using the column information we create four strings that will contain the SQL for the following operations:
    • create an "operations table"
    • delete rows that are not needed
    • update rows that need to be updated
    • insert rows that are missing
  • there are four strings mostly for debugging purposes to keep them smaller than the 8000 characters that Microsoft SQL Server Management Studio can print at a time, but they are concatenated and executed as one.
  • implementation details:
    • we use FOR XML PATH('Row'), ELEMENTS XSINIL to generate a string with all the data in each row, so we don't have to compare rows column by column. We could have made this work with comparisons, but the code would have been bulky and ugly when comparing for NULL or for having values. ELEMENTS XSINIL will ensure that there is a difference between empty space and NULL.
    • a FULL OUTER JOIN is used to find (based on the primary key columns of the destination table) if rows need to be either deleted, updated or inserted. That operation is specified in the __oper__ column
    • the operations table is thus created, containing the primary key values and the operation required with two indexes: one on the primary keys and one on the operation. These indexes are not really that relevant, so one could choose to remove them.
    • a @batch table variable is used with a PRIMARY KEY on the primary key columns of the destination table, for performance reasons
    • the batching is done via a DELETE... OUTPUT operation. We delete the 4900 rows we process and we output them in the @batch table
    • for each segment we either: delete destination rows with the same primary keys for 'DEL', update destination rows with the same primary keys for 'UPD' and insert source rows with the same primary keys for 'INS'
    • before we update, we set IDENTITY_INSERT to ON and at the end to OFF, if any identity columns
    • if some rows were affected by the operation, then we continue in the same segment. If not, then we look in the operations table to see if there are still rows to be processed. No rows may be affected while there are still rows to be processed due to the locking avoidance hints
  • improvements to avoid locking:
    • when we generate the operations table we use NOLOCK, which reads uncommitted values ignoring locks. This may not be what you want, but if the source table is locked for whatever reason, this ensures the merge operation is not blocked
    • when we process the batches we use READPAST, which ignores locked destination rows. This ensures that rows that can be updated will be, while the others can be done later, meaning that if you have 1 locked row, the merge operation will go around it, then wait until it is unlocked to update or delete it.

If you want to see what the generated SQL looks like, uncomment the PRINT lines and comment the EXEC one.

Now, I just wrote this stored procedure, so I may have missed some cases. Let me know if you find a situation where this doesn't work as expected. 

Hope it helps!

What is the structure of a table created from another via SELECT * INTO [Second] FROM [First] ?

A simple question, indeed, a basic one, but one that I have never asked myself until today. I honestly believed that the result is a generic table containing the same column names and types and their values and nothing else.

The answer, though, is strange. If the original column is NOT NULL, the resulting column will also be NOT NULL. If the original column has IDENTITY, the column in the second table will also have IDENTITY. And, as you know, you can't add or remove IDENTITY from existing columns without dropping and adding them back. The DEFAULT value, though, is not transferred.

The Microsoft reference page says: 

The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list.

...

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

  • The SELECT statement contains a join.
  • Multiple SELECT statements are joined by using UNION.
  • The identity column is listed more than one time in the select list.
  • The identity column is part of an expression.
  • The identity column is from a remote data source.

...

Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. If these objects are required, you can create them after executing the SELECT...INTO statement.

...

Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order.

...

When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed.

So, my assumptions were wrong, but still what do you do when you want to achieve exactly that: create a new schema-less table with the same columns in terms of name and data type and value only? It's a bit ugly, but I have not found a better alternative. Just UNION ALL with a SELECT that has the same number of (nullable) columns and no rows. like this:

SELECT *
  INTO [Second]
FROM [First]
  UNION ALL
(SELECT NULL, NULL,NULL,NULL WHERE 1=0)

Assuming that the First table had four columns, this will result in a table having the same column names, types and values as the original table, but all columns will be nullable and with no identity.

Hope it helps!

and has 1 comment

I have been maintaining a Chrome browser extension I wrote for more than a year now and I always get the occasional user asking me if I can make it work with Firefox. And until now I said no, because I use the "world" feature in manifest.json. But I was wrong.

You see, Google is aggressively pushing for manifest version 3, discontinuing support for browser extensions using v2. So my extension was written from the beginning with version 3. In order to load the scripts and CSS files that I needed to run in the context of the page, I used the world:"MAIN" feature. When I tried it with v2, Chrome immediately told me "The 'world' property is restricted to extensions with 'manifest_version' set to 3 or higher." (Or higher. lol) So when I looked for how to use world in Firefox manifest v3 I got a lot of documentation about Xray Vision and how they absolutely refuse to implement the world feature.

I googled, I scoured the web, I read all of Stack Overflow, I even tried LLMs like ChatGPT of Gemini to hilarious results, like Gemini accusing me I want to circumvent the security of the browser. No. There is no simple way of doing the same thing in Manifest v3 on Firefox. Case closed, right? I mean, who even uses Firefox? Less than 2.5% of people on the Internet. And it's a shit browser.

But no, here comes a guy and tells me that he made it work. HOW?! By now you probably guessed it, I left enough hints after all. You just have to use manifest version 2! 'world' is only restricted for version 3 in Chrome browsers!!!

Although now that I am writing this post I see stuff like Manifest V3 updates landed in Firefox 128 so maybe it something that works only recently (the article is from July 2024). Hmm..

I guess there is a silver lining to the fact I refused users for a year now, because I had a year in which I didn't have to deal with the buggy debugger (heh!) in Firefox.

Anyway, if you have the same problem, that's your solution: make a v2 manifest for Firefox and a v3 manifest for Chromium browsers. Or use it in v3 as well, because apparently Firefox changed their minds. And I wrote this blog with such glee that I would help people that had the same problem as me. Ugh!

NO! I will not make it work for Safari! Eat a dick!

  Just a few days ago I was writing on how important it is to tell Entity Framework what SQL type to use in order to avoid costly conversions. In fact, it wasn't so much an EF issue as it was an SQL one. Converting even character types to character types or changing collation was surprisingly expensive.  In this post I will show you how important it is to choose the right type for your querying columns, especially the primary key. 

  First imagine this scenario: you get some data from an outside source, rows and rows of it, and you have to store them and query them in SQL. The value that uniquely identifies a row is a small string, maybe 50 characters long. How do you proceed?

  My first naive solution was the most obvious one: just create a table that has a column for each value in the rows and put the primary key on the identifying one. But this leads to immediate performance losses:

  • by default, a primary key is a clustered index - text is not sequential, so at every insert the database engine will physically move huge swaths of data in order to place the rows in the alphabetical order of their identifiers
  • a primary key is a unique index - meaning text will have to get compared to other text in order to determine uniqueness, which is slow
  • by default, SQL is case insensitive - meaning that all text comparisons will have to be made taking into account capitalization and accents
  • 50 characters is a lot - even without Unicode support, it's 50 bytes, which is 12 times more than an integer, meaning the primary key index will be large; and slow

  "But!", you will undoubtedly say, if you put the primary key on some other column, you will still have to create a unique index on the identifier. Isn't this just pushing the problem farther down the road? The size and speed limitations will be the same. And primary keys are clustered only by default, but they can be declared as not clustered. And SQL doesn't need to be case insensitive, all you have to do is change the collation of the column to be binary and it will be compared faster. Wouldn't that solve the problem?

  No. In fact, my final solution which worked five times faster, did not have an index on the identifier column AT ALL. Incidentally, I did end up changing the collation, but only because the idiots sending me the data were doing it case sensitive.

  Without further ado, here is what I did:

  • an INT column with IDENTITY(1,1) as the primary key - which ensures a fast insertion due to the sequential nature of the value, fast query speed and low usage of disk space for the index
  • an INT column holding the checksum of the identifier - which when indexed, is fast to query and doesn't use a lot of disk space for the index

   So how do I query on the identifier? Simple: I calculate the checksum of the string and then I look it up in the database - which uses the index to locate the few strings that have the same checksum, then just finds the right one by enumerating through them. I query on the checksum column AND the text identifier. And there is an added bonus: I only need to do this once. If I need the record from the DB again, I query it directly through the integer primary key.

  Entity Framework has this automatic memory cache so when I am querying on the database entity using a business model - as good separation of concerns practice would dictate - it gets it really fast from memory. Because the memory cache also uses just the int to identify an entity, which means double the benefits!

  The eagle eyed reader will have noticed that I am not using a unique index on the identifier, so technically I could create multiple rows with the same one. However, my application is always looking for the existing record first. But if you really worry about data consistency, the index on the checksum column can be replaced with a unique index on the checksum and identifier column. It will take more space, but it will be just as fast.

  Another thing that you may have noticed is that I use a code checksum, not the database provided functions to achieve the same. At first glance, it's an instant win: just create a persisted computed column that calculates the checksum or binary checksum of the identifier column. However, this would be weird when having to query, since you would have to craft a stored procedure or a custom SQL command to get the identifier and query on its checksum. In my case I just calculate a checksum - and not use the lazy string.GethashCode function which may be subject to change and it's already different between 32 and 64 bit systems.

  Of course, if you want your text columns to be case and/or accent insensitive, you will have to store the hash code of the lowercase and unaccented string or use an implementation that is case and accent insensitive. This may not be trivial.

  Further tests showed that just using a non clustered index on the identifier column, even a unique one, was just slightly slower, maybe 5%. However, the space taken by indexes increased by 20%. So I might understand why you would find it a bit off putting and skip the checksum part.

  Hope this helps!

  P.S. Why did this solution provide such a huge performance gain? Obviously the SQL team would have implemented a sort of checksum for their text index, this should have been working natively and faster than any possible implementation I could make. Well, I don't know the answer. In fact, this all could be some quirk of Entity Framework and the SQL queries would not be optimizable to such a degree. I will attempt to test that using purely SQL commands. But meanwhile, all the points I made above are valid and with a little more work you can have a lot more control on how the system works.

  I've built an application and, like any lazy dev out there, I focused on the business logic, the project structure, the readability, comments, the dependency injection, the unit tests, you know... the code. My preference is to start from top to bottom, so I create more and more detailed implementations of interfaces while going down to the metal. The bottom of this chain is the repository, that class which handles database access, and I've spent little to understand or optimize that code. I mean, it's DB access, you read or you write stuff, how difficult can it be?

  When it was time to actually test it, the performance of the application was unexpectedly bad. I profiled it and I was getting reasonable percentages for different types of code, but it was all taking too long. And suddenly my colleague says "well, I tried a few things and now it works twice as fast". Excuse me?! You did WHAT?! I have been trying a few things too, and managed to do diddly squat! Give me that PR to see what you did! And... it was nothing I could see.

  He didn't change the code, he just added or altered the attributes decorating the properties of models. That pissed me off, because I had previously gone to the generated SQL with the SQL Profiler and it was all OK. So I executed my code and his code and recorded the SQL that came out:

  • was it the lazy loading? Nope. The number of instructions and their order was exactly the same
  • was it the explicit declaration of the names of indexes and foreign keys? Nope. Removing those didn't affect performance.
  • was it the ChangeTracker.LazyLoadingEnabled=false thing? Nope, I wasn't using child entities in a way that could be affected.
  • was there some other structure of the generated SQL? No. It was exactly the same SQL! Just my code was using thousands of CPU units and his was using none.
  • was it magic? Probably, because it made no sense whatsoever! Except...

Entity Framework generates simple SQL queries, but it doesn't execute them as you and I would. It constructs a string, then uses sp_executesql to run it. Something like this:

exec sp_executesql N'SELECT TOP(1) [p].[ID], [p].[TXT], [p].[LUP_TS]

FROM [sch].[table] AS [p]

WHERE [p].[ID] = @__p_0',N'@__p_0 nvarchar(64)',@__p_0='xxxx'

Do you see it? I didn't until I started to compare the same SQL in the two versions. And it was the type of the parameters! Note that the aptly named parameter @__p_0 is an NVARCHAR. The actual column in the database was VARCHAR! Meaning that the code above was unnecessarily always converting values in order to compare them. The waste of resources was staggering!

How do you declare the exact database type of your columns? Multiple ways. In my case there were three different problems:

  • no Unicode(false) attribute on the string columns - meaning EF expected the columns to be NVARCHAR
  • no Typename parameter in the Column attribute where the columns were NTEXT - meaning EF expected them to be NVARCHAR(Max)
    • I guess one could skip the Unicode thing and instead just specify the type name, but I haven't tested it
  • using MaxLength instead of StringLength - because even if their descriptions are very similar and MaxLength sounds like applying in more cases, it's StringLength that EF wants.

From 40-50ms per processing loop, it dropped to 21ms just by fixing these.

Long story short: parametrized SQL executed with sp_executesql hides a possible performance issue if the columns that you compare or extract have slightly different types than the one of the parameters.

Go figure. I hate Entity Framework!