C# GroupBy Trouble

Question

As of now, I am trying to create a list that groups based on certain criteria and then display that list in the view.

I have two database tables and one is an association table.

First Table

public partial class InitialTraining
{
    public InitialTraining()
    {
        InitialTrainingAssociations = new HashSet<InitialTrainingAssociation>();
    }
    public int Id { get; set; }

    [ForeignKey("MedicInfo")]
    public int TfoId { get; set; }

    [ForeignKey("InstructorInfo")]
    public int? InstructorId { get; set; }

    [ForeignKey("PilotInfo")]
    public int? PilotId { get; set; }

    public DateTime DateTakenInitial { get; set; }

    public decimal FlightTime { get; set; }

    public bool Active { get; set; }

    [StringLength(2000)]
    public string Narrative { get; set; }

    [Required]
    [StringLength(20)]
    public string TrainingType { get; set; }

    [ForeignKey("CodePhase")]
    public int PhaseId { get; set; }

    [ForeignKey("PhaseTrainingType")]
    public int PhaseTrainingTypeId { get; set; }
    public string EnteredBy { get; set; }
    public DateTime? EnteredDate { get; set; }

    public virtual MedicInfo MedicInfo { get; set; }
    public virtual MedicInfo InstructorInfo { get; set; }
    public virtual MedicInfo PilotInfo { get; set; }
    public virtual Code_Phase CodePhase { get; set; }
    public virtual Code_PhaseTrainingType PhaseTrainingType { get; set; }
    public virtual ICollection<InitialTrainingAssociation> InitialTrainingAssociations { get; set; }
}

Second Table (Association Table)

public class InitialTrainingAssociation
{
    public int Id { get; set; }
    [ForeignKey("InitialTraining")]
    public int InitialTrainingId { get; set; }
    [ForeignKey("CodePerformanceAnchor")]
    public int? PerformanceAnchorId { get; set; }
    [ForeignKey("GradingSystem")]
    public int? GradingSystemId { get; set; }
    public virtual AviationMedicTraining.CodePerformanceAnchor CodePerformanceAnchor { get; set; }
    public virtual InitialTraining InitialTraining { get; set; }
    public virtual GradingSystem GradingSystem { get; set; }
}

---

Here is my GroupBy in C#.

// get list of initial training record ids for statistics
var lstInitialTrainings = db.InitialTrainings.Where(x => x.TfoId == medicId && x.Active).Select(x => x.Id).ToList();

// get list of initial training performance anchors associated with initial training records
var lstPerformanceAnchors = db.InitialTrainingAssociations
    .Where(x => lstInitialTrainings.Contains(x.InitialTrainingId)).GroupBy(t => t.PerformanceAnchorId)
    .Select(s => new MedicStatistic()
    {
        PerformanceAnchorName = db.CodePerformanceAnchor.FirstOrDefault(v => v.Id == s.Key).PerformanceAnchor,
        AnchorCount = s.Count()
    }).ToList();

---

My Goal

Obviously from my code I want to group by the performance anchor in the association table, but I need more information from the Initial Training table to include in my ViewModel MedicStatistic, but I am having trouble figuring out the best way to do it.

My overall goal is to be able to get the most recent time a performance anchor was completed from the Initial Training table.

Visual

Initial Training Table (not all fields were captured in snippet b/c they're not important for the purpose of this question)

Initial Training Association Table

---

What I expect

So, from the pictures provided above as you can see there are multiple 1's for performance anchor id's in the association table, but they each have different InitialTrainingId. So, this specific performance anchor has been done multiple times, but I need to get the most recent date from the Initial Training table. Also, I need to get the corresponding grade with the anchor from the Grading System table, based on the most recent date.

So, for the performance anchor that equals 1.. I would want the grade that corresponds to the InitialTrainingId of 17 because that record was the most recent time that the performance anchor of 1 was done.

If you have any questions please let me know.

Answer 1

You want the data grouped by CodePerformanceAnchor, so the most natural way to start the query is at its DbSet which immediately eliminates the necessity of grouping:

from pa in db.CodePerformanceAnchors
let mostRecentInitialTraining
    = pa.InitialTrainingAssociations
        .Select(ita => ita.InitialTraining)
        .OrderByDescending(tr => tr.DateTakenInitial)
        .FirstOrDefault()
select new 
{
    pa.PerformanceAnchor,
    mostRecentInitialTraining.DateTakenInitial,
    mostRecentInitialTraining. ...
    ...
    AnchorCount = pa.InitialTrainingAssociations.Count()
}

As you see, only navigation properties are used and the query as a whole is pretty straightforward. I assume that the PerformanceAchor class also has an InitialTrainingAssociations collection.

I can't guarantee that EF will be able to execute it entirely server-side though, that's always tricky with more complex LINQ queries.

Answer 2

I'm going to ignore the virtual properties in your InitialTrainingAssociation class, since you didn't mention anything about them and it's not immediately apparent to me whether they actually contain data, or why they are virtual.

It seems like IQueryable.Join is the easiest way to combine the data you want.

In the following example, we will start with the entries from the InitialTrainings table. We will then Join with the InitialTrainingAssociations table, which will result in a collection of paired InitialTraining and InitialTrainingAssociation objects.

var initialTrainingResults =
    // Start with the InitialTrainings data.
    db.InitialTrainings

    // Add association information.
    .Join(
        // The table we want to join with
        db.InitialTrainingAssociations,

        // Key selector for the outer type (the type of the collection
        // initiating the join, in this case InitialTraining)
        it => it.Id,

        // Key selector for the inner type (the type of the collection
        // being joined with, in this case InitialTrainingAssociation)
        ita => ita.InitialTrainingId,

        // Result selector. This defines how we store the joined data.
        // We store the results in an anonymous type, so that we can
        // use the intermediate data without having to declare a new class.
        (InitialTraining, InitialTrainingAssociation) =>
            new { InitialTraining, InitialTrainingAssociation }
    )

From here, we can add data from the PerformanceAnchors and GradingSystems tables, by performing more Joins. Each time we perform a Join, we will add a new entity to our anonymous type. The result will be a collection of anonymous types representing data we retrieved from the database.

    // Add performance anchor information.
    .Join(
        db.PerformanceAnchors,
        x => x.InitialTrainingAssociation.PerformanceAnchorId,
        pa => pa.Id,
        (x, PerformanceAnchor) =>
            new { x.InitialTrainingAssociation, x.InitialTraining, PerformanceAnchor }
    )

    // Add grading system information.
    .Join(
        db.GradingSystems,
        x => x.InitialTrainingAssociation.GradingSystemId,
        gs => gs.Id,
        // No need for InitialTrainingAssociation anymore, so we don't
        // include it in this final selector.
        (x, GradingSystem) =>
            new { x.InitialTraining, x.PerformanceAnchor, GradingSystem }
    );

(This was a verbose example to show how you can join all the tables together. You can use less Joins if you don't need to access all the data at once, and you can filter down the InitialTrainings collection that we start with if you know you only need to access certain pieces of data.)

At this point, initialTrainingResults is an IEnumerable containing one entry for each association between the InitialTrainings, PerformanceAnchors, and GradingSystems tables. Essentially, what we've done is taken all the InitialTrainingAssociations and expanded their Ids into actual objects.

To get the most recent set of data for each performance anchor:

var performanceAnchors = initialTrainingResults
    // Group by each unique Performance Anchor. Remember, the IEnumerable
    // we are operating on contains our anonymous type of combined Training,
    // Performance Anchor and Grading data.
    .GroupBy(x => x.PerformanceAnchor.Id)

    // Order each Performance Anchor group by the dates of its training,
    // and take the first one from each group
    .Select(g => g.OrderByDescending(x => x.InitialTraining.DateTakenInitial).First());

Answer 3

In the Select you can order the group result to get the most recent associated InitialTraining by DateTakenInitial, and from there get the desired data

//...omitted for brevity
.GroupBy(t => t.PerformanceAnchorId)
.Select(g => {

    var mostRecent = g.OrderByDescending(_ => _.InitialTraining.DateTakenInitial).First();
    // get the corresponding grade with the anchor from the Grading System table
    var gradeid = mostRecent.GradingSystemId;
    var gradingSystem = mostRecent.GradingSystem;

    //get the most recent date from the Initial Training
    var mostRecentDate = mostRecent.InitialTraining.DateTakenInitial

    //..get the desired values and assign to view model

    var model = new MedicStatistic {
        //Already have access to CodePerformanceAnchor
        PerformanceAnchorName = mostRecent.CodePerformanceAnchor.PerformanceAnchor
        AnchorCount = g.Count(),
        MostRecentlyCompleted = mostRecentDate,
    };

    return model;
});