Linq to SQL is still a thing, you may need to click it’s package when you install visual studio 2017+, but it’s still there and it’s still the best SQL interface they have build in my opinion.
We rarely use anything else, but I can see the value of entity if you’re changing your DB a lot. We typically don’t do that though, and to be honest, working with changes with linq to sql, typically goes smoother than entity migrations.
Disclaimer: it’s entirely possible that we simply suck at entity.
LINQ to SQL is a dead end. Look into LINQ to DB https://github.com/linq2db/linq2db which is way faster than LINQ to SQL ever was (faster than even Dapper). If you must you could give EF a try, but stay away from LINQ to SQL.
The value of Dapper IMHO is not the speed. It is precisely the interface. With dapper I can copy paste code between C# and SQL contexts freely. Exploring the database directly in SQL and then use the result directly in the program without a translation step.
LINQ to DB has always seemed interesting, but I don't like it's use of attributes for POCO properties. It's not clear from the introduction whether non-attribute properties will work if the names and types are compatible, or even if the attributes are custom or come from the standard System.ComponentModel.DataAnnotations. The attributes aren't discussed at all in the introductory examples actually.
We rarely use anything else, but I can see the value of entity if you’re changing your DB a lot. We typically don’t do that though, and to be honest, working with changes with linq to sql, typically goes smoother than entity migrations.
Disclaimer: it’s entirely possible that we simply suck at entity.