LINQ Return from the list is with all the repeated records

Question

I’m having problems performing a select (using LINQ) in a View in SQL Server 2012. The values stored in the bank are as below:

   ID_Acomp    ID_Pessoa    Nome      Data
     26           300     MONTEIRO  01-01-2016
     27           300     MONTEIRO  02-02-2016
     28           300     MONTEIRO  03-03-2016

When I perform select in SQL Manager, the values return perfectly. But when I do the same select through LINQ, the value of the last record is replicated over the other records above, and it looks like this:

   ID_Acomp    ID_Pessoa    Nome      Data
     28           300     MONTEIRO  03-03-2016
     28           300     MONTEIRO  03-03-2016
     28           300     MONTEIRO  03-03-2016

The code I’m using in the application basically is this:

IQueryable<VW_PESSOA_ACOMPANHAMENTO> vwPessoaAcomp =  
                            contexto.VW_PESSOA_ACOMPANHAMENTO.AsQueryable();

if (ID_Pessoa > 0)
{
    vwPessoaAcomp = vwPessoaAcomp(p => p.ID_Pessoa == ID_Pessoa);
}

var retorno = (from A in vwPessoaAcomp
                 orderby A.ID_Acomp descending
                   select A).ToList();

Below is the code of my view:

SELECT A.ID_Acomp, P.ID_Pessoa, P.Nome, A.Data 
    FROM ACOMPANHAMENTO A, PESSOA P WHERE A.ID_ACOMP = P.ID_ACOMP

Answer 1

The code below should work perfectly, if it is returning wrong probably the problem is in the View, in the tables involved, or in the data involved:

var vwPessoaAcomp = contexto.VW_PESSOA_ACOMPANHAMENTO.AsQueryable();

if (ID_Pessoa > 0)
{
    vwPessoaAcomp = vwPessoaAcomp.Where(p => p.ID_Pessoa == ID_Pessoa);
}

var retorno = vwPessoa.OrderBy(p => p.ID_Acomp).ToList();

I would modify the view code to the following code, which I consider more readable:

SELECT A.ID_Acomp, P.ID_Pessoa, P.Nome, A.Data 
    FROM ACOMPANHAMENTO A JOIN PESSOA P ON A.ID_ACOMP = P.ID_ACOMP

You could give a SELECT * FROM PESSOA and another SELECT * FROM ACOMPANHAMENTO and publish the results here to know what data are involved in this case.