CODEWITHSUNDEEP
c#linq-to-sql
webdad3
webdad3

Reputation: 9080

Bring back the Max Result for each item

In this list a teacher can have multiple SchoolEndYears. I would like to modify this query so I'm only grabbing the max for each teacher.

Code:

var    gridInfo = (from teacher in TeacherAccesses
                   join u in USERS
                   on teacher.ID equals u.ID
                   select new 
                   {
                      Name = u.LastName + ", " + u.FirstName,
                      ID = teacher.ID,
                      Flag = "Existing",
                      SchoolID = teacher.SchoolID,
                      status = teacher.TeacherAttr.IncludeinOfficialResults.Value,
                      SchoolEndYear = teacher.TeacherAttr.SchoolEndYear
                    }).OrderByDescending(x=>x.SchoolEndYear).OrderBy(x=>x.Name);

As you can see I tried doing the .OrderByDescending but when I tried to do a .FirstOrDefault it only brings back the 1st record in the group.

What I'm hoping to bring back is:

Upvotes: 0

Views: 53

Answers (2)

Andrei
Andrei

Reputation: 56688

What you need is to group records for the same teacher together, and then apply a Max function, that allows you to find the max element of the group:

var gridInfo = (from teacher in TeacherAccesses
                ...
               })
               .GroupBy(x => x.ID)
               .Select(g => g.Max(x => x.SchoolEndYear))
               .OrderBy(x => x.Name);

Upvotes: 1

Mark
Mark

Reputation: 8150

I think you want something like the following, although I'm not sure how efficient this is:

var gridInfo = (from teacher in TeacherAccesses
                join u in USERS
                on teacher.ID equals u.ID
                where teacher.Year = TeacherAccesses.Where(x => x.ID = teacher.ID)
                                         .Max(x => x.TeacherAttr.SchoolEndYear)
                select new 
                {
                   Name = u.LastName + ", " + u.FirstName,
                   ID = teacher.ID,
                   Flag = "Existing",
                   SchoolID = teacher.SchoolID,
                   status = teacher.TeacherAttr.IncludeinOfficialResults.Value,
                   SchoolEndYear = teacher.TeacherAttr.SchoolEndYear
                 });

Here's a simplified LINQPad example:

void Main()
{
    var TeacherAccesses = new List<Teacher>() {
        new Teacher() { ID = 1, Name = "Teacher-A", TeacherAttr = new Attr() { SchoolEndYear = 2012 } },
        new Teacher() { ID = 1, Name = "Teacher-A", TeacherAttr = new Attr() { SchoolEndYear = 2013 } },
        new Teacher() { ID = 1, Name = "Teacher-A", TeacherAttr = new Attr() { SchoolEndYear = 2014 } },
        new Teacher() { ID = 2, Name = "Teacher-B", TeacherAttr = new Attr() { SchoolEndYear = 2012 } },
        new Teacher() { ID = 2, Name = "Teacher-B", TeacherAttr = new Attr() { SchoolEndYear = 2013 } },
        new Teacher() { ID = 3, Name = "Teacher-C", TeacherAttr = new Attr() { SchoolEndYear = 2012 } }
    };
    //TeacherAccesses.Dump();
    var latest =
        from teacher in TeacherAccesses
        where teacher.TeacherAttr.SchoolEndYear == TeacherAccesses.Where(x => x.ID == teacher.ID).Max(x => x.TeacherAttr.SchoolEndYear)
        select new {
            Name = teacher.Name,
            Year = teacher.TeacherAttr.SchoolEndYear
        };
    latest.Dump();
}

// Define other methods and classes here
public class Teacher
{
    public int ID { get; set; }
    public string Name { get; set; }
    public Attr TeacherAttr { get; set; }
}

public class Attr
{
    public int SchoolEndYear { get; set; }
}

Results:

Teacher-A    2014
Teacher-B    2013
Teacher-C    2012

Upvotes: 1

Related Questions