2

There seem to be lots of questions about SQL to LINQ, but I can't seem to find examples with joined tables and grouping; specifically with a need to get data from multiple tables.

Take this simple SQL:

SELECT
    s.showId, s.showName, v.venueName, Min(dateTime) startDate
FROM
    shows s
    INNER JOIN venues v ON s.venueId = v.venueId
    INNER JOIN showDates d ON s.showId = d.showId
GROUP BY
    s.showId

The best I can come up with is the following

var ungrouped = (
    from s in db.Shows
    join v in db.Venues on s.VenueId equals v.VenueId
    join d in db.ShowDates on s.ShowId equals d.ShowId
    select new { s, v, d }
).ToList();

var grouped = (
    from s in ungrouped
    group s by s.s.ShowId into grp
    select new
    {
        showId = grp.Key,
        name = (from g in grp select g.s.showName).FirstOrDefault(),
        venue = (from g in grp select g.v.VenueName).FirstOrDefault(),
        startDate = grp.Max(g => g.d.DateTime)
    }
);

This works but it feels messy. I don't like:

  • It being split into two statements
  • Having to repeatedly write (from g in grp select ...).FirstOrDefault()
  • Bits like s.s.ShowId
  • How its vastly more lines of code than the SQL

This example is a simple one, it only gets worse when I have 5+ tables to join and 10+ columns to select.

Question: Is this the best way to do this, and I should just accept it; or is there a better way to write this query?

Ben Robinson
  • 1,602
  • 3
  • 16
  • 30
  • Just curious, what is the actual reason you want to rewrite it by using LINQ to SQL? – Fabio Apr 30 '20 at 20:43
  • I'm converting a PHP app to .net MVC. The database is represented in classes and I understand Linq is the way to go - open to suggestions though! – Ben Robinson Apr 30 '20 at 21:36
  • First suggestion would be use original raw sql, especially when you migrating app to different platform, try not to change platform agnostic code to avoid possible mistakes. – Fabio Apr 30 '20 at 21:56
  • 1
    Are you moving to .net core 3? There were some big changes to how Linq GroupBy works which you may need to watch out for: https://stackoverflow.com/questions/58138556/client-side-groupby-is-not-supported – Sam May 01 '20 at 05:07

2 Answers2

2

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)
                            });
DKar
  • 464
  • 4
  • 17
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 };
Batuhan Kara
  • 263
  • 3
  • 11
  • There are both one-to-many (venues can have many shows, shows have many dates). Your answer doesn't work; the relationships aren't defined in code anywhere - should they be? – Ben Robinson Apr 30 '20 at 21:30
  • for using one you need to define virtual members. but how it does not work can you tell me more? – Batuhan Kara May 02 '20 at 18:23