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