EF Linq group by ICollection of objects

Question

All,

I have a Linq query which fetches a list of events which works great. The problem I'm facing is that Events contains a ICollection of Artists called headliners and in the list I only want 1 event per,set of, Artist(s).

The query underneath works fine but: I require a top 10 of Events but only one Event per, set of, artist(s) for sorting the popularity of the artist with highest popularity can be used - not what i want.

 Context.Events
      .Where(x => x.Stage.Venue.AreaId == 1 && x.StartDateTimeUtc > DateTime.UtcNow && x.IsVerified)
      .OrderByDescending(x => x.Headliners.Max(y => y.Popularity))
      .Take(10)
      .ToList();

How can I adjust the query above that I only get one Event per Artist. I would need to do some sort of grouping to see if the event is performed by same (set of) Artist(s).

I'm looking into using the Artist's primary key but because it is an collection i cannot get it to work. I already tried the String.Join to get a single unique key for the headliners. This is however not support in entity framework.

Is this something that can (gracefully) be supported by Linq to EF?

The following SQL query does almost what i want expect that it won't work with multiple artist for the same event

 SELECT MAX(E.EventId), MAX(E.Name)
 FROM [dbo].[Events] E
 INNER JOIN [dbo].[Stages] S ON E.StageId = S.StageId
 INNER JOIN [dbo].[Venues] V ON S.VenueId = V.VenueId
 INNER JOIN [dbo].[Areas] A ON V.AreaId = A.AreaId
 INNER JOIN [dbo].[Headliners] H ON E.EventId = H.EventId
 INNER JOIN [dbo].[Artists] A2 ON A2.ArtistId = H.ArtistId
 WHERE E.IsVerified = 1 AND E.StartDateTimeUtc>GETDATE() AND  A.AreaId = 1
 GROUP BY A2.ArtistId, A2.Name, A2.EchoNestHotttnesss
 ORDER BY A2.EchoNestHotttnesss desc

Answer 1

Edit:

A pretty decent partial-LINQ lazily executed solution could be done in this way:

Firstly, get your query up to the ordered events based on popularity:

var evArtists = Context.Events
  .Where(x => x.Stage.Venue.AreaId == 1 && x.StartDateTimeUtc > DateTime.UtcNow && x.IsVerified)
  .OrderByDescending(x => x.Headliners.Max(y => y.Popularity));

Secondly, since a ICollection<Artist> can be unordered yet forming equal set, creates an intermediate function to check if two ICollection<Artist> are of identical members:

private bool areArtistsEqual(ICollection<Artist> arts1, ICollection<Artist> arts2) {
    return arts1.Count == arts2.Count && //have the same amount of artists
        arts1.Select(x => x.ArtistId)
        .Except(arts2.Select(y => y.ArtistId))
        .ToList().Count == 0; //when excepted, returns 0
}

Thirdly, use the above method to get the unique artists set in the query results, put the results in a List, and fill the List with the number of elements you need (say, 10 elements):

List<Events> topEvList = new List<Events>();
foreach (var ev in evArtists) {
    if (topEvList.Count == 0 || !topEvList.Any(te => areArtistsEqual(te.Headliners, ev.Headliners)))
        topEvList.Add(ev);
    if (topEvList.Count >= 10) //you have had enough events
        break;
}

Your result is in the topEvList.

Benefits:

The solution above is lazily executed and is also pretty decent in the sense that you can really break down the logic and check your execution piece by piece without breaking the performance.

Note that using the method above you do not need to refer to the evArtists (which is your large query) other than by its individual element ev. Using full-LINQ solution is possible, yet you may need to refer to evArtists.Any to find the duplicates set of artists (as you do have have memory of what sets has been chosen before) from the original ordered query itself (rather than by simply using its element (ev) one by one).

This is possible because you create a temporary memory topEvList which records what sets have been chosen before and only need to check if the next element (ev) is not among the already selected set of artists. Thus, you do not impair your performance by checking you set of artists against the whole ordered query every time.

---

Original:

You are almost there actually. What you further need are LINQ GroupBy and First, and put your Take(10) the last:

var query = Context.Events
  .Where(x => x.Stage.Venue.AreaId == 1 && x.StartDateTimeUtc > DateTime.UtcNow && x.IsVerified)
  .OrderByDescending(x => x.Headliners.Max(y => y.Popularity))
  .GroupBy(a => a.ArtistId) 
  .Select(e => e.First())
  .Take(10);

Since in by this query you have sorted your headliner artist:

.OrderByDescending(x => x.Headliners.Max(y => y.Popularity))

Then you only need to group your headliners by ArtistId:

.GroupBy(a => a.ArtistId) 

Thus each artist would be having one group. Then next, you only want the first element in the group (supposedly the most popular Event per Artist):

.Select(e => e.First())

And thus you will get all the most popular events per artist. And lastly, among these most popular events per artist, you only want to take 10 of them, thus:

.Take(10);

And you are done!

---

Answer 2

Challenging task, but here it is:

var availableEvents = db.MusicEvents.Where(e => 
     e.Stage.Venue.AreaId == 1 && e.StartDateTimeUtc > DateTime.UtcNow && e.IsVerified);

var topEvents =
    (from e1 in availableEvents
     where e1.Headliners.Any() &&
        !availableEvents.Any(e2 => e2.StartDateTimeUtc < e1.StartDateTimeUtc &&
            !e2.Headliners.Any(a2 => !e1.Headliners.Any(a1 => a1.Id == a2.Id)) &&
            !e1.Headliners.Any(a1 => !e2.Headliners.Any(a2 => a2.Id == a1.Id)))
     orderby e1.Headliners.Max(a => a.Popularity) descending
     select e1)
    .Take(10)
    .ToList();

The first subquery (availableEvents) is just for reusing the "availability" filter inside the main query. It does not execute separately.

The critical part is the condition

!availableEvents.Any(e2 => e2.StartDateTimeUtc < e1.StartDateTimeUtc &&
    !e2.Headliners.Any(a2 => !e1.Headliners.Any(a1 => a1.Id == a2.Id)) &&
    !e1.Headliners.Any(a1 => !e2.Headliners.Any(a2 => a2.Id == a1.Id)))

The idea is to exclude the later events for the same set of headliners. It should be read this way:

Exclude the event if there is another available event starting earlier and there is no at least one artist from either event that is not headliner of the other event (i.e. they have the same headliner set).