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

That block of code that prepares tp from tp2 looks very bizarre. I think the entire thing could be replaced with a GroupBy — Caius Jard
– Caius Jard, Commented Feb 5, 2022 at 7:11
If these persons really exist you should remove their names. — Gert Arnold
– Gert Arnold, Commented Feb 5, 2022 at 9:12
@GertArnold - Not really, just need to display them all but only once from the first display within that column. Just refer to Table 2. That's exactly what I need to achieve. I just updated my code. — timmack
– timmack, Commented Feb 5, 2022 at 9:29

Caius Jard · Accepted Answer · 2022-02-05 07:01:28Z

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

timmack · Accepted Answer · 2022-02-05 09:25:59Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related