Conditional Ordering By Value in SQL expression wont effect the LINQ Results using SQLite.Net-PCL

user_2036103 0
Viewed 80

pure sql (in sqlite) is this:

select * from Employees order by case WorkDay
         WHEN 'MON' then 0
         WHEN 'TUE' then 1
         WHEN 'WED' then 2
         WHEN 'THU' then 3 
         WHEN 'FRI' then 4 END

result in a sql editor is okay and as expected

id| FullName   | Department| WorkDay
7   Frank Bone   C Block     MON
8   Frank Bone   D Block     TUE
6   Frank Bone   A Block     FRI
9   Frank Bone   E Block     FRI

and C# code using SQLite.Net-PCL (Universal Windows Platform)

 var rows = 
     conn.Query<Employees>("select * from Employees order by case WorkDay
                            WHEN 'MON' then 0 
                            WHEN 'TUE' then 1
                            WHEN 'WED' then 2 
                            WHEN 'THU' then 3 
                            WHEN 'FRI' then 4 END").ToList();

      //rows.ForEach(x => { listBox1.Add(x); }); rows appears in wrong/weird order in debug mode. this line not important and not the cause of wrong order.

rows appears in a weird/wrong order

I got this rows in a wrong order. Do you know what causes this weird order?

id| FullName   | Department| WorkDay
6   Frank Bone   A Block     FRI
7   Frank Bone   C Block     MON
9   Frank Bone   E Block     FRI
8   Frank Bone   D Block     TUE

extra note: Model of table class (EF?)

public class Employees
{

    [PrimaryKey, AutoIncrement]
    public int id { get; set; }

    public string FullName { get; set; }

    [Indexed]
    public string Department { get; set; }

    public string WorkDay { get; set; }
}
windows-10-universal uwp-xaml sql-order-by sqlite sql
1 Answers

try this

define enum , and do rest as given below 

 [Flags] enum DayEnum { MON=0, TUE= 1, WED= 2, THU= 3, FRI = 4};

public class Employees
{
    [PrimaryKey, AutoIncrement]
    public int id { get; set; }
    public string FullName { get; set; }
    [Indexed]
    public string Department { get; set; }
    public string WorkDay { get; set    ; }
        public DayEnum DayNO{ get; set; }
    }

var employees = conn.Query<Employees>("select * from Employees");
var rows = (from e in employees
           select new Employee() {
                id  =e.id
                FullName   = e.FullName
                Department = e.Department
                WorkDay = e.WorkDay
                DayNO = (DayEnum ) Enum.Parse(typeof(DayEnum ), e.WorkDay)
            }).OrderBy(e=>e.DayNO );

As per you comment seems like problem with library , one work around is

add new property WorkDayNo

public class Employees
{
    [PrimaryKey, AutoIncrement]
    public int id { get; set; }
    public string FullName { get; set; }
    [Indexed]
    public string Department { get; set; }
    public string WorkDay { get; set; }
    public string WorkDayNo { get; set; }
}

do query like this 

var rows = 
     conn.Query<Employees>("select *, (case WorkDay
                                WHEN 'MON' then 0 
                                WHEN 'TUE' then 1
                                WHEN 'WED' then 2 
                                WHEN 'THU' then 3 
                                WHEN 'FRI' then 4 END) as WorkDayNo from Employees").OrderBy(e=> e.WorkDayNo).ToList();

Can you please try query as below 

select id, FullName, Department,WorkDay from
(select *, (case WorkDay
                            WHEN 'MON' then 0 
                            WHEN 'TUE' then 1
                            WHEN 'WED' then 2 
                            WHEN 'THU' then 3 
                            WHEN 'FRI' then 4 END) as workdayno from Employees) d  order by  workdayno
user_3144880
Related