Why is the query operator 'ElementAt' is not supported in LINQ to SQL?

Question

In LINQ to SQL, I get the exception "The query operator 'ElementAt' is not supported." When trying to use the ElementAt extension method on an IQueryable returned from a LINQ to SQL query.

Here is the stack trace:

at System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
   at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
   at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
   at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node)
   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.Table`1.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.ElementAt[TSource](IQueryable`1 source, Int32 index)

Now I realize to get rid of this exception and to use ElementAt I could call '.ToList()' before using the extension method and it will work. That is fine, but I still don't like the fact that this is a runtime exception (and what seems like LSP violation).

Is there a reason why these methods cannot be supported? Is it just because they cannot be translated easily into SQL? What other IQueryable/IEnumerable extension methods are not supported, is there a list somewhere?

It would be nice to avoid runtime exceptions.

Answer 1

From MSDN, Standard Query Operator Translation (LINQ to SQL) - this article contains the full list of operators that haven't been translated:

• TakeWhile , SkipWhile
• Reverse
• Last , LastOrDefault
• ElementAt , ElementAtOrDefault
• DefaultIfEmpty

Operators with No Translation

The following methods are not translated by LINQ to SQL. The most common reason is the difference between unordered multisets and sequences.

Operators

Rationale

...

ElementAt , ElementAtOrDefault

SQL queries operate on multisets, not on indexable sequences.

Answer 2

It is odd, in particular because Skip() is supported. Could you, for example, do:

var obj = source.Skip(index).First();

?