IQueryable.ToList method messing up with T-SQL sorting order

Question

I'm using EF Code First and I have a Entity Framework (against an Oracle Database). For the sake of simplicity, I'll be resuming the scenario. Suppose I have table Products like this:

ProductName (varchar)
A
1
C  
2
B
3
4

Well, so I build a query using EF:

var query = Repo.Products.AsQueryable().OrderBy(p => p.ProductName);

When I debug, I get the generated SQL and execute in oracle, everthing is ok, the result set is:

1
2
3
4
A
B
C

This is right (remember the field is varchar/string) given the order of precedence of characters.

Ok, now if I do the following:

var list = query.ToList();

The result is:

A
B
C
1
2
3
4

This is wrong, and different from Oracle result set. If I call ToList() before OrderBy(), the result is ordered correctly, but I do not want to call ToList() before to avoid fetching all the records before filtering.

I'm really getting crazy with this. Anyone can help?

Answer 1

I got it!

The problem was NLS_SORT variable in Oracle. The weird thing was that PL/SQL Manager Developer shows the "right" order, with numbers first, but the reason for the different behaviour is because it changes the NLS_SORT variable session, and Entity Framework doesn't.

I just set NLS_SORT to 'BINARY' before executing any query and that was it!

Solved!

Thanks.