While there's some stuff in C#/LINQ/EF that's more verbose (left joins are often a nightmare) or not-supported, I'll always say that I prefer writing queries in EF than in SQL, at least when dealing with SQL features that are supported by EF (which is a lot of them, it's a very expressive dialect).
But EF lets you start with FROM, lets you do whichever keywords you need in whichever order (instead of WHERE -> GROUP BY -> HAVING and you've got to CTE or Subquery if you want another GROUP BY). It also lets you access the members of a group because the objects are still treated as a graph instead of being pulverized into a flat table like SQL does. It also makes your FKs into first-class navigational properties of the table.
Like, if I have an addressID and I want to get its country code?
In MS SQL that's
SELECT CountryCode
FROM Country
INNER JOIN ProvState ON ProvState.CountryID = Country.ID
INNER JOIN Address ON Address.ProvStateID = ProvState.ID
WHERE Address.ID = @AddressIDParam
In EF that's
db.Addresses
.Where(a => a.ID == addressIDParam)
.Select(a => a.ProvState.Country.CountryCode)
EF has a hell of a lot of flaws, but linguistically I love it. Yes there's a lot of aliasing boilerplate in EF, but the ability to walk the foreign keys and the fact that you can put the select
after the table name pays off so very well.
Also there's a dialect of LINQ that looks more like SQL but it's kind of weird and I don't love it so I prefer to use the lambda syntax above.
In that dialect, it's
from a in db.Addresses
where a == addressIDParam
select a.ProvState.Country.CountryCode
which is even more terse and SQL-y although I find it a weird linguistic mash-up.