different interpretation of the dot in the order by C # linq and ORACLE

Question

With ORACLE

select * from (
select 'LM.CHARLET' c from dual
union
select 'L.MOURLIN' c from dual
union
select 'Y.LEFEUVRE' c from dual
union
select 'S.MIRO' c from dual
) order by c

RETURN

• LM.CHARLET
• L.MOURLIN
• S.MIRO
• Y.LEFEUVRE

But with C#

List<string> testLst = new List<string> { "LM.CHARLET", "L.MOURLIN", "Y.LEFEUVRE", "S.MIRO" };
List<string> orderedLst = testLst.OrderBy (p => p).ToList ();

RETURN

• L.MOURLIN
• LM.CHARLET
• S.MIRO
• Y.LEFEUVRE

does anyone have an explanation for this difference ?

Answer 1

In Oracle, the ordering depends on the Collation settings:

ALTER SESSION SET NLS_COMP=BINARY;
ALTER SESSION SET NLS_SORT=BINARY;

select *
from   (
  select 'LM.CHARLET' AS c from dual union
  select 'L.MOURLIN'  from dual union
  select 'Y.LEFEUVRE' from dual union
  select 'S.MIRO'     from dual
)
order by c

Outputs:

| C          |
| :--------- |
| L.MOURLIN  |
| LM.CHARLET |
| S.MIRO     |
| Y.LEFEUVRE |

and:

ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=FRENCH;

select *
from   (
  select 'LM.CHARLET' AS c from dual union
  select 'L.MOURLIN'  from dual union
  select 'Y.LEFEUVRE' from dual union
  select 'S.MIRO'     from dual
)
order by c

Outputs:

| C          |
| :--------- |
| LM.CHARLET |
| L.MOURLIN  |
| S.MIRO     |
| Y.LEFEUVRE |

db<>fiddle here

It appears that your Oracle session is using linguistic collation and sorting while C# is using the Default comparer. Change either Oracle's collation settings or the comparer you're using in C# so that the sorting uses the same method in both.

In Oracle, you could use the NLSSORT function in the ORDER BY to override the session defaults:

ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=FRENCH;

select *
from   (
  select 'LM.CHARLET' AS c from dual union
  select 'L.MOURLIN'  from dual union
  select 'Y.LEFEUVRE' from dual union
  select 'S.MIRO'     from dual
)
order by NLSSORT( c, 'NLS_SORT = BINARY' );

Outputs:

| C          |
| :--------- |
| L.MOURLIN  |
| LM.CHARLET |
| S.MIRO     |
| Y.LEFEUVRE |

db<>fiddle here