Accepted answer

I am not sure if you are looking for something like this but it's a bit cleaner, it's not split in 2 statements and you might find it helpful. I couldn't use a dbcontext so I used lists to make sure the syntax is correct.

var res = Shows.Join(Venues,
         show => show.VenueID,
         venue => venue.VenueID,
            (show, venue) => new { show, venue })
                    val =>,
                    showdate => showdate.ShowID,
                        (val, showDate) => new {, val.venue, showDates = showDate })
                            .GroupBy(u =>
                            .Select(grp => new
                                showId = grp.Key,
                                name = grp.FirstOrDefault()?.show.showName,
                                venue = grp.FirstOrDefault()?.venue.VenueName,
                                startDate = grp.Max(g => g.showDates.DateTime)


we need to now realation beetwen them one to one or one to many , but not too far from this answer.

 var GrouppedResult = Shows.Include(x=>x.Veneu).Include(x=>x.ShowDates)
    .Select(x=>///anything you want);


from show in Shows
join veneu in Veneu on veneu.VeneuId equals show.VeneuId
join showDates in ShowDates on showDates.ShowId=show.ShowID
group show by show.Id into grouppedShows
select new { ///what you want };

Related Articles