Help with SqlQuery in context Entity Framework

Question

Help with SqlQuery in context Entity Framework

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (0 votes)

2

I need to make a return of my database in a specific way, however I have a problem, the situation is as follows:

I have this query already tested directly on SQL Server , and it 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 C # tag, the query looks 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);
}

As it stands, it does not display an error, but it returns an empty list. Notice that direct execution in SQL uses the date as a string, but if I use it as a string in the C # code, 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 type Datetime.

I tried to use the date without passing the .ToString ("yyyy-MM-dd") to format, however it also presents a complaining error of 00 (zeros) of the team contained in the date.

So, I wish you could help me with this, how do I resolve it?

===================================================== ===============

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

sql-server datetime c# entity-framework

asked by anonymous 13.11.2018 / 16:28

3 answers

1

The SQL Server query works because there you are delimiting date field values between single quotation marks (% with%):

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

But in C # it is not.

Do this:

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: ' , available from C # 6.0, Visual Studio 2015) for the code to be 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 $"'{var}'" :

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

Or use concatenation of String.Format() simply, as it was doing before:

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

EDITION

Still the questioner reported that the error occurred:

Converting an nvarchar data type to a datetime data type resulted in a value out of range. ( The conversion of a varchar data type to a datetime resulted in an out-of-range value.

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

DBCC USEROPTIONS

And check the record whose ymd column is Set Option .

To force a format on this query, individually, use the command dateformat ( 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();

14.11.2018 / 00:56

0

Try to use between. Staying 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);

13.11.2018 / 16:38

Passing values from checkboxes to an element contained in a div How to apply a delay in the CSS display property on a change from none to flex without JavaScript?

score 3 · Accepted Answer

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 to the query with the class SQLParameter of the Entity Framework, and we will inform you that the return will be a list of objects of 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();