CODEWITHSUNDEEP
c#mysql.netentity-frameworklinq
Moshe Stone
Moshe Stone

Reputation: 135

MySql ORDER BY FIELD() in EntityFramework

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

Upvotes: 0

Views: 554

Answers (3)

Gilad Green
Gilad Green

Reputation: 37299

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); 
}

Upvotes: 1

DarkSquirrel42
DarkSquirrel42

Reputation: 10257

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();
        }

Upvotes: 1

Roelant M
Roelant M

Reputation: 1706

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)

Upvotes: 0

Related Questions