The output sorted results are different between SQL statement vs LINQ to SQL

Question

I have a data table in my database and it has only one column which stores unique keys in string data type. The snippet code and its data as shown below

CREATE TABLE MyTable (    
   KEY_CODE varchar(5)
);

insert into MyTable(KEY_CODE) values('00');
insert into MyTable(KEY_CODE) values('-01');
insert into MyTable(KEY_CODE) values('01');
insert into MyTable(KEY_CODE) values('02');
insert into MyTable(KEY_CODE) values('03');
insert into MyTable(KEY_CODE) values('T');

Then I query all my KEY_CODE items and sort them by KEY_CODE ASC

select * from MyTable 
order by KEY_CODE

And here is the output result in MS SQL Server Management Studio. You can see that MS SSMS understand the key "-01" is smaller than "00" and "01".

Now, in my VB.NET project, I also have a data table with the same column and data. But the order of the KEY_CODE column is not matched with the one from MS SSMS. You can see the output data table in Debug mode that I mention in the captured image below. The "-01" key is not at the top of the result.

I am expecting the order result from LINQ to SQL should be the same as the one from MS SSMS. How can I achieve this?

Dim myTable As DataTable = New DataTable("MyTable")
Dim col As DataColumn = New DataColumn("KEY_CODE")
col.DataType = System.Type.GetType("System.String")

myTable.Columns.Add(col)

Dim row1 As DataRow = myTable.NewRow()
Dim row2 As DataRow = myTable.NewRow()
Dim row3 As DataRow = myTable.NewRow()
Dim row4 As DataRow = myTable.NewRow()
Dim row5 As DataRow = myTable.NewRow()
Dim row6 As DataRow = myTable.NewRow()

row1.Item("KEY_CODE") = "00"
row2.Item("KEY_CODE") = "-01"
row3.Item("KEY_CODE") = "01"
row4.Item("KEY_CODE") = "02"
row5.Item("KEY_CODE") = "03"
row6.Item("KEY_CODE") = "T"

myTable.Rows.Add(row1)
myTable.Rows.Add(row2)
myTable.Rows.Add(row3)
myTable.Rows.Add(row4)
myTable.Rows.Add(row5)
myTable.Rows.Add(row6)

Dim datarows As DataRow() = myTable.Select()
Dim output = (From row In datarows
              Order By "KEY_CODE ASC"
              Select row Distinct).CopyToDataTable()

user_1745365 · Answer

There's a lot of tutorials online for LINQ to SQL, but what it is NOT is injecting SQL syntax as a string into a LINQ query. Instead, you write code in what appears to be valid VB.NET code, with strong type checking and help from auto-complete, but it gets converted to an SQL statement by not actually running your code, but instead analyzing expression trees and using reflection to try to see what you want from the database and generates an SQL statement to get it. Because it's converted and run on the database engine, it has some subtle differences, such as nullable types acting slightly different in SQL than in Visual Basic, and you get the SQL behavior.

A query like this:

   From Item in new DataContext().Items 
   WHERE Item.Key_Code>0
   Order By Item.KEY_CODE 
   SELECT KeyCode=Item.KEY_CODE, Member=Item.Member

will generate SQL that nearly mirrors this query. What it won't do is bring the entire Items table into memory and execute the "where", "order by" or "Select" in Visual Basic.

Your code with

Dim output = (From row In datarows
              Order By "KEY_CODE ASC"
              Select row Distinct).CopyToDataTable()

is starting with a memory object (datarow), not a data context, so LINQ will be done in Visual Basic, which has a lot more power than LINQ TO SQL. It is constantly comparing "KEY_CODE ASC" as a string for every row and since all results are the same, it is unable to change the order.

Change it to something like this:

Dim output = (From row In MyTable.Rows.Cast(of DataRow)
              Order By row.item("KEY_CODE")
              Select row Distinct).CopyToDataTable()

or

Dim output = (From row as DataRow In MyTable
                  Order By row.item("KEY_CODE")
                  Select row Distinct).CopyToDataTable()

BTW, there's multiple ways of handling datarows in LINQ expressions, but due to the fact that DataRow collections existed before LINQ did, it's slightly quirky, so I showed two alternate ways of doing LINQ queries on a DataTable.

The output sorted results are different between SQL statement vs LINQ to SQL

1 Answers