Entity Framework Query - Get objects in a specific order

Question

I have a list defining the identifiers of several objects in my database. I want to get a set of objects, all from one table that have these identifiers, and keep them in that exact order, and I want it to be executed as a single database query (and not "N").

For example, I have a list of identifiers {5, 3, 6, 9}, and I want to return a list of Customer objects with these identifiers and keep them in order {Customer (5, 'Bob'), Client (3, "JimBo") , Client (6, "Joe"), Client (9, "Jack")}.

The amount of data is quite small, and I do not mind re-sorting it after a database query. I could do all this in about 15 lines of clean code (including manual re-sorting), but I feel there should be a one- or two-line LINQ query to EF, which should make it easy.

Answer 1

I do not think that everything can be done in one request, but it is easy to do in two requests. One on the database and one in memory.

The first request will select only clients with the specified identifiers:

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

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

Answer 2

I believe that you will need to complete your order after receiving the results. I would probably handle 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 memory connection of the list of identifiers with the query result will preserve the order of identifiers.

Edit: From comment

my spidey feelings tingle when using Join to rely on saving a sorted order. It is interesting if the property Join the documentation was declared (if it cannot change in the future version of .NET, for example, for performance purposes).

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

Join preserves the order of the elements of the external and for each of these elements, the order of the matching elements of the internal.

Answer 3

Obviously, you can get a list of objects in which the identification list contains an easily visible identifier. As for orderby, I can’t think of it based on a list of identifiers in a single request. However, if you have a logical way to indicate how the original identifiers were ordered (if not random), you can create an equality comparison function, as shown here

Answer 4

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

IDs may be another request to get identifiers from another place, but order them as you wish. Note that this is Linqpad code, so the .Dump () method.