I have a view in the v_PortalProprietario database that returns in each line the name of the owners that are repeated and the accounts (Acertos) that are unique. I would like to make a query in LINQ that groups the owner’s data into a DTO and a property with all the accounts referring to that owner. Making the query below is returning only the first Acerto in List, but in database executing the view I have a lot of rows with the same Proprietario.

var prop = (from p in db.v_PortalProprietario
            where p.ProprietarioId == pProprietario
            group p by new {p.ProprietarioId, p.ProprietarioNm, p.Documento}
            into gp
            select new ProprietarioDTO ()
            {
                ProprietarioId = gp.Key.ProprietarioId,
                ProprietarioNm = gp.Key.ProprietarioNm,
                Documento = gp.Key.Documento,
                Acertos = gp.OrderByDescending(f => f.DataAcerto)
                                    .Select(a => new AcertoDTO
                                    {
                                        AcertoId = a.AcertoId,
                                        DataAcerto = a.DataAcerto,
                                        FormaPagto = a.FormaPagto,
                                        ValorAcerto = a.ValorAcerto,
                                        FlagConsolidacao = a.FlagConsolidacao
                                    }).ToList()
            }
            ).ToList();

If I break in 2 blocks of code it works, but I want to understand why the only one doesn’t work.

That works:

var itens = (from p in db.v_PortalProprietario
             where p.ProprietarioId == pProprietario
             select p);

var prop2 = itens.GroupBy(p => new { p.ProprietarioId, p.ProprietarioNm, p.Documento })
                 .Select(gp => new ProprietarioDTO
                 {
                     ProprietarioId = gp.Key.ProprietarioId,
                     ProprietarioNm = gp.Key.ProprietarioNm,
                     Documento = gp.Key.Documento,
                     Acertos = gp.Select(a => new AcertoDTO
                     {
                         AcertoId = a.AcertoId,
                         DataAcerto = a.DataAcerto,
                         FormaPagto = a.FormaPagto,
                         ValorAcerto = a.ValorAcerto,
                         FlagConsolidacao = a.FlagConsolidacao
                     }).OrderByDescending(a => a.DataAcerto).ToList()
                 })
                 .ToList();