LINQ Syntax and Linq-to-SQL
Linq-to-SQL translates standard query operators of LINQ into T-SQL commands for the versions of SQL Server supported by the ADO.NET 3.5 managed provider. Although a lot of understandable similarities with SQL constructs exist in LINQ, it is key to note that LINQ operators are defined against an ordered sequence of elements. The T-SQL language (as well as any other SQL-based language) works instead with unordered sets of values.
Do not confuse this with the ordering you can apply through the ORDER BY clause. The ORDER BY clause is merely an operation that occurs once the sequence has been obtained. What you get in this case is always an unordered set that is then ordered in the way you requested and just for your convenience. The order is not implicit in the sequence.
As a result, the identity of an element descends from its values, not from its position in the sequence. Sounds familiar? Sure, it's one of the pillars of the SQL language.
Because of this substantial difference between in-memory collections and result sets, certain standard LINQ operators do not work properly or are just not supported in Linq-to-SQL.
Want some examples?
For example, Take and Skip originate quite complex T-SQL statements in fierce contrast with their inherent simplicity. Other operators such as TakeWhile, SkipWhile, Last, ElementAt, Reverse are just not translated to T-SQL and are unsupported by Linq-to-SQL.