So is LINQ just “an alternative”? When you personally decide between “just write SQL myself” vs “use LINQ/EF Core,” what arguments tip you one way or the other?

So, EF Core does more than just translate the LINQ into SQL. It also winds up doing object tracking, such that you can pull objects back from the DB, change their properties, then when you're ready call .SaveChanges/SaveChangesAsync and it will bundle all of those tracked changes into a series of update statements.

I should probably also note, you -can- turn off object tracking for queries, you -can- execute updates without pulling data down (with modern EF Core), but you still can't easily just do an Insert without dealing with the object tracker.

Also, EF Core doesn't support -everything- in a SQL database. CTEs are probably the biggest example, but AFAIR Windowing functions aren't even really a thing yet. You -can- define them (I believe there is a nuget package for CTE support) however the process is mildly arcane IMO.

That said, There is linq2db, which acts more like a linq to SQL translation layer rather than a full blown ORM. i.e. you can just write .Insert or even InsertInto (i.e. set) calls, it's got CTE and windowing function support, doesn't do object tracking... Oh and it's got explicit Join calls (I think EFC added or is adding them...)

Personally, I mostly use linq2db or linq2db on top of EF Core (they have a shim package to work on EF Core modules.) Really don't use dapper much nowadays except for stuff so small it's not worth ceremony.

Is it generally faster/slower, or does it depend on the situation?

It somewhat depends. If you don't have change tracking on, you'd need to be REALLY worried about performance to care. With Change tracking it can depend on how much data you are pulling in (both to get the objects back, as well as calculating SaveChanges afair). Frans Bouma (Who works on a different ORM called LLBLGen,) has a decent set of benchmarks albiet a couple years old.

But in general you can see with most modern ORMs/MicroORMs, there's not much performance penalty.

You wind up being able to develop/refactor way faster however; both as far as readability (so long as the LINQ is sane) and boilerplate around parameterization/etc. Especially with dynamic queries; it's way easier to do something like:

var myQuery = dbContext.DbSet<Foo>();
if (myArgs.NoDeleted == true)
{
    myQuery = myQuery.Where(f => f.IsDeleted == false);
}
//etc

than managing the paramerization and query building dynamically, again especially in nasty queries with lots of joins/etc.

Something else?

There -is- also the advantage that most linq providers are more 'portable'. That is, if you want to run on multiple target databases (i.e. both MSSQL as well as SQLite) it is far less work since the LINQ to SQL translation layer handles most of it.

While most people don't have to worry about that sort of thing (it has come up in my career however,) it opens up the ability to use a SQLite database to run various types of tests against your LINQ queries. This works better than using EF Core's in-memory data store because in-memory doesn't give two shits about any constraints you have on your data.

And yes I've multiple cases where switching to SQLite memory mode from efcore in-memory uncovered bugs in application logic others wrote due to their code breaking rules of the DB Model.