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