Problems with Lambda and Datetime consultation

Asked

Viewed 373 times

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 do a Ubmit on the page of two dates for example 22/09/2015 and 22/09/2015 respective Initial Date and Final Date my query returns nothing and when I change the appointment to 22/09/2015 and 23/09/2015 returns only items of the day 22.

What I would need is to query the data in the between and also by the time the query that is generated by the 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 proceed to return for example same day items between hours 22/09/2015 00:00:00 and 22/09/2015 23:59:59

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

asp.net-mvc entity-framework datetime lambda-expressions

  • you want to pick up regardless of the time ?

  • So, I want to pick date and time. if someone accessed the system or better logged in at 22/09/2015 at 23:58:00 this data has to return. when making the consultation 22/09/2015 00:00:00 and 22/09/2015 23:59:59 but in View I have fields with Datepicker that only fill day month and year understands;

  • Take the test with my answer DbFunctions and tell me if it worked!

1 answer

0


Use Dbfunctions, with the method Truncatetime:

Regardless of the Time you want to pick up all the dates before the reported period and must inform the Entity Framework you only want for the date (I saw by your logic that the time in this appointment 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)

Observing: If you change your rule where the date will come complete and you want to pick by the time also your screen should be changed to accept the typing of the time along with the date and there do not need to put the Dbfunctions.Truncatetime. This observation is by reporting in the comments.

Browser other questions tagged asp.net-mvc entity-framework datetime lambda-expressions

You are not signed in. Login or sign up in order to post.