4

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 works 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's 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's going on here?

I even tried calling ToArray() before foreach, but that did not help.

EDIT:

If I change the query to:

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

It works... why?

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

Alfero Chingono
  • 2,663
  • 3
  • 33
  • 54
  • I was getting a similar error in my EF code, but I believe it was a deadlock exception on the transaction. I wonder if the ToArray before the OrderBy is just forcing more processing to take place in your C# code rather than in the DB, reducing the amount of processing time in the DB, and thus reducing the probability of getting a deadlock in the DB? – Andrew Garrison Jun 28 '11 at 19:58
  • @deverop Did you find a solution to this or escalate to Microsoft? I am seeing something similar without Entity Framework but it is harder to reproduce. Do you have a full example? – Travis Apr 28 '12 at 14:40
  • @Travis Nope I did not find the solution nor did I escalate. I ended up changing the query and breaking it up by calling `ToArray()` before sorting. – Alfero Chingono May 02 '12 at 18:53
  • @adaptive Bummer. Is the duplication scenario as simple as having a table with a parent relationship, and executing the where/orderby query above? Or was your mapping more complex than that? – Travis May 02 '12 at 18:59
  • @Travis The table has to have a cyclical reference to itself. In my case, the node can have a parent as well as children of type `Node`. – Alfero Chingono May 02 '12 at 19:02

1 Answers1

1

The fact that the query in your Edit section works indicates that the problem is in the OrderBy(n => n.SortOrder) clause, because only this part of the query ...

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

... is actually executed on the server. By calling .ToArray() you force the query to be executed and an (unsorted) list is loaded into memory. The following OrderBy defines a query on this list (which is an IEnumerable and not an IQueryable anymore). This second query will then be executed in memory on this list and EF or SqlCe is not involved in this sorting.

But generally SqlCe supports OrderBy, so the question remains why the first query throws an exception.

What type is Node.SortOrder you are trying to sort by? Is it nullable for instance or some "exotic" type which SqlCe is not able to sort by?

Slauma
  • 175,098
  • 59
  • 401
  • 420
  • `Node.SortOrder` is an `int`. Might I also add that switching `OrderBy` and `Where` does not make a difference. So, `DataContext.Entites.Nodes.OrderBy(n => n.SortOrder).Where(n => n.Parent.Name == node.Key)` throws the same exception. – Alfero Chingono Apr 14 '11 at 03:12
  • 1
    @deverop: Did you also test if it throws an exception if you *only sort* (`OrderBy` without `Where`) or if you *only filter* (`Where` without `OrderBy`)? This would also be interesting to narrow down the issue. – Slauma Apr 14 '11 at 10:16
  • Thanks for the suggestion. `OrderBy` by itself works, and the same applies to `Where` (as demonstrated in the question). But I just can not use them together. – Alfero Chingono Apr 14 '11 at 21:47
  • @deverop: I am afraid that I don't have an idea anymore. The query you are doing looks rather simple and I can't see why it could fail. I'd recommend to ask this question also directly at Microsoft to improve your chance for a solution - for instance here: http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/threads/ – Slauma Apr 14 '11 at 23:09