Entity Framework Query - Get Objects in Specific Order

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 l开发者_运维技巧ike there should be a one- or two-line LINQ query against EF that should do this easily.

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.

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

You can obviously get a list of objects where the ID list contains the ID being searched easily enough. As for the orderby, there is no way I can think of to do this based on a list of IDs in a single query. However, if you have a logical way of specifying how the original IDs were ordered (if not random) then you can create an equality comparer function as shown here

void Main()
{
    List<Data> data = new List<Data>();
    data.Add(new Data{Id =1, Name = "ABC1"});
    data.Add(new Data{Id =2, Name = "ABC2"});
    data.Add(new Data{Id =3, Name = "ABC3"});
    data.Add(new Data{Id =4, Name = "ABC4"});
    data.Add(new Data{Id =5, Name = "ABC5"});

    var result = from d in data
                let ids = new List<int>{3,4,5}
                where ids.Any(i=> i == d.Id)
                select d;
    result.Dump();              
}

// Define other methods and classes here
class Data
{
    public int Id{get;set;}
    public string Name{get;set;}
}

The ids could be yet another query to get the Ids from somewhere else, but order them as you want. Note that this is a Linqpad code, hence .Dump() method.