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?

Is there nothing else happening here? I'm unable to reproduce this on my end. — Corey Adler, Jan 15 '15 at 20:44
Nothing else, every linq query to oracle database places letters before numbers using orderby, even if T-SQL generated is correct and tested ordering perfectly. Getting mad already. — Guilherme Fonseca, Jan 15 '15 at 20:47
It seems a problem right inside ToList, when fetching the results and converting them to List of objects. Is there any way to debug ToList method? — Guilherme Fonseca, Jan 15 '15 at 20:49

score 0 · Accepted Answer · answered Jan 18 '15 at 23:59

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.

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

1 Answers1