DEV Community: Costin Manda

Raw string literals in .NET, both useful and entertaining

Costin Manda — Wed, 08 Oct 2025 15:48:10 +0000

Original post: https://siderite.dev/blog/raw-string-literals-in-net-both-useful-and-enterta

Intro

Another new feature in .NET that I absolutely love: Raw string literals, optionally followed by a new_line, the content of the string, and then ends with the same number of quotes that the literal started with. ").

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 three backticks followed by "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.

Generate readable and efficient logging methods

Costin Manda — Wed, 08 Oct 2025 14:15:41 +0000

Original post: https://siderite.dev/blog/generate-readable-and-efficient-logging-methods

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.

Sparse arrays in JavaScript

Costin Manda — Sun, 15 Jun 2025 08:24:43 +0000

Original post: https://siderite.dev/blog/sparse-arrays-in-javascript/

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 - more powerful than you think

Costin Manda — Fri, 09 May 2025 10:12:47 +0000

Original post: https://siderite.dev/blog/arrayfrom---more-powerful-than-you-think/

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!

Bypassing blocking Chrome certificate screens

Costin Manda — Mon, 28 Apr 2025 20:13:15 +0000

Original URL: https://siderite.dev/blog/bypassing-blocking-chrome-certificate-screens

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!

Data access in code, using repositories, even with ORMs

Costin Manda — Thu, 12 Dec 2024 11:10:46 +0000

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!

MockManager in unit tests - a builder pattern used for mocks

Costin Manda — Thu, 12 Dec 2024 09:02:34 +0000

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 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(
                new List<CalculatorToken> {
                    CalculatorToken.Addition, CalculatorToken.From(1), CalculatorToken.From(1)
                }
            );
        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.

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:

instantiate the mock manager of the class you want to test (or write one based on the steps above)
compose specific scenarios for the test (with auto complete for existing already covered scenario steps)
execute the method you want to test with test parameters
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!

Migrate data from an SQL Server table to another, without locking it

Costin Manda — Fri, 08 Nov 2024 11:44:59 +0000

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:

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

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_identity,c.is_computed, 
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
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

DECLARE @operSql NVARCHAR(Max)
DECLARE @delSql NVARCHAR(Max)
DECLARE @updSql NVARCHAR(Max)
DECLARE @insSql NVARCHAR(Max)

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_identity=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_identity=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_identity=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_identity=1

SELECT @insSql = CONCAT(N'

SET IDENTITY_INSERT ',@DestinationTable,N' ON

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

SET IDENTITY_INSERT ',@DestinationTable,N' OFF

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

DROP TABLE #tgtColumns

--PRINT @operSql
--PRINT @delSql
--PRINT @updSql
--PRINT @insSql

DECLARE @sql NVARCHAR(Max) = CONCAT(@operSql, @delSql, @updSql, @insSql)
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, or that there are primary keys on the destination. 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'
- 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!

SELECT INTO quirk I had no idea about

Costin Manda — Fri, 08 Nov 2024 10:53:31 +0000

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!

Firefox and content_scripts.world

Costin Manda — Fri, 13 Sep 2024 13:58:11 +0000

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!

Huge SQL performance gains from using INTs

Costin Manda — Tue, 03 Sep 2024 19:06:11 +0000

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 due, 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 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 change and it's 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.

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.

EF Core 6 - correct types halving the execution time!

Costin Manda — Fri, 30 Aug 2024 19:56:45 +0000

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!

DEV Community: Costin Manda

Raw string literals in .NET, both useful and entertaining

Intro

Details

More reading

Generate readable and efficient logging methods

Intro

Details

More to read

Sparse arrays in JavaScript

Intro

Explanation

Going deeper

Conclusion

Array.from - more powerful than you think

Bypassing blocking Chrome certificate screens

Data access in code, using repositories, even with ORMs

Intro

History

More layers

Reason

My conclusion

MockManager in unit tests - a builder pattern used for mocks

Intro

How to unit test

Solution

Conclusion

Migrate data from an SQL Server table to another, without locking it

SELECT INTO quirk I had no idea about

Firefox and content_scripts.world

Huge SQL performance gains from using INTs

EF Core 6 - correct types halving the execution time!