Linq-to-Entities contains and order by-CodePudding

I use EF Core and I want to select only the IDs I need, as I would do it, I use an In SQL expression. How can I get the result in the same order as the Ids in the array? And fill OrderNum value in result Dto?

public IEnumerable<ResultDto> Foo()
{
    int[] validIds = { 100, 2, 3, 4, 5, 6, 8, 13, 14, 16, 22 };

    // Without the required sorting
    var query = dc.LeaveRequests.Where(x => validIds.Contains(x.Id));

   ...
}

class Model
{
     public int Id { get; set; }
     public string Name { get; set; }
}

class ResultDto
{
     public int Id { get; set; }
     public string Name { get; set; }
     public int OrderNum { get; set; }
}

CodePudding user response：

Assuming that the valid ids may be provided in another order, you could order by the index position of the id in the validIds (using a list instead of an array) and map the index position of the result to the OrderNum property:

var query = dc.LeaveRequests
    .Where(x => validIds.Contains(x.Id))
    .OrderBy(x => validIds.IndexOf(x.Id))
    .Select((x, i) => new ResultDto
    {
        Id = x.Id,
        Name = x.Name,
        OrderNum = i
    });

CodePudding user response：

Try OrderBy if you don't have more requirements.

var query = dc.LeaveRequests
     .Where(x => validIds.Contains(x.Id))
     .OrderBy(x => validIds.IndexOf(x.Id))
     .Select(x => new OrderNum {
          Id = x.Id,
          Name = x.Name,
          OrderNum = //fill OrderNum here,

     })
     .AsEnumerable();

CodePudding user response：

I would create an index lookup dictionary with the ID as the key and the index as the value. You can then order the result by looking up the index in the dictionary in O(1) time. (using .IndexOf on the array would be an O(n) operation)

int[] validIds = { 100, 2, 3, 4, 5, 6, 8, 13, 14, 16, 22 };
var result = dc.LeaveRequests.Where(x => validIds.Contains(x.Id)).AsEnumerable();
    
var indexLookup = validIds.Select((v,i) => (v,i)).ToDictionary(x => x.v, x => x.i);
var sortedResult = result.OrderBy(x => indexLookup[x.Id]);

CodePudding user response：

var results = dc.LeaveRequests
    .Where(x => validIds.Contains(x.Id))
    .Select(x => new ResultDto
    {
        Id = x.Id,
        Name = x.FechaCreacion,
    })
    .AsEnumerable()
    .OrderBy(x => validIds.IndexOf(x.Id))
    .ToList();

for (int i = 0; i < results.Count; i  )
{
    results[i].OrderNum = i;
}