We use SQLite for storage of all things. The entire application lives on a single machine.

   OurApp.exe: 
   SQLite <-> C# Services <-> Razor Components

When you have a stack like this, Blazor goes into additional dimensions of productive. Every F5 of the app is a complete stack running without any frustration in setting up databases, containers, message buses, etc. None of the concerns regarding scalability apply when you plan to go vertical.

I would not currently argue for developing blazor server apps as a replacement for 100% of web applications. But, there are situations where you have a constrained user base and predictable performance characteristics which provides enough confidence to go all-in on a single fast box.

Not familiar with .NET, but am intrigued by the Sqlite all-the-things bit -- can you run your LINQ queries client-side? (e.g. for offline capable apps)

We use the Blazor Server hosting model, which is inherently incapable of running offline. For our specific use case, this is actually more ideal than any notion of offline capability. In this model, any LINQ would be running on the server.

Now, you certainly can run LINQ client-side if you were on a Blazor WASM (or hypothetically, Desktop) application. This is kind of the entire point of the stack.

LINQ is built into the core .NET runtime (and the syntax into C#) and available everywhere (I think LINQ was introduced with .NET 4 and C# 3.0 some >10 years ago). So doesn't matter if client/server or blazor. But you need some kind of LINQ-to-your-datasource adapter (i.e. to Sqlite, MongoDB etc.) which might have different availability depending on your environment (i.e. a Sqlite adapter would clearly somehow depend on a native sqlite client - but AFAIK is available in Blazor through WebAssembly).