CODEWITHSUNDEEP
c#linqsql-order-bycase
Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

How Order by Case in LINQ

I have this answer Entity framework OrderBy "CASE WHEN" but this only handle two options

var p = ctx.People.OrderBy(p => (p.IsQualityNetwork == 1 || p.IsEmployee == 1) ? 0 : 1)
                  .ThenBy(p => p.Name);

I have a text field and want rows appear on an specific order. In sql I would do:

ORDER BY CASE when name = "ca" then 1
              when name = "po" then 2
              when name = "pe" then 3 
              when name = "ps" then 4
              when name = "st" then 5
              when name = "mu" then 6
         END

Upvotes: 7

Views: 10496

Answers (1)

Joseph
Joseph

Reputation: 5160

How about creating a map of your sort order?

var sortOrder = new Dictionary<string,int>
{
  { "ca", 1 },
  { "po", 2 },
  { "pe", 3 },
  { "ps", 4 },
  { "st", 5 },
  { "mu", 6 }
};

var defaultOrder = sortOrder.Max(x => x.Value) + 1;

var sortedPeople = ctx
  .People
  .AsEnumerable()
  .OrderBy(p => (p.IsQualityNetwork == 1 || p.IsEmployee == 1) ? 0 : 1)
  .ThenBy(p => sortOrder.TryGetValue(p.Name, out var order) ? order : defaultOrder);

Upvotes: 13

Related Questions