3
I need to make a return from my database in a specific way, but I have a problem, the situation is as follows:
I have this query already tested directly on SQL Server, and is working perfectly:
SELECT
COUNT (ate.id) AS data,
pes.tp_raca AS categories,
pes.genero AS name
FROM
Atendimento ate
INNER JOIN Pessoa pes ON pes.id = ate.pessoa_id
WHERE
ate.dt_atendimento >= '2018-08-20'
AND ate.dt_atendimento <= '2018-11-10'
GROUP BY
pes.tp_raca,
pes.genero
And returns the following result:
data categories name
1 5 Feminino
1 2 Masculino
2 5 Masculino
In the code C#, query looked like this:
using (var context = new SiviasContext())
{
DateTime inicio = new DateTime(2018,08,20);
DateTime fim = new DateTime(2018,11,10);
var result = context.Database.SqlQuery<List<Tuple<int, string, string>>>("SELECT COUNT (ate.id) AS data, pes.tp_raca AS categories, " +
"pes.genero AS name FROM Atendimento ate INNER JOIN Pessoa pes ON pes.id = ate.pessoa_id " +
"WHERE ate.dt_atendimento >= "+inicio.ToString("yyyy-MM-dd")+" AND ate.dt_atendimento <= "+ fim.ToString("yyyy-MM-dd") +
" GROUP BY pes.tp_raca, pes.genero").ToList();
ViewBag.result = JsonConvert.SerializeObject(result);
}
The way it is, it does not present an error, but it returns an empty list. Note that direct execution on SQL I use the data as a string, but if I use it as a string in the code C#, it presents an error saying that it is not possible to use a string in a Datetime type. That is, it recognizes that the date attribute in the database is of the type Datetime.
I’ve tried using the date without passing the .Tostring("yyyy-MM-dd") to format, but it also presents an error complaining of 00 (zeroes) of the team contained in the date.
So I wish you could help me out on this, how do I fix?
==============================================================
The resolution was given following the idea of @Pedro Paulo, thus:
List<RetornoConsulta> result = context.Atendimentos.Where(x => x.dt_atendimento >= dt_inicio)
.Where(x => x.dt_atendimento <= dt_fim)
.Select(g => new {
categories = g.Pessoa.tp_raca.ToString(),
name = g.Pessoa.genero
})
.GroupBy(g => new {
categories = g.categories,
name = g.name
})
.Select(g => new RetornoConsulta
{
data = g.Count(),
categories = g.Key.categories.ToString(),
name = g.Key.name
}).ToList();
Why doesn’t Voce use Linq? Your models aren’t mapped? With Linq the search is simpler and easier to use
– Edenilson Bila
I would like but could not get by the following: Here I am presenting an example of query already ready. Because I need to mount a query that receives filters as optional parameters and also cross-reference two information using the group by, but I couldn’t use it in Iqueryable. It would work like this, the user chooses the two fields to be crossed. In the return I check which is the first field to be crossed and do the group by on top of him, then the second to be crossed and do another group by of it on top of the previous one, but I had difficulty in it, so I chose to do it by typing the query.
– Tácio Brito