How to return or display distinct values only from a specific column using EF Core?

Question

I have a database table Table 1 where I need to filter a specific column Grade Level which contains non-distinct or duplicate values and I'm trying to filter the results of that column to return only once or single and the rest will display empty.

I'm using Entity Framework Core to achieve the result using the code below but it will return a distinct row result.

What I need to achieve is if you to refer to my database table which is Table 1. You can see that under column Grade Level; it has duplicate values. I want to return the data from Table 1 into my table in the UI similar to Table 2 below.

In short, I don't want to display duplicated values from that column (Grade Level) instead the duplicate value should be empty but will still display the row.

Here's the code I currently used which return distinct row but not like the data from Table 2.

var teachingPersonnel = new List<TeachingViewModel>();

var teachingPersonnel2 = context.TeachingPersonnel
                                .AsNoTracking()
                                .Where(x => x.School_Id == School_Id)
                                .Select(x => new TeachingViewModel
                                             {
                                                 Id = x.Id,
                                                 School_Id = x.School_Id,
                                                 OrderId = x.OrderId,
                                                 Grade_Level = x.Grade_Level,
                                                 Name = x.Name,
                                                 Position = x.Position,
                                             })
                                .OrderBy(x => x.OrderId);

teachingPersonnel = teachingPersonnel2.AsNoTracking()
                                      .Select(p => p.Grade_Level)
                                      .Distinct()
                                      .Select(id => teachingPersonnel2.OrderBy(p => p.OrderId)
                                                                      .FirstOrDefault(p => p.Grade_Level == id))
                                      .OrderBy(x => x.OrderId)
                                      .ToList();

return teachingPersonnel;

How do I update my code to return results similar to Table 2?

Is this possible to achieve? Thanks

Answer 1

Presumably your UI hs something like this:

foreach(var t in GetTeachingPersonnel()){
  ...
  Console.Write(t.Grade);
  ...
}

Make it like this:

var lastGrade = null;
foreach(var t in GetTeachingPersonnel()){
  ...
  if(lastGrade != t.Grade){
    Console.WriteLine(t.Grade);
    lastGrade = t.Grade;
  }
  ...
}

This way if the grade for this row is different to last row, it prints, otherwise it doesn't. This way you don't have to chop up your "get the data" method- somewhere else you call that method you might need that row to have the grade info in it; this is more of a "row span"/data presentation type requirement that should be the remit of the ui

---

If you're desperate to have your data layer do this, you can adopt a similar approach; foreach over the List you have created and if this Grade is the same as the last Grade, make this Grade null/empty instead (and update lastGrade)

var lastGrade = null;
foreach(var t in teachingPersonnel){
  if(lastGrade == t.Grade){
    t.Grade = "";
    lastGrade = t.Grade;
  }
}