Help with Sqlquery in context Entity Framework

Question

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();

Answer 1

First create a class that represents the return of your query data:

public class RetornoConsulta
{
    public int Data { get; set; }
    public string Categories { get; set; }
    public string Name { get; set; }
}

In your query we will pass the start and end dates as parameters for the query with the class SQLParameter of the Entity Framework, and we will inform you that the return will be a list of objects in our class RetornoConsulta:

List<SqlParameter> parametros = new List<SqlParameter> 
{
    new SqlParameter("@dataInicio", inicio),
    new SqlParameter("@dataFim", fim)
};

var result = context.Database.SqlQuery<RetornoConsulta>(
                 @"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 BETWEEN @dataInicio AND @dataFim
                   GROUP BY pes.tp_raca, pes.genero", 
                   parametros.ToArray()
                ).ToList();

Answer 2

The SQL Server query works because there you are delimiting the values of the date field between simple quotes ('):

WHERE
    ate.dt_atendimento >= '2018-08-20'
AND ate.dt_atendimento <= '2018-11-10'

But in C# it’s not.

Do so:

var dtIni = inicio.ToString("yyyy-MM-dd");
var dtFim = fim.ToString("yyyy-MM-dd");
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 BETWEEN '{dtIni}' AND '{dtFim}'" +
      " GROUP BY pes.tp_raca, pes.genero"
   ).ToList();

I used string interpolation (example: $"'{var}'", available from C# 6.0, Visual Studio 2015) to make code readable:

     $" WHERE ate.dt_atendimento BETWEEN '{dtIni}' AND '{dtFim}'" +

If you are using an earlier version of C#/Visual Studio, you can change this filter line to use String.Format():

      String.Format(" WHERE ate.dt_atendimento BETWEEN '{0}' AND '{1}'", dtIni, dtFim) +

Or use the concatenation of strings simply, as I was doing before:

      " WHERE ate.dt_atendimento BETWEEN '" + dtIni + "' AND '" + dtFim + "'" +

EDITION

Still the questioner reported the error:

Conversion of an nvarchar data type into a datetime data type resulted in a value outside the range.
(The Conversion of a varchar data type to a datetime data type resulted in an out-of-range value.)

In this case the date format configured for the base in SQL Server may be different from ymd. To check which format is configured for the database, run the query:

DBCC USEROPTIONS

And check the log whose column Set Option be it dateformat.

To force a format in this query, individually, use the command SET DATEFORMAT ymd (docmentation and reference):

var result = context.Database.SqlQuery<List<Tuple<int, string, string>>>(
      "SET DATEFORMAT ymd; " +
      "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 BETWEEN '{dtIni}' AND '{dtFim}'" +
      " GROUP BY pes.tp_raca, pes.genero"
   ).ToList();

Answer 3

Try to use between. Get like this:

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 between "+inicio.ToString("yyyy-MM-dd")+" AND "+ fim.ToString("yyyy-MM-dd") +
    " GROUP BY pes.tp_raca, pes.genero").ToList();
   ViewBag.result = JsonConvert.SerializeObject(result);