I have an IQueryable with WHERE IN (ids). Can ToList() return the same order as ids?-CodePudding

My code:

var ids = new int[] { 16259,16238,16240,16243 };
var teamQuery = _teamRepository.Where(team => ids.Contains(team.ID));
var teams = teamQuery.ToList();

I have a IQueryable object teamQuery which forms a query

SELECT
    [Extent1].[ID] AS [ID],
    [Extent1].[Name] AS [Name],
    FROM [dbo].[Team] AS [Extent1]
    WHERE [Extent1].[ID] IN (16259, 16238, 16240, 16243)

Can I keep the same order in the generated List teams object during ToList() materialization as in the IN method (16259, 16238, 16240, 16243)?

Now the order of objects in teams is [16238, 16240, 16243, 16259]

CodePudding user response：

This is the SQL that you want to try and approximate:

SELECT
[Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
FROM [dbo].[Team] AS [Extent1]
WHERE [Extent1].[ID] IN (16259, 16238, 16240, 16243)
ORDER BY CASE [Extent1].ID 
             WHEN 16259 THEN 0
             WHEN 16238 THEN 1
             WHEN 16240 THEN 2
             WHEN 16243 THEN 3
             ELSE 4
         END;

I wouldn't recommend this as a generic solution, not unless your list of options was constrained to being very small:

var ids = new [] { 16259, 16238, 16240, 16243 };
var firstId = ids.First();
var teamQuery = _teamRepository.Where(team => ids.Contains(team.ID))
                               .OrderByDescending(t => t.Id == firstId);
foreach(var id in ids.Skip(1))
{
    teamQuery = query.ThenByDescending(t => t.Id == id);
}
var teams = teamQuery.ToList();

A more efficient solution is to sort the results after executing in the database by correlating against the original array indices:

var ids = new [] { 16259, 16238, 16240, 16243 };
var teamData = _teamRepository.Where(team => ids.Contains(team.ID)).ToList();
var teams = teamData.OrderBy(team => ids.IndexOf(team.ID)).Tolist();

To do it in pure SQL efficiently for a large list would involve first creating the ordered list as a table reference, then you could order by the position in that list, but the LINQ to SQL would not easily replicate the same process unless you define a specific User Defined Type in the schema.

You could also interpolate this type of query, or construct it as raw SQL, that provides you with more options but is less LINQish.

Generally we can sort data in these sorts of custom ways with more efficient code in C#. There are very few benefits to sorting on the database unless the list is obscenely large.