I haven't used LINQ but I have a good idea about how it works. It is certainly great to write concrete SQL-like transformations (be it based on a database, a list, ...).
Where it lacks is abstraction. To make that more concrete, let me ask: can you write LINQ that takes an arbitrary structure and selects every numeric field and calculate the sum over it? And if that is not possible, it should not compile.
I.e. can you define a function "numericsSum(...)" and call it with "List(User(salary: Number, deductions: Number, name: String))" and have it calcuate the sum (ignoring the name-field) but having it fail to compile when calling with "List(User(name: String, date_of_birth: Date))"?
Another example: is it possible to create your own special "join" function, that joins to data structures if they have exactly one common field (which you don't have to specify)?
In both examples, the LINQ compiler must be able to inspect the structure of a type (such as User) at compile time (not runtime) and do pretty arbitrary calculations. Most languages don't support that and I think even LINQ only works with concrete types in that sense. Which, by the way, is already better than what most languages offer - don't get me wrong here. But it is not as powerful as what SQL "compilers" offer - however those are then limited to SQL only, lacking the ability to do general purpose programming.
Where it lacks is abstraction. To make that more concrete, let me ask: can you write LINQ that takes an arbitrary structure and selects every numeric field and calculate the sum over it? And if that is not possible, it should not compile.
I.e. can you define a function "numericsSum(...)" and call it with "List(User(salary: Number, deductions: Number, name: String))" and have it calcuate the sum (ignoring the name-field) but having it fail to compile when calling with "List(User(name: String, date_of_birth: Date))"?
Another example: is it possible to create your own special "join" function, that joins to data structures if they have exactly one common field (which you don't have to specify)?
In both examples, the LINQ compiler must be able to inspect the structure of a type (such as User) at compile time (not runtime) and do pretty arbitrary calculations. Most languages don't support that and I think even LINQ only works with concrete types in that sense. Which, by the way, is already better than what most languages offer - don't get me wrong here. But it is not as powerful as what SQL "compilers" offer - however those are then limited to SQL only, lacking the ability to do general purpose programming.