Blazor has two modes: Blazor client-side is C# code running on a WASM dotnet runtime. It does have the frontend/backend split. Data still has to be serialized (as HTTP calls since that's all a browser can do) but it's done using the same classes & libraries as on the backend.

Blazor server-side has no distinction though because everything is running on the server, and the UI is just a thin stream of HTML updates.

The model you choose is up to you based on resources, latency requirements, payload size, etc. Blazor just gives you more ways to get things done.

Ditto, I've no desire at all for web applications--or heaven forbid clients--to poke directly at a database or cache, or otherwise obviate the need for APIs. One will need to deploy, scale, monitor, and secure those things very differently from one another.

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).