TSQL OrderBy vs LINQ OrderBy Madness for UniqueIdentifier
After several hours of trying to figure out why some of the code was out of sync, I came to the realization that TSQL OrderBy is very different from Linq OrderBy for strings. So naturally I had to find a way to make sure the statement from tsql returned the same order as linq, so I used uniqueidentifier (guid) as my main one, but it didn't seem to be there.
Here is the order using Linq (using Guid type (UniqueID) for property)
var listings = validListings.Select(x => x.TempListing)
.OrderBy(x => x.UniqueID)
.ToList();
TSQL seems to use a uniqueidentifier binary value for comparison, while LINQ uses a string value even if the string is a GUID
LINQ RESULTS (printed) (first few)
00460400-a41d-465d-83c5-225f697e7bb5 015bef8d-5fa3-4c03-8d05-bfecf74b36b9 0202b433-4748-4660-97a1-94d119209aa6 03f34eb0-45cd-4586-b7d2-6e337b441c43 05e41d20-be24-4f4f-b098-574744dd84f0 0767e5d5-afba-49ab-a047-c9f509c80d3a 08f87ba1-8aa8-48a6-8f98-c3b4c6511b76 0b4157c4-7bdc-4e98-a00c-9259af754844 0bd194d0-fb66-4a69-9718-2128594ff9b0 0cda256a-7632-47de-b867-a2bc46382881 0d36f81a-ca37-446e-a325-87b46ef5b8d3 0d89fd26-4204-4d0a-b187-73a36536a848 0e345ca9-3d5d-43ed-aa75-fbd356f94535 0e767557-87ea-4c31-9f54-75d354a87d0f 0f62fc97-85b0-4611-b3e5-0c5ae4f12a18 1020d776-9810-4122-a9ef-3c527f21970c
TSQL FIRST FEW
9C5231CE-01DE-4A20-A4C9-001AD0D28512 3D52B47C-B29C-44A8-99F9-00AA660610A8 FDA7B67D-AEDB-4644-96E4-0147A0EEC29D C8C7B677-76EB-41D3-B11C-020B9047EB00 487FF542-599B-42D4-BCE3-02C5D569E509 BDAA48DB-60AF-4A36-AFDB-02FA706EE87F 2CD9D59C-C2B5-433C-9FD1-0444F0384BB3 D44695A3-6FEF-4842-BFCB-048C110FA178 28FF051C-38A7-424F-B657-0698452DFE36 D9320EC6-64CD-4C26-8C5C-088C04E22AD7 D9F7FDC1-16D6-4C3A-B117-0908A234DF95 7DB09D09-F10B-4F33-9390-09211F9B2958 D970EE98-B575-4E73-BBAC-0981D6DC1682 9B05CDD9-2D85-486B-BC6B-0BA7E44F6021 539D22ED-FF2A-4376-A650-0BFE184C0E26 0F62FC97-85B0-4611-B3E5-0C5AE4F12A18 5D8EF134-0DC2-4B32-9F02-0C65940C1BCF
How can I get them to return the same result?
source to share
As you discovered, C # ( System.Guid
) and SQL Server use different algorithms for sorting GUIDs .
If you want SQL Server to sort the GUID in a way System.Guid
and then convert it to VARCHAR.
If you want to sort Guid
in C # like SQL does, convert the values Guid
to System.Data.SqlGuid
:
var listings = validListings.Select(x =>x.TempListing)
.OrderBy(x=> new SqlGuid(x.UniqueID)).ToList();
However, GUID is not the right datatype for the column being sorted - I would switch to the column IDENTITY
if possible.
source to share