Load Records From EF Data Model in Custom/Predefined Order Using LINQ

Question

I have a comma delimited list of record id's:

 string = "2207,117,90,2168,120,118,113,112,17"

I need to be able to load the records those id's are associated with in the position that they are in that string.

Here is the code I am using to get my data using LINQ to EF with LINQ Methods:

 var styles = db.DataModel.Categories.Where(c => c.CategoryTypeID == 5 && c.Enabled).OrderBy(c => c.Name).ToList();

Is there anyway that the above query can be modified to get the records in the order that they are in the above list of record id's?

I am not sure how to do this. Any help/example would be greatly appreciated.

I am using LINQ Methods to EF 4.0 + C#

Thanks!

score 3 · Accepted Answer · answered Mar 28 '12 at 19:25

3

You can sort by the index in the input array after using AsEnumerable():

var input = "2207,117,90,2168,120,118,113,112,17".Split(',');
var recordIds = Array.ConvertAll(input, int.Parse);

var styles = db.DataModel
                .Categories
                .Where(c => c.CategoryTypeID == 5 && c.Enabled && recordIds.Contains(c.ID))
                .AsEnumerable()
                .OrderBy(c => Array.IndexOf(recordIds, c.ID))
                .ToList();

Above assumes you want to load all Category objects that have an ID that is contained in recordIds. Once they are retrieved you order them by their index in the array.

answered Mar 28 '12 at 19:25

BrokenGlass

158,293
28
286
335

Does `AsEnumerable()` cause the first part to be evaluated on the server? I always thought you had to do `ToList()` in order to force loading. – mellamokb Mar 28 '12 at 20:13
`AsEnumerable()` essentially will switch from an `IQueryable` that's in this case executed as SQL on the DB to an `IEnumerable` that's using Linq to Objects in memory - to enumerate all results you still do need `ToList()` – BrokenGlass Mar 28 '12 at 20:47

mellamokb · Answer 2 · 2012-03-28T19:27:14.363

0

You can use the comma-separated list filter hack:

...Where(c => ("," + myString + ",").IndexOf("," + c.CatgeoryID + ",") != -1)...

Using a similar technique, you can get them back in the same order:

...OrderBy (c => ("," + myString + ",").IndexOf("," + c.CategoryID + ","))...

edited Mar 28 '12 at 19:27

answered Mar 28 '12 at 19:19

mellamokb

56,094
12
110
136

score 0 · Answer 3 · answered Mar 28 '12 at 19:29

If you don't want to use Linq-to-objects you can do the following:

int[] ids =  "2207,117,90,2168,120,118,113,112,17".Split(',').Select(x => int.Parse(x)).ToArray();


var styles = from c in db.DataModel.Categories
             join i in ids on c.ID equals i
             where c.CategoryTypeID == 5 && c.Enabled
             select x;

Load Records From EF Data Model in Custom/Predefined Order Using LINQ

3 Answers3