Lookup for an Entity Column ID

Question

I have an Entity Framework Core project that uses generic repositories and UnitOfWork and is working as expected.

The database is one to many and related by IDs.

The RTCTrials entity contains a FK CourseID related to RTCCourses PK. When loading trials I am trying to get the course name in the datagrid and only achieved by using a union. Is this inefficient and a simpler approach. Ideally I would add a dropdownlist column populated with RTCCourses in the trials grid template and the CourseID in the trials table would select the correct id and show the ValueMember course name.

This is what the current method looks like:

using (var context = new RTCContext())
{
    var factory = new EntityFrameworkUnitOfWorkFactory(context);
    var unit = factory.Create();

    var festivals = unit.RTCFestivals.All().ToList();
    var trials = unit.RTCTrials.All().ToList();
    var courses = unit.RTCCourses.All().ToList();

    var trialcourses = trials.Join(courses, courses => courses.CourseID, trials => trials.CourseID, (trials, courses) => new
            {
                TrialID = trials.TrialID,
                FestivalID = trials.FestivalID,
                CourseID = trials.CourseID,
                Trial = trials.Trial,
                Course = courses.CourseName,
                TrialGrade = trials.TrialGrade,
                TrialDistance = trials.TrialDistance,
                TrialAge = trials.TrialAge,
                TrialHurdles = trials.TrialHurdles,
                TrialAllowances = trials.TrialAllowances,
                TrialMonth = trials.TrialMonth,
                TrialActualDate = trials.TrialActualDate,
                TrialActualTime = trials.TrialActualTime,
                TrialRaceCard = trials.TrialRaceCard,
                TrialQualifiers = trials.TrialQualifiers
            }).ToList();

    this.radGridViewFestivalDestinations.DataSource = festivals;
    this.radGridViewFestivalDestinations.Templates[0].DataSource = trialcourses;

    foreach (GridViewDataColumn column in radGridViewFestivalDestinations.MasterTemplate.Columns)
    {
        column.BestFit();
    }

    foreach (GridViewDataColumn column in radGridViewFestivalDestinations.Templates[0].Columns)
    {
        column.BestFit();
    }
}

RTCTrial Entity

public partial class RTCTrial {

    public RTCTrial()
    {
        this.RTCResults = new List<RTCResult>();
        this.RTCWeathers = new List<RTCWeather>();
        OnCreated();
    }

    public virtual int TrialID { get; set; }

    public virtual int FestivalID { get; set; }

    public virtual int CourseID { get; set; }

    public virtual string Trial { get; set; }

    public virtual string TrialGrade { get; set; }

    public virtual string TrialDistance { get; set; }

    public virtual string TrialAge { get; set; }

    public virtual int? TrialHurdles { get; set; }

    public virtual string TrialAllowances { get; set; }

    public virtual string TrialMonth { get; set; }

    public virtual DateTime? TrialActualDate { get; set; }

    public virtual TimeSpan? TrialActualTime { get; set; }

    public virtual string TrialRaceCard { get; set; }

    public virtual int TrialQualifiers { get; set; }

    public virtual RTCCourse RTCCourse { get; set; }

    public virtual RTCFestival RTCFestival { get; set; }

    public virtual IList<RTCResult> RTCResults { get; set; }

    public virtual IList<RTCWeather> RTCWeathers { get; set; }

    #region Extensibility Method Definitions

    partial void OnCreated();

    #endregion
}

RTCCourse Entity

public partial class RTCCourse {

    public RTCCourse()
    {
        this.RTCTrials = new List<RTCTrial>();
        OnCreated();
    }

    public virtual int CourseID { get; set; }

    public virtual string CourseName { get; set; }

    public virtual string CourseCountry { get; set; }

    public virtual string CourseDirection { get; set; }

    public virtual string CourseCharacteristics { get; set; }

    public virtual string CourseAlternateName { get; set; }

    public virtual double CourseLat { get; set; }

    public virtual double CourseLong { get; set; }

    public virtual IList<RTCTrial> RTCTrials { get; set; }

    #region Extensibility Method Definitions

    partial void OnCreated();

    #endregion
}

Regards, Neil

Answer 1

Suggestion would be on the returned courses you would want each course to have its associated trials. In the unit of work that returns all courses - possibly have an option to include them. Your dropdown would bind to each course and your grid would bind to the list of trials in the selected course.

    public IEnumerable<RTCCourse> All(bool includeTrials = false)
    {
        var q = context.RTCCourses;
        if (includeTrials)
        {
            q = q.Include(c => c.RTCTrials)//.ThenInclude(t => t.RTCResults)
            ;
        }
        return q.AsEnumerable(); // assuming that is the returned type
    }

That should allow your courses to have the list of trials set. Then there is no need to get all trials. And you can bind to courses (and list of trials within each) directly instead of doing the join and binding to the anonymous.

Of 'course' -- this is merely a suggestion ;)