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 into grouppedshows
select new { ///what you want };

Related Query