ElementAt() doesn't work in Linq to SubSonic

Question

I have this query:

var iterator = criteria.binaryAssetBranchNodeIds.GetEnumerator();
iterator.MoveNext();
var binaryAssetStructures = from bas in db.BinaryAssetStructures
                              where bas.BinaryAssetStructureId == iterator.Current
                              select bas;

When I iterate over the binaryAssetStructureIds with a foreach loop no problems occur. When I try this

var binaryAssetStructure = binaryAssetStructures.ElementAt(0);

I get following error:

Unable to cast object of type 'System.Linq.Expressions.MethodCallExpression' to type 'SubSonic.Linq.Structure.ProjectionExpression'

First() for example does work... What am I missing here...

Answer 1

I don't know SubSonic at all, but FWIW a similar issue exists with the Entity Framework. In that case it boils down to the fact that there's no direct translation of ElementAt to SQL.

First() can be easily translated to SELECT TOP 1 FROM ... ORDER BY ..., but the same is not easily expressed for ElementAt.

You could argue that e.g. ElementAt(5) should be translated to SELECT TOP 5 FROM ... ORDER BY ... and then the first four elements simply discarded, but that doesn't work very well if you ask for ElementAt(100000).

In EF, you can partialle overcome this issue forcing the expression to be evaluated first, which can be done with calls to AsEnumerable, ToList or ToArray.

For example

var binaryAssetStructure = binaryAssetStructures.AsEnumerable().ElementAt(0);

I hope this helps although not explicitly directed at SubSonic.