CODEWITHSUNDEEP
c#entity-frameworklinq
I Love Stackoverflow
I Love Stackoverflow

Reputation: 6868

How to get records from tables in custom order of ids?

I have some Ids store in below variable:

List<int> Ids;

Now I want to get records based on above Ids but with same order as it is in above Ids.

For eg: Records are like this in database:

Employee:

Id
1
2
3
4
5

Now if Ids array holds Ids like this : 4,2,5,3,1 then I am trying to get records in this order order only:

Query:

  var data = context.Employee.Where(t => Ids.Contains(t.Id)).ToList();

But above query is giving me output like it is in table:

Id
1
2
3
4
5

Expected output :

Id
4
2
5
3
1

Update:I have already tried this below solution but as this is entity framework it didn't work out:

var data = context.Employee.Where(t => Ids.Contains(t.Id))
                    .OrderBy(d => Ids.IndexOf(d.Id)).ToList();

For above solution to make it working I have to add to list :

var data = context.Employee.Where(t => Ids.Contains(t.Id)).ToList()
                    .OrderBy(d => Ids.IndexOf(d.Id)).ToList();

But I don't want to load data in memory and then filter out my record.

Upvotes: 2

Views: 796

Answers (2)

bubi
bubi

Reputation: 6501

Without stored procedures you can use Union and ?: that are both canonical functions.
I can't immagine other ways.

?:
You can use it to assign a weigth to each id value then order by the weigth. Also, you have to generate ?: using dynamic linq.

What is the equivalent of "CASE WHEN THEN" (T-SQL) with Entity Framework?
Dynamically generate LINQ queries

Union
I think this is the more simple way to obtain it. In this case you can add a Where/Union for each Id.

EDIT 1
About using Union you can use code similar to this

IQueryable<Foo> query = context.Foos.AsQueryable();
List<int> Ids = new List<int>();
Ids.AddRange(new[] {3,2,1});
bool first = true;
foreach (int id in Ids)
{
    if (first)
    {
        query = query.Where(_ => _.FooId == id);
        first = false;
    }
    else
    {
        query = query.Union(context.Foos.Where(_ => _.FooId == id));
    }
}


var results = query.ToList();

This generate the followiong query

SELECT
[Distinct2].[C1] AS [C1]
FROM ( SELECT DISTINCT
        [UnionAll2].[C1] AS [C1]
        FROM  (SELECT
                [Distinct1].[C1] AS [C1]
                FROM ( SELECT DISTINCT
                        [UnionAll1].[FooId] AS [C1]
                        FROM  (SELECT
                                [Extent1].[FooId] AS [FooId]
                                FROM [Foos] AS [Extent1]
                                WHERE [Extent1].[FooId] = @p__linq__0
                        UNION ALL
                                SELECT
                                [Extent2].[FooId] AS [FooId]
                                FROM [Foos] AS [Extent2]
                                WHERE [Extent2].[FooId] = @p__linq__1) AS [UnionAll1]
                )  AS [Distinct1]
        UNION ALL
                SELECT
                [Extent3].[FooId] AS [FooId]
                FROM [Foos] AS [Extent3]
                WHERE [Extent3].[FooId] = @p__linq__2) AS [UnionAll2]
)  AS [Distinct2]
p__linq__0 = 3
p__linq__1 = 2
p__linq__2 = 1

EDIT 2
I think the best approach is in memory approach because it has the same network load, EF does not generate the ugly query that could not work on databases different from SQL Server and code is more readable. In your particular application could be that union/where is better. So, generally I would suggest you to try memory approach then, if you have [performance] issues, you can check if union/where is better.

Upvotes: 1

Maarten
Maarten

Reputation: 22955

Since the order in which the data is returned when you do not specify an ORDER BY is not determined, you have to add an ORDER BY to indicate how you want it sorted. Unfortunately you have to order based on objects/values in-memory, and cannot use that to order in your SQL query.

Therefore, the best you can do is to order in-memory once the data is retrieved from the database.

var data = context.Employee
    // Add a criteria that we only want the known ids
    .Where(t => Ids.Contains(t.Id))
    // Anything after this is done in-memory instead of by the database
    .AsEnumerable()
    // Sort the results, in-memory
    .OrderBy(d => Ids.IndexOf(d.Id))
    // Materialize into a list
    .ToList();

Upvotes: 2

Related Questions