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

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

CodePudding user response：

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;
  }
}

CodePudding user response：

For those who are looking the same output result as this scenario. I just missed the filter condition inside my for loop. Here's my updated code which resolve my previous issue. Code

 string curItem1 = null;
 string curItem2 = null;
using (var context = new SchoolsContext(options))
            {
                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).ToList();

                for (int item = 0; item < teachingPersonnel2.Count(); item  )           
                {
                    curItem1 = teachingPersonnel2[item].Grade_Level;                  
                    if (curItem1 != curItem2)
                    {
                        curItem2 = teachingPersonnel2[item].Grade_Level;
                        teachingPersonnel.Add(new TeachingViewModel
                        {
                            Id = teachingPersonnel2[item].Id,
                            School_Id = teachingPersonnel2[item].School_Id,
                            OrderId = teachingPersonnel2[item].OrderId,
                            Grade_Level = teachingPersonnel2[item].Grade_Level,
                            Name = teachingPersonnel2[item].Name,
                            Position = teachingPersonnel2[item].Position,
                        });
                      
                    }                   
                    else
                    {                       
                        teachingPersonnel.Add(new TeachingViewModel
                        {
                            Id = teachingPersonnel2[item].Id,
                            School_Id = teachingPersonnel2[item].School_Id,
                            OrderId = teachingPersonnel2[item].OrderId,
                            Grade_Level = string.Empty,
                            Name = teachingPersonnel2[item].Name,
                            Position = teachingPersonnel2[item].Position,
                        });
                    }
                   
                }
              
                return teachingPersonnel;
            }
        }