1

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..

jmesolomon
  • 563
  • 5
  • 15
  • Did you confirm that the query results were correctly ordered before your projection ? – Jonathan M Nov 09 '16 at 22:45
  • You could sort the list after you get it from the database http://stackoverflow.com/questions/3309188/how-to-sort-a-listt-by-a-property-in-the-object – Jaime Macias Nov 09 '16 at 22:47
  • Hi Jonathan, thanks!. Yes the query returns the expected result when you run it from the management studio – jmesolomon Nov 09 '16 at 22:48
  • @jaimeMacias thanks for that.. but I mean that kinda defeats the purpose of sorting it in the stored procedure isn't it? – jmesolomon Nov 09 '16 at 22:48
  • I'm talking in debug mode. If queryResult contains results ordered, but Students does not, then we know the problem lies in the Select call. Otherwise, it might be while reading the data. – Jonathan M Nov 09 '16 at 22:49
  • @jonathanM thanks, actually the queryResult is not even ordered accordingly – jmesolomon Nov 09 '16 at 22:56

1 Answers1

0

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

jmesolomon
  • 563
  • 5
  • 15