> If you can express it with SQL, you can express it with LINQ.

Not if it uses CTEs or other advanced SQL features. It can do basic select, join, group, sure. But that's just scratching the surface of how powerful SQL is. As for requiring language support: in Elixir, Ecto.Query essentially implements a LINQ-like DSL as a library (albeit with macros). See https://hexdocs.pm/ecto/Ecto.Query.html

From what I see a CTE is just an expression that you can reference.

In C#, if you did something like this

var seniorMales = from user in users where u.Age > 65 && u.Sex == “male” select u

seniorMales is just an IQueryable<User>

Later on you could say

var gaMales = seniorMales.Where(s => s.State == “GA”)

var floridaMale = seniorMales.Where(s => s.State ==“Florida”)

All three of those are just expressions that have not hit the database yet.

Then when you want to actually run the query and limit the number of rows, you can do

var result = floridaMales.Limit(20).ToList()

It would then create a query including a limit clause just as you would expect it to.

Now if you do

floridaMales.ToList().Limit(10)

It would return all of the rows in the table to the client and it would be limited on the client side (don’t do that).

Worse case, if LINQ can’t express in query syntax, a provider can add its own extensions in function syntax and the provider can still parse the expression tree.

Yes, I've been a professional C#/.NET developer before and understand LINQ and Entity Framework. The difference is that CTEs allow you to reference multiple tables at once and do inserts and updates on the intermediate result set. You can also use recursive CTEs to do hierarchical and graph-style queries, which you can't do with LINQ as far as I know.

LINQ also can't do window functions. Like I said, it covers the basics of SQL that most applications need. But as soon as you need to go beyond that you'll be writing custom SQL.

Never used C#, but in Java land we have a similar dsl jooq, which supports everything i’ve ever needed, including window functions https://www.jooq.org/doc/latest/manual/sql-building/column-e...

Yeah, there's nothing else quite like jOOQ as far as I know. I think SQLAlchemy in Python is the only other library that even comes close.

jooq is amazing and one of the things I really miss any time I leave Java land.

Then don't leave