1
I’m having trouble with a consultation within my application. The values returned by my query are repeated, the same value appears 407 times, when the right one would have to be 407 different record, just like it is in the database. I tried to perform the query in two different ways:
List<TBZFI2_APENAS_DRE> Zfi2 = db.TBZFI2_APENAS_DRE.Where(x => x.TBCLASSECUSTO.TBSUBMETRICA_CLASSE.Any(y => y.Id_SubMetrica == itens.Id_SubMetrica) && x.mes == mesInicial && x.ano == tbplanejamento.Ano).ToList();
and also in that way:
string query1 = string.Format("SELECT * FROM TBZFI2_APENAS_DRE ZFI2 JOIN TBCLASSECUSTO CC ON CC.Id = ZFI2.Id_Classe JOIN TBSUBMETRICA_CLASSE SC ON SC.Id_Classe = CC.Id JOIN TBSUBMETRICA SUBMETR ON SC.Id_SubMetrica = SUBMETR.Id WHERE ZFI2.ano = {0} AND SUBMETR.Id = {1} and ZFI2.mes = {2}", tbplanejamento.Ano, itens.Id_SubMetrica, mesInicial);
var ClassesPareto = db.TBZFI2_APENAS_DRE.SqlQuery(query1);
Both forms return me a result of 407 repeated records. Debugging the application, I got the mounted query that the second form listed above generates. The query was the following:
SELECT *
FROM TBZFI2_APENAS_DRE ZFI2
JOIN TBCLASSECUSTO CC ON CC.Id = ZFI2.Id_Classe
JOIN TBSUBMETRICA_CLASSE SC ON SC.Id_Classe = CC.Id
JOIN TBSUBMETRICA SUBMETR ON SC.Id_SubMetrica = SUBMETR.Id
WHERE ZFI2.ano = 2019
AND SUBMETR.Id = 24
and ZFI2.mes = 1
As we can see, the parameters I passed in the query worked well, I took this same query mounted and played directly in SQL Server and the result was correct. I had a return of 407 records, each with its respective value.
Conclusion: The same query when executed by the application returns a list of all the repeated records and when generated by the database returns the correct records. I have tried everything and I did not get a satisfactory result. I would like your help to solve this case.
Check that the table primary key is mapped correctly.
– Elton