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