Maintaining order when selecting a static group from a table

Sa开发者_StackOverflow社区y I have a list static list of Ids in a particular order:

List<int> ordered = new List<int> {7,2,3,4,5};

And I would like to select the items out of the database maintaining that order.

The trivial:

 var posts = ( from p in Current.DB.Posts
                        where ordered.Contains(p.Id)
                        select p).ToList();

Comes back fast, but out of order.

How do I select these posts out of the db and maintain the order in an elegant and efficient way?

---

If you don't explicitly include an order-by clause, you only really have a set - any ordering is purely convenience and will usually happen to be on the clustered index - but IIRC this is not guaranteed (and I imagine things like the server choosing to use parallelism would throw this out)

Include an order-by; either at the DB, or at the client. Alternatively, throw the results into a dictionary:

var dict = Current.DB.Posts.Where(p => ordered.Contains(p.Id))
                  .ToDictionary(p => p.Id);

then you can pull out the one you need at will, ignoring order.

---

Here's a combination of Marc's answer and your answer:

var dict = Current.DB.Posts.Where(p => ordered.Contains(p.Id))
           .ToDictionary(p => p.Id);
return ordered.Select(id => dict[id]).ToList();

Since it omits the OrderBy step, I suspect that it will be a bit more efficient. It's certainly a bit prettier.

---

We ended up going with:

        var reverseIndex = ordered.Select((id, index) => new { Id = id, Index = index }).ToDictionary(pair => pair.Id, s => s.Index);

        model.Posts = Current.DB.Posts
            .Where(p => postIds.Contains(p.Id))
            .AsEnumerable()
            .OrderBy(p => reverseIndex[p.Id] )
            .ToList();

Ugly, yet reasonably efficient for large lists.

---

You could project the List<int> onto your list of posts.

var posts = ( from p in Current.DB.Posts 
                        where ordered.Contains(p.Id) 
                        select p).ToList(); 

return ordered.Select(o => posts.Single(post => post.Id == o)).ToList();

You could also do this at the database retrieval level but you'd be doing multiple select statements

ordered.Select(o => Current.DB.Posts.Single(post => post.Id == o)).ToList();