LINQ to Entities - sort by array

Question

I have the following code:

    public List<OversizeElement> GetOversizeRegulations(List<string> states)
    {
        var tmpList = (from i in _db.States
                       where states.Any(p=>i.Name == p)
                       select new
                       {
                           StateID = i.ID,
                           StateName = i.Name,
                           StateShortName = i.ShortName
                       }).ToList();

so, I select additional information for all states from 'states' variable. It works, but I need to get the same order, as in the 'states' variable. How to sort it? It requires IComparer object, but I can't imagine how to write it in my case

How is `states` ordered ? – Matias Cicero Feb 16 '17 at 14:56 — Matias Cicero, Feb 16 '17 at 14:56
is the order from states diffent then tmplist? – lordkain Feb 16 '17 at 14:57 — lordkain, Feb 16 '17 at 14:57

George Vovos · Accepted Answer · 2017-02-16T15:10:03.160

2

If you want the original ordering you can do something like:

public List<Destination> GetOversizeRegulations(List<string> states)
{

        var tmpDictionary = (from i in _db.States
                             where states.Contains(i.Name)
                             select new
                                      {
                                        StateID = i.Id,
                                        StateName = i.Name,
                                        StateShortName = i.ShartName
                                      }
                              ).ToDictionary(k => k.StateName, k => k);

        var result = states
                    .Where(m=>tmpDictionary.ContainsKey(m))
                    .Select(m => tmpDictionary[m]).ToList();


 }

edited Feb 16 '17 at 15:10

answered Feb 16 '17 at 15:02

George Vovos

7,563
2
22
45

Where does `_db.Destinations` come from? – Matias Cicero Feb 16 '17 at 15:03
@MatiasCicero Sorry,this is your Table (States) .I haven't compiled it,I think there aren't any other syntax errors.. – George Vovos Feb 16 '17 at 15:04
@MatiasCicero I just added the final Where in case state now not exist in the db – George Vovos Feb 16 '17 at 15:11

score 0 · Answer 2 · answered Feb 16 '17 at 15:00

Enumerate states instead of _db.States:

var tmpList = states
              .Select(s => _db.States.SingleOrDefault(st => st.Name == s))
              .Where(s => s != null)
              .Select(new
              {
                  StateID = s.ID,
                  StateName = s.Name,
                  StateShortName = s.ShortName
              });

score 0 · Answer 3 · answered Feb 16 '17 at 15:01

You can use the IndexOf function within your OrderBy function. Assuming you have an id field of some sort in both lists that are identical. Let's say in this instance that id is called StateId:

var tmpList = (from i in _db.States
                   where states.Any(p=>i.Name == p)
                   select new
                   {
                       StateID = i.ID,
                       StateName = i.Name,
                       StateShortName = i.ShortName
                   }).OrderBy(s => states.Select(x => x.StateId).IndexOf(s.StateId)).ToList();

LINQ to Entities - sort by array

3 Answers3