score:2

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 })
                .Join(ShowDates,
                    val => val.show.ShowID,
                    showdate => showdate.ShowID,
                        (val, showDate) => new { val.show, val.venue, showDates = showDate })
                            .GroupBy(u => u.show.ShowID)
                            .Select(grp => new
                            {
                                showId = grp.Key,
                                name = grp.FirstOrDefault()?.show.showName,
                                venue = grp.FirstOrDefault()?.venue.VenueName,
                                startDate = grp.Max(g => g.showDates.DateTime)
                            });

score:0

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)
    .Where(x=>x.Veneu.Any()&&x.ShowDates.Any())
    .GroupBy(x=>x.ShowId)
    .Select(x=>///anything you want);

or

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