CODEWITHSUNDEEP
sql-serverperformanceentity-frameworklinq
ison
ison

Reputation: 183

Select multiple rows in order based on list of Ids with LINQ

I have a list of ids. How can I select all rows whose id is in the list and preserve the order?

This is what I have:

var ids = new int[]{5, 1, 8, 2, 3};
var items = Db.Items.Where(x => ids.Contains(x.Id));

But it doesn't preserve the order.

I know I can sort items based on ids, but I want O(n) complexity.

This question is similar to: Select multiple records based on list of Id's with linq but I need to keep the rows in order

Upvotes: 4

Views: 6034

Answers (4)

Jakub Dąbek
Jakub Dąbek

Reputation: 1044

I'm not into queries and databases that much but you can do this quite quickly with just doing C# tricks

var ids = new int[]{5, 1, 8, 2, 3};
var dict = ids.Select((id, i) => new { Index = i, Id = id })
    .ToDictionary(x => x.Id, x => x.Index);
var items = Db.Items
    .Where(x => ids.Contains(x.Id))
    .OrderBy(x => dict[x.Id]);

I can't tell how it will get translated into a database query

I haven't tested it, but here'e a version without OrderBy, but less space-efficient (it might actually be slower):

var ids = new int[]{5, 1, 8, 2, 3};
var temp = Db.Items
.Where(x => ids.Contains(x.Id))
.ToLookup(x => x.Id);

var tempList = new List<IGrouping<int, Item>>();
for(int i = 0; i < ids.Length; i++)
{
    tempList.Add(temp[ids[i]]);
}

var items = tempList.SelectMany(x => x);

There is also another way - simply do a reverse join:

var ids = new int[]{5, 1, 8, 2, 3};
var items = from id in ids
            join item in Db.Items
            on id equals item.Id
            select item;

This will result in a query sorted by ids

Upvotes: 3

Juan G Carmona
Juan G Carmona

Reputation: 2208

I'd say that,

1st, you can get the items as you did before:

var ids = new int[]{5, 1, 8, 2, 3};
var items = Db.Items.Where(x => ids.Contains(x.Id));

and then you could do something like:

var orderedItems = new int[ids.Length()] // sorry, I'm codign in SO edit, not sure on the syntax
foreach(id in items)
{
var position = Array.IndexOf(items, id)
orderedITems[position] = id;
}

That should do what you asked (also could be simplified in a single line).

I hope it helps,

Juan

Upvotes: 1

Mathieu Renda
Mathieu Renda

Reputation: 15346

What about projecting into an intermediate type to preserve the original index, building a union, and sorting over the indexes?

class ItemWithIndex
{
    public int Index { get; set; }
    public Item Item { get; set; }
}

class Item
{
    public int Id { get; set; }
}

int[] ids = { 5, 1, 8, 2, 3 };

IQueryable<ItemWithIndex> query = null;

for(int index = 0; index < ids.Length; index++)
{
    int currentIndex = index;
    int currentId = ids[index];

    IQueryable<ItemWithIndex> next = db.Items
        .Where(i => i.Id == currentId)
        .Select(i => new ItemWithIndex { Index = currentIndex, Item = i });

    query = query == null ? next : query.Concat(next);
}

ItemWithIndex[] items = query
    .OrderBy(i => i.Index)
    .ToArray();

Here is the generated query:

SELECT 
    [UnionAll4].[Id] AS [C1], 
    [UnionAll4].[C1] AS [C2], 
    [UnionAll4].[Id1] AS [C3]
    FROM  (SELECT 
        [Extent1].[Id] AS [Id], 
        @p__linq__1 AS [C1], 
        [Extent1].[Id] AS [Id1]
        FROM [dbo].[Items] AS [Extent1]
        WHERE [Extent1].[Id] = @p__linq__0
    UNION ALL
        SELECT 
        [Extent2].[Id] AS [Id], 
        @p__linq__3 AS [C1], 
        [Extent2].[Id] AS [Id1]
        FROM [dbo].[Items] AS [Extent2]
        WHERE [Extent2].[Id] = @p__linq__2
    UNION ALL
        SELECT 
        [Extent3].[Id] AS [Id], 
        @p__linq__5 AS [C1], 
        [Extent3].[Id] AS [Id1]
        FROM [dbo].[Items] AS [Extent3]
        WHERE [Extent3].[Id] = @p__linq__4
    UNION ALL
        SELECT 
        [Extent4].[Id] AS [Id], 
        @p__linq__7 AS [C1], 
        [Extent4].[Id] AS [Id1]
        FROM [dbo].[Items] AS [Extent4]
        WHERE [Extent4].[Id] = @p__linq__6
    UNION ALL
        SELECT 
        [Extent5].[Id] AS [Id], 
        @p__linq__9 AS [C1], 
        [Extent5].[Id] AS [Id1]
        FROM [dbo].[Items] AS [Extent5]
        WHERE [Extent5].[Id] = @p__linq__8) AS [UnionAll4]

Upvotes: 2

ison
ison

Reputation: 183

This is a possible solution:

var ids = new int[] {5, 1, 8, 2, 3};
var items = new List<Item>();
for (int i = 0; i < ids.Length; i++)
{
    items.Add(Db.Items.Find(ids[i]));
}

However, it performs N queries, so there should be a better way.

Upvotes: 0

Related Questions