jmesolomon jmesolomon - 27 days ago 7
SQL Question

Mapping SQL query result to DTO is not being ordered accordingly

Hi I am trying to populate an object from the SQL query result in an ordered way. However, my problem is that the DTO, when it is mapped/populated from the query result, is not ordered the way I have ordered it in my SQL query.

For the simplest example let's say I have 5 records of students in my database.

Select Id , Name, Age from Student Order by Name ASC
is my
GetStudents
stored procedure in the database

Then let us say this is the code that actually does the mapping

var queryResult= conn.GetStudents(); //this is just the conn that calls the stored procedure which is returned as a enumerable list

var myStudentRecord = new StudentsRecord{

TotalRecord = queryResult.ToList().Count(),

//this is not ordered according to the result from SP
Students = queryResult.Select(result => new Student
{
Id = result.Id,
Name = result.Name
Age = result.Age
}).ToList()
}


The problem that I have is The
StudentsRecord.Students
list of student objects are coming back in the same order (in this case ordered by Name ASC) regardless of changing the StoredProcedure order by ASC or DESC

StudentsRecord.Students
displays a list of


  1. Adam

  2. Arria

  3. Asia

  4. Ben

  5. Cruz



If I change my stored procedure as DESC
StudentsRecord.Students
ordering stays the same.

I hope I'm making sense here.

Really, I'm just wondering why this is happening. is this framework related? The result set from the query essentially is the same list with just different ordering ASC or DESC and obviously it works accordingly when you run the query form the management studio. But when it gets mapped to the object, the list does not reflect the ordering from the result..

Answer

Okay, so I finally have an answer to this question (it was human error ^^, ). We use Dapper and setup our project to use Command Query pattern (CQRS).

In my scenario I am extending from a legacy implementation of datatable with server-side paging. So I extended the stored procedure to accept two additional parameters SortBy and OrderBy since each column on the datatable can be sorted via ASC/DESC

My mistake was that the parameters were defined the other way SortBy was getting the value of OrderBy and vv. which was causing the problem! The mapping actually works as it is