Count, order desc and select top 4 values for ASP.NET

Question

I am trying to create a table that shows the top 4 nationalities in my database for my .NET application. I can do it using SQL following this.

This is the result in sql

However, what I need for my application is

I have tried using LINQ as well as var in the View but unable to get it. I saw this and tried following but could not quite understand

Employee Model

    public class employees
    {
       public int eeID { get; set; }
       public string Name { get; set; }       
       public string Gender { get; set; }
       public string Nationality { get; set; }
    }

Answer 1

You can do the below things

1. Store the Grouped Nationalities sorted as per their counts
2. Get the top results, use Enumerable.Take
3. Get the count of the other Nationalities which should not include the top records, For that, you can use Enumerable.Skip
4. Concat result from points 2 and 3.
5. Pass the final output to the view.

Sample Code. Please refer to this link for working code

// Your Logic
var topRecordsCount = 4;
var groupedResult = list
                    .GroupBy(x => x.Nationality)
                    .OrderByDescending(x => x.Count());
    
var topRecords = groupedResult
                .Take(topRecordsCount)
                .Select(x => new FinalResult {Nationality = x.Key, Total = x.Count()}).ToList();
var othersCount = groupedResult.Skip(topRecordsCount).Select(x => x.Count()).Sum();
var othersRecord = new FinalResult { Nationality = "Others", Total = othersCount};

topRecords.Add(othersRecord);
foreach(var top in topRecords)
    Console.WriteLine(top.Nationality + " - " + top.Total);

---

// model to store the final output
public class FinalResult {
     public string Nationality { get; set; }
     public int Total { get; set; }
}