How join List<Class> with IEnumerable<Class>?

Question

I have the following classes.

  public class Course 
    {
        public string CourseNumber { get; set; }
        public List<PriceGroup> PriceGroups { get; set; }
    }


   public class PriceGroup
    {
        public int Id { get; set; }
        public string CourseNumber { get; set; }
    }

I have a service that fetches data from the database. Courses is an IEnumerable<Course> and priceGroups is a IEnumerable<PriceGroup>. As you can see in the Course class, a course has a List<PriceGroup>. I need to somehow add each PriceGroup in the IEnumerable<PriceGroup> to the list in each course in the IEnumerable<Course> if they match on the variable CourseNumber. So I will end up with a correct Course containing the right List<PriceGroup>'s

public async Task<IEnumerable<Course>> GetAllAsync()
        {
            //courses is an IEnumerable<Course>
            var courses = await _courseRepository.GetAllAsync();

            //priceGroups is an IEnumerable<PriceGroup>
            var priceGroups = await _coursePriceGroupRepository.GetAllAsync();     

            courses.GroupJoin(priceGroups, c => c.PriceGroups.Select(i => i.CourseNumber), pg => pg.CourseNumber, (c, pg) => new
            {

            });    
            return await courses;
        }

To do this I've tried to do a GroupJoin but without success. I have not finished the attempt as I am completely stuck mentally. Perhaps I am trying to do something that is not possible with a GroupJoin. Does anyone know a way to achieve what I need?

Answer 1

So you want all (or some) Courses, each Course with all (or some of) its PriceGroups.

There is a straightforware one-to-many relation betweeen Courses and PriceGroups: Every Course has zero or more PriceGroups, every PriceGroup belongs to exactly one Course, namely the Course that foreign key CourseNumber refers to.

You are right, whenever you want items with their sub-items you use GroupJoin.

public async Task<IEnumerable<Course>> GetAllAsync()
{
    IEnumerable<Course> courses = await _courseRepository.GetAllAsync();
    IEnumerable<PriceGroup> priceGroups = await _coursePriceGroupRepository.GetAllAsync();     

    var queryCoursesWithPriceGroups = courses.GroupJoin( // GroupJoin Courses
        priceGroups,                                     // with PriceGroups
        course => course.CourseNumber,                   // from every Course take primary key
        priceGroup => priceGroup.CourseNumber,           // from every PriceGroup take foreign key
        (course, priceGroupsOfThisCourse) => new        // from every Course with
        {                                               // its priceGroups make one new
             // Select the Course properties you plan to use:
             Id = course.Id,
             Name = course.Name,
             StartDate = course.StartDate,
             ...

             PriceGroups = priceGroupsOfThisCourse.Select(priceGroup => new
             {
                  // Select only the PriceGroup properties that you plan to use
                  Id = priceGroup.Id,
                  Name = priceGroup.Name,
                  ...

                  // not needed, you know the value:
                  // CourseId = priceGroup.CourseId
             })
             .ToList(),
       });
       // Note: this is a query. It is not executed yet!

       return await queryCoursesWithPriceGroups.ToListAsync();