MySql ORDER BY FIELD() in EntityFramework

Question

I have a list of numbers and need to select rows from DB table by that order. i was looking to implement ORDER BY FIELD() query in LinQ / lambda expressions with no luck

any ideas?

the code look like this:

 using (var db = new TimeTable.EntityFramework.TimeTableEntities())
        {
            List<int> list = new List<int>() { 2, 1, 4, 3 };
            var query = db.place_users_info.OrderBy(item => item.UniqueId);
        }

I need to order the rows by the list items

if it isn't that big amount of data , just enumerate and order after the data has been retrived — Thorarins, Sep 07 '16 at 06:09
Could be the same of this? http://stackoverflow.com/questions/39349413/how-to-get-records-from-tables-in-custom-order-of-ids/39357035#39364400 — bubi, Sep 07 '16 at 08:10

score 1 · Answer 1 · answered Sep 07 '16 at 06:27

if your rowcount is not that big, maybe you are looking for something like this (checks ommitted):

using (var db = new TimeTable.EntityFramework.TimeTableEntities())
        {
            List<int> list = new List<int>() { 2, 1, 4, 3 };
            var a = db.place_users_info.ToArray();
            var b = list.Select(x=>a[x]).ToList();
        }

score 1 · Answer 2 · answered Sep 07 '16 at 06:39

From what I understand you have (according to the example) 4 rows which you want to order by the number they have on the number list.

To do so use Zip to merge the lists by index (first item in numbers list will be with first item in the data list and so on)

using (var db = new TimeTable.EntityFramework.TimeTableEntities())
{ 
    List<int> list = new List<int>() { 2, 1, 4, 3 }; 
    var query = db.place_users_info.Zip(list, (f,s) => new { f,s })
                                   .OrderBy(item => item.s)
                                   .Select(item => item.f); 
}

score 0 · Answer 3 · answered Sep 07 '16 at 06:18

If you have the list, say:

var list<myobject> = <yourRepository>.<GetYourData>.ToList().OrderBy(o => o.<property>;

besides, in memory order by is probably faster then in DB.

If you would do:

var list<myobject> = <yourRepository>.<GetYourData>.OrderBy(o => o.<property>).ToList();

you would do the order by in the DB. (If your returns an IQueryable)

MySql ORDER BY FIELD() in EntityFramework

3 Answers3