How to create group clause in Entity Framework Core

Question

I am trying to query 2 tables, Club and LinkClubUser. I want to retrieve the last club that a User is assigned from the last Date in the LinkclubUser.

I have the right SQL query:

select top 1 
    max(A.DataInscricao), 
    A.Nome, A.NomeNaCamisola, A.NumNaCamisola, A.Posicao, A.Situacao  
from 
    Clube as A 
inner join 
    LinkClubeUser as B on a.Id = b.IdClube
where 
    b.IdUser ='9faea9f3-28d7-4e34-8572-3102726d3c75'
group by 
    A.Nome, A.NomeNaCamisola, A.NumNaCamisola, A.Posicao, A.Situacao

I got the right row.

I try to convert this query to Entity Framework Core like this, but I get back more than 1 row:

var query = _dbContext
                .LinkClubeUser
                .Join(_dbContext.Clube,
                    lnk => lnk.IdClube,
                    clube => clube.Id,
                    (Lnk, clube) => new { Lnk, clube })
                .Where(t => t.Lnk.IdUser == "9faea9f3-28d7-4e34-8572-3102726d3c75")
                .GroupBy(t => new
                {
                    t.clube.Id,
                    t.clube.Nome,
                    t.clube.NomeNaCamisola,
                    t.clube.NumNaCamisola,
                    t.clube.Posicao,
                    t.clube.Situacao,
                    t.clube.DataInscricao
                }, t => t.clube)
                .Select(g => new
                {
                    Nome = g.Key.Nome,
                    NomeNaCamisola = g.Key.NomeNaCamisola,
                    NumNaCamisola = g.Key.NumNaCamisola,
                    Posicao = g.Key.Posicao,
                    Situacao = g.Key.Situacao,
                    DataInscricao = (DateTime)g.Max(p => p.DataInscricao)
                }).Take(1);

but this code is converted by Entity Framework Core like this

SELECT 
    [lnk].[Id], [lnk].[Date], [lnk].[IdClub],
    [lnk].[IdQuestionario], [lnk].[IdUser], [clube].[Id],
    [clube].[ClubActual], [clube].[Date], [clube].[Nome],
    [clube].[NomeNaCamisola], [clube].[NumNaCamisola], [clube].[Posicao],
    [clube].[Situacao]
FROM 
    [LinkClubeUser] AS [lnk] 
INNER JOIN 
    [Clube] AS [clube] ON [lnk].[IdClube] = [clube].[Id]
WHERE 
    [lnk].[IdUser] = N'9faea9f3-28d7-4e34-8572-3102726d3c75' 
ORDER BY 
    [clube].[Id], [clube].[Nome], [clube].[NomeNaCamisola],
    [clube].[NumNaCamisola], [clube].[Posicao], [clube].[Situacao],
    [clube].[DataInscricao]

I don't see the group by clause.

What's wrong in my Entity Framework Core query?

Answer 1

I don´t have the group clause.

I think you added all the clube table fields to your group clause so your group clause lost its nature and did not compile to SQL, while you added some of them in your first SQL query.

Edit

var query = (from user in _dbContext.LinkClubeUser
             Join club in _dbContext.Clube on user.IdClub equals club.Id into cu
             let clubUser = cu.DefaultIfEmpty() // outer join if you need it
                Where clubUser.IdUser == "9faea9f3-28d7-4e34-8572-3102726d3c75")
                Select  new
                {
                    Nome = user.Nome,
                    NomeNaCamisola = user.NomeNaCamisola,
                    NumNaCamisola = user.NumNaCamisola,
                    Posicao = user.Posicao,
                    Situacao = user.Situacao,
                    DataInscricao = (DateTime)clubUser.Max(p => p.DataInscricao)
                }).Take(1);

Hope it helps.