Problems with Lambda and DateTime query

Question

Problems with Lambda and DateTime query

Navigation

#1 by (0 votes)

1

Hello, I have the following ActionResult code from my controller below:

[HttpPost]
[AuthorizedUser]
public ActionResult Load(DateTime DataInicial, DateTime DataFinal)
{
    var AcessoDominio = new SmartAdmin.Domain.Acesso();
    var Collection = new List<AcessoDto>();

    Collection = AcessoDominio.GetList(_ => _.DTH_ACESSO >= DataInicial &&
                                                _.DTH_ACESSO <= DataFinal)
                                                 .OrderByDescending(_ => _.DTH_ACESSO)
    .ToList();

    ViewBag.DataInicial = DataInicial;
    ViewBag.DataFinal = DataFinal;

    return View("Index", Collection.ToPagedList(1, PageSize));
}

When a submit in the page of two dates for example 09/22/2015 and 09/22/2015 respective Start Date and End Date my query returns nothing and when I change the query to 22/09/2015 and 23/09/2015 returns only items of the day 22 .

What I would need is to query the date in between and also by time the query that is generated by Entity is as follows:

SELECT
'Extent1'.'COD_ACESSO', 
'Extent1'.'COD_USUARIO', 
'Extent1'.'TIPO_USUARIO', 
'Extent1'.'IP', 
'Extent1'.'BROWSER', 
'Extent1'.'PLATAFORMA', 
'Extent1'.'RESOLUCAO', 
'Extent1'.'DTH_ACESSO', 
'Extent1'.'DIA', 
'Extent1'.'MES', 
'Extent1'.'ANO', 
'Extent1'.'HORA', 
'Extent1'.'URL_ACESSO', 
'Extent1'.'DISPOSITIVO', 
'Extent1'.'DOMINIO', 
'Extent1'.'SUPORTA_ACTIVEX', 
'Extent1'.'SUPORTA_COOKIES', 
'Extent1'.'SUPORTA_JAVA_APPLET', 
'Extent1'.'COORD_LATITUDE', 
'Extent1'.'COORD_LONGITUDE', 
'Extent1'.'CIDADE'
FROM 'ACESSO' AS 'Extent1'
 WHERE ('Extent1'.'DTH_ACESSO' >= @p__linq__0) AND ('Extent1'.'DTH_ACESSO' <= @p__linq__1

I do not know how to return for example items from the same day between hours 22/09/2015 00:00:00 and 22/09/2015 23:59:59

Details: My field already has DateTime '_.DTH_ACESSO' I do not know how to proceed!

datetime entity-framework asp.net-mvc

asked by anonymous 12.10.2015 / 16:12

1 answer

Insert the result of a query into a php file setError () overlapping ActionBar

score 0 · Accepted Answer

Use DbFunctions , using the TruncateTime method:

Regardless of the time you want to get all the dates in front of the informed period and you should tell the Entity Framework that you only want by the date ( this query does not need )

Example

DateTime dataInicial = DateTime.Parse("22/09/2015");
DateTime dataFinal = DateTime.Parse("22/09/2015");

myDataBaseEntities db = new myDataBaseEntities();

IList<Datas> datas = db.Datas
    .Where(x => DbFunctions.TruncateTime(x.Data) >= dataInicial 
             && DbFunctions.TruncateTime(x.Data) <= dataFinal)
    .ToList();

SQL generated because of DbFunctions.TruncateTime :

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[DataJulia] AS [DataJulia], 
    [Extent1].[Data] AS [Data]
    FROM [dbo].[Datas] AS [Extent1]
    WHERE ((convert (datetime2, convert(varchar(255), [Extent1].[Data], 102) ,  102)) >=
    @p__linq__0) 
   AND ((convert (datetime2, convert(varchar(255), [Extent1].[Data], 102) ,  102)) 
   <= @p__linq__1)

Note: If you change your rule where the date will come complete and you want to pick up by the hour also your screen should be changed to accept entering the time along with the date and then you do not need to put DbFunctions.TruncateTime . This comment is by reporting in the comments.