How to sort in .NET Same as sorting SQL Server?

Question

I did TDD against some existing stored procedures. They return XML, so I use LINQ to XML.

I am currently working on a test that will prove that the data is sorted correctly. The test passes through XML and creates an IEnumerable of an anonymous type containing three columns to be sorted. From this, he creates a second IEnumerable, sorting the first:

var sortedColumns = from g in columns orderby g.ColumnA ascending, g.ColumnB ascending, g.ColumnC ascending select g; 

Finally, he claims that sorted columns are the same as unsorted ones using SequenceEquals.

The problem occurs when the database sort is different from the current sort. In particular, .NET places “W-” before “Wa” in column B.

Is there a way to sort in the same order as SQL Server random sort? If not, how will I sort in the same order as SQL_Latin1_General_CP1_CI_AS?

Answer 1

If this is a Windows sort, then it comes down to setting the appropriate locale and sort order, as in the sort names in the order that. A clean world follows the current user interface culture.

If this is an SQL collation, then it is a little more complicated. If you use VARCHAR, you have left the castle. There is hope for NVARCHAR. See Comparing SQL Mappings with Windows Mappings :

SQL sorting rules for sorting non-Unicode data are incompatible with any regular procedure that is provided by the Microsoft Windows operating system; however, sorting. Unicode data is compatible with a specific version of Windows sorting rules. Because comparison rules for data other than Unicode and Unicode are different, when you use SQL matching, you can see different results for comparing the same characters, depending on the underlying data type. For example, if you use the SQL collation "SQL_Latin1_General_CP1_CI_AS", the non-Unicode string 'ac' is smaller than the string 'ab' because the hyphen ("-") is sorted as a separate character that precedes "b". However, if you convert these strings to Unicode, and you do the same comparison, the Unicode string N'a-c 'is considered larger than N'ab' because Unicode collation "word sorting" is used, which ignores the hyphen.

For your purposes (TDD), I would recommend just avoiding suspicious characters such as a hyphen - or having two s one after the other (German ss problems) or capital I (problems with Turkish colliton), sh (problems with sorting by Spanish language) and etc. etc ... Stick to a small subset of characters that are sorted correctly, like a,A,b,B I'm serious.

Answer 2

Write a custom implementation of IComparer , which actually calls the SQL server to compare each of the two values ​​that it asked to compare.

It would be very slow, a little stupid, but it would do exactly what you ask.

Or, if you only care about accurately copying one particular sorting sequence, rather than random sorting, then write a one-time procedure to call SQL Server and create a collection of all characters in the order that the SQL server will place them and use to build a custom implementation of IComparer , which then matches the sequence of the SQL server without invoking the SQL server at run time.

Answer 3

LINQ-to-SQL does not support the direct use of sorts. If you want to achieve special sorting, you will have to locally receive the data, convert it to a list (or something else) and sort it yourself.

For more information, see the following two links:

• LINQ sorting doesn't work at all

• NET Framework Support and Notes

Answer 4

I removed this with use-own-icomparert-with-linq-orderby .

  MyComparer comparer = new MyComparer(); items = items.OrderByDescending( x => property.GetValue(x), comparer).ToList(); break; 

You can create a custom mapper for your needs. (Your results may require a specific type, not an anonymous type - I have not actually tested it).