Group with LINQ and perform calculations

Question

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.BirthName, 
                                   a.DateOfBirth, 
                                   a.Songs.Count })
                             .ToList();

Posted 15-Jul-22 7:06am

User-15670402

Updated 17-Jul-22 8:55am

Add a Solution

Comments

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
  Artists.Id,
  Artists.Name,
  (SELECT COUNT(*) FROM ArtistSongs WHERE ArtistSongs.ArtistId = Artists.Id) SongsCount
FROM Artists

[Edit]
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.

2 solutions

Solution 1

Something like this should do the trick

C

 //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) =>
    new
    {
        ArtistName = artist.BirthName,
        ArtistDob = artist.DateOfBirth,
        //select the count of the song collection
        SongCount = songCollection.Select(s => s).Count()
    }).ToList();

Posted 15-Jul-22 10:24am

George Swan

Updated 15-Jul-22 11:03am

v2

Comments

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!

User-15670402 15-Jul-22 16:42pm

I'm getting an error:

System.InvalidOperationException: 'Processing of the LINQ expression 'DbSet<artists>
.GroupJoin(
outer: DbSet<songs>,
inner: artist => artist.ArtistId,
outerKeySelector: song => song.ArtistId,
innerKeySelector: (artist, songCollection) => new {
ArtistName = artist.BirthName,
ArtistDob = artist.DateOfBirth,
SongCount = songCollection
.Select(s => s)
.AsQueryable()
.Count()
})' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2022-07-17T08:55:00

You've got 2 options:
1) linq query,
2) raw sql query.

1)
I'd suggest to split your query into 3 parts. See:

C#

var artists = context.Artists
    .Select(x => x)
    .ToList() //this call is obligatory
    .Select(x => new
    {
        x.ArtistId,
        x.BirthName, 
        Age = DateTime.Today.Year - x.DateOfBirth.Year
    })
    .ToList();

var songs = context.Songs
    .GroupBy(x=> x.ArtistId)
    .Select(x=> new
    {
        x.ArtistId,
        Cnt = grp.Count()
    })
    .ToList();

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

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

SQL

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[^]