Click here to Skip to main content
15,748,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables, Artists and Songs. There are 12 artists each with multiple songs. With the query below, I have been able to return the artist name, birthdate, and a count of how many songs each artist has. However, I have not been able to find a way to do a GROUP BY the artist name. This query is returning the artist name 11 times if that artist has 11 songs.

I also need to figure out how to calculate the age by taking the current year and subtracting the DateOfBirth.

What I have tried:

dgvArtists.DataSource = (from a in context.Artists
                              join s in context.Songs
                                   on a.ArtistId equals s.ArtistId
                              select new { 
                                   a.Songs.Count })
Updated 17-Jul-22 8:55am
0x01AA 15-Jul-22 15:10pm    
I have no idea about linq, but maybe this gives you an idea. In SQL it would be this:

(SELECT COUNT(*) FROM ArtistSongs WHERE ArtistSongs.ArtistId = Artists.Id) SongsCount
FROM Artists

And I don't see the need of 'group by' ;)
User-15670402 15-Jul-22 15:40pm    
Unfortunately, I cannot use a SQL query for this.

Something like this should do the trick

 //join the Song collection to the Artists collection
  dgvArtists.DataSource = context.Artists.GroupJoin(context.Songs,
 //match the artist.ArtistId to the song.ArtistId.
    artist => artist.ArtistId,
    song => song.ArtistId,
//The  parameters passed to the new function are an Artist and a collection of Songs
 // where each song's ArtistId matches the artist.ArtistId
    (artist, songCollection) =>
        ArtistName = artist.BirthName,
        ArtistDob = artist.DateOfBirth,
        //select the count of the song collection
        SongCount = songCollection.Select(s => s).Count()

Share this answer
0x01AA 15-Jul-22 16:28pm    
I will never understand that syntax :-)
George Swan 15-Jul-22 17:05pm    
My apologies, I should have annotated the code to try to make it easier to understand. The edited version should be more helpful
0x01AA 16-Jul-22 7:03am    
Thank you very much. 5.
Does it not need to be songCollection => song.ArtistId, instead of song => song.ArtistId,? As you see, I still don't get that syntax ;)
George Swan 16-Jul-22 12:29pm    
song is just a descriptive name given to the parameter. The type is omitted. Instead of, Song song, Song is assumed and it is sufficient just to give a name to the parameter. It is not unusual just to use a letter like s=>s.ArtistId. I would recommend that you become familiar with lambda expressions. They are an integral part of Linq and one of the stellar features of the C# language. Visual Studio’s IntelliSense is a great help, it shows what types the required input parameters are and the type that the function expects to be returned.
0x01AA 17-Jul-22 6:59am    
Thank you very much again!
You've got 2 options:
1) linq query,
2) raw sql query.

I'd suggest to split your query into 3 parts. See:
var artists = context.Artists
    .Select(x => x)
    .ToList() //this call is obligatory
    .Select(x => new
        Age = DateTime.Today.Year - x.DateOfBirth.Year

var songs = context.Songs
    .GroupBy(x=> x.ArtistId)
    .Select(x=> new
        Cnt = grp.Count()

var result = (from a in artist
              join s in songs on a.ArtistId equals s.ArtistId
              select new 
                  // define fields/cols here

See: Raw SQL Queries - EF Core | Microsoft Docs[^]

SELECT A.ArtistId, A.BirthName, DateDiff(yy, GETDATE(), A.DateOfBirth) AS Age, COUNT(*) AS SongsCount
FROM Artists AS A INNER JOIN ArtistSongs  AS S
  ON S.ArtistId = A.ArtistId
GROUP BY A.ArtistId, A.BirthName, DateDiff(yy, A.DateOfBirth, GETDATE()) 

More: DATEDIFF (Transact-SQL) - SQL Server | Microsoft Docs[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900