You've got 2 options:
1) linq query,
2) raw sql query.
1)
I'd suggest to split your query into 3 parts. See:
var artists = context.Artists
.Select(x => x)
.ToList()
.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
{
})
.ToList();
2)
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[
^]