Entity Framework Query - Get Objects in Specific Order

Question

I have a List specifying the IDs of a number of objects in my database. I want to get the set of objects, all from one table, that have those IDs and keep them in that exact order, and I want it to execute as one query against the DB (not 'N' queries).

For example, I have a list of IDs {5, 3, 6, 9}, and I want to get back a list of Customer objects with those IDs and keep them in order { Customer(5, 'Bob'), Customer(3, 'JimBo'), Customer(6, 'Joe'), Customer(9, 'Jack') }.

The amount of data is small enough that I don't mind having to re-sort it after the DB query. I could do all of this in about 15 lines of clean code (including re-sorting by hand), but I feel like there should be a one- or two-line LINQ query against EF that should do this easily.

Answer 1

I believe you'll need to perform your ordering after you get the results. I would probably tackle it like this.

var efQuery = /* your query here */.AsEnumerable(); // force evaluation 
var orderedResult = from id in idList // { 5, 3, 6, 9 }
                    join item in efQuery
                    on id equals item.Id
                    select item;

The in-memory join of the list of IDs to the query result will preserve the ordering of the IDs.

Edit: From Comment

my spidey senses tingle at using Join to rely on maintaining the sorted order. I wonder if that's a stated property of Join in documentation (if not it could change in some future version of .NET, such as for performance reasons).

I point you to http://msdn.microsoft.com/en-us/library/bb534675.aspx, the Remarks section

Join preserves the order of the elements of outer, and for each of these elements, the order of the matching elements of inner.

Answer 2

I dont think whole thing can be done in one query, but it is easy to do it in two queries. One on DB and one in memory.

First query will select only customers with specified Ids:

var customers = (from c in context.Customers
                where itemIds.Contains(c.Id)
                select c).AsEnumerable();

Second will order them according to your list:

var customers = from id in itemIds 
                join c in customers 
                on id equals c.Id
                select c;

var customersList = customers.ToList();