Entity Framework 4.0 with Sql Compact Edition 4.0 - Not Implemented Exception

Question

I have this LINQ query:

var children = DataContext.Entities.Nodes .Where(n => n.Parent.Name == node.Key) .OrderBy(n => n.SortOrder); foreach (var child in children) var childNode = CreateNode(child); 

When using SQL Server, everything is working fine. However, when using SqlCe, I get the following error:

 [SqlCeException (0x80004005): Not implemented] System.Data.SqlServerCe.SqlCeDataReader.ProcessResults(Int32 hr) +125 System.Data.SqlServerCe.SqlCeDataReader.IsEndOfRowset(Int32 hr) +131 System.Data.SqlServerCe.SqlCeDataReader.Move(DIRECTION direction) +376 System.Data.SqlServerCe.SqlCeDataReader.Read() +95 System.Data.Common.Internal.Materialization.Shaper`1.StoreRead() +44 [EntityCommandExecutionException: An error occurred while reading from the store provider data reader. See the inner exception for details.] System.Data.Common.Internal.Materialization.Shaper`1.StoreRead() +130 System.Data.Common.Internal.Materialization.SimpleEnumerator.MoveNext() +46 

Any idea what is going on here?

I even tried calling ToArray() before foreach , but that didn't help.

EDIT:

If I changed the request to:

  var children = DataContext.Entities.Nodes .Where(n => n.Parent.Name == node.Key) .ToArray() .OrderBy(n => n.SortOrder); 

It works ... why?

EDIT 2:. By the way, the Parent navigator points to the same table, so each Node can have a {0..1} parent Node .

Answer 1

The fact that the request in your editing section shows that the problem is in the OrderBy(n => n.SortOrder) , because only this part of the request ...

 DataContext.Entities.Nodes .Where(n => n.Parent.Name == node.Key) 

... is actually running on the server. By calling .ToArray() , you force the request, and a (unsorted) list is loaded into memory. The following OrderBy defines the query in this list (which is more IEnumerable , not IQueryable ). This second query will be executed in memory on this list, and EF or SqlCe will not participate in this sort.

But, as a rule, SqlCe supports OrderBy, so the question remains why the first request throws an exception.

What type of Node.SortOrder are you trying to sort? Is it null or, for example, some kind of “exotic” type that SqlCe cannot sort by?