Where with date and time - Entity Framework

Asked

Viewed 126 times

0

I am making a select with Entity framework. where I need to use Where, with date and time, in the select of SQL SERVER, I would do so:

     select dbo.contas_receber.id, dbo.contas_receber.tipo_conta,dbo.contas_receber.data_pagamento, dbo.contas_receber.data_pagamento,dbo.contas_receber.observacao, dbo.contas_receber.valor,dbo.contas_receber.forma_pagamento from dbo.contas_receber  where (dbo.contas_receber.data_pagamento) >= ('" + data_abertura + "')  and DATEADD(day,  -DATEDIFF(day, 0,'" + data_abertura + "'), dbo.contas_receber.data_pagamento) >= '" + hora_aber + "'

In Entity, I was able to do just the date part, I can’t do the time part:

_context.ContasApagar.Select(p => new CaixasViewModel
        {
            Data = p.DataPagamento,
            Hora = p.DataPagamento,
            Historico = p.Obs,
            Valor = p.ValorPago,
            Forma = p.FormaPagamento
        }).ToList()).Where(p => p.Data >= data_abertura && p.Hora >= DateTime.Parse(hora_aber));

If I do so, it does not select correctly, it filters by date longer than opening date, and time longer than opening time, but if the time is shorter, but it is a day ahead of opening data_date, it should appear in select.

c# entity-framework

  • what comes in data_abertura and hora_aber ? one is datetime another string ?

  • Opening date is a datetime, and hora_abert is a string.

  • and what content of it: hora_aber ("01/01/0001 08:10:00" or "08:10:00"... ) ?

  • Hora_aber is 08:10, I need for example, the opening date is 12/07/2018 15:00, and there is an account that was paid on 13/07/2018 10:20, this account needs to appear, filtering by the time does not work, because 10:20 is less than 15:00, in sql I use DATEADD(day, -DATEDIFF(day, 0,'" + opendata_ + "'), in Entity I don’t know how to do

1 answer

1


I see no reason to separate Date of Time... in C# Datetime treats both, so if you convert "08:10" to Datetime, the value will be 01/01/0001 08:10:00.

You need to inform Date as well.

Also, you can filter the objects before Select, thus filtering in p.Datapayment.

DateTime hora_abertura = DateTime.Parse(hora_aber);
DateTime data = data_abertura.AddHours(hora_abertura.Hour).AddMinutes(hora_abertura.Minute);


_context.ContasApagar.Where(p=> p.DataPagamento >= data).Select(p => new CaixasViewModel
        {
            Data = p.DataPagamento,
            Hora = p.DataPagamento,
            Historico = p.Obs,
            Valor = p.ValorPago,
            Forma = p.FormaPagamento
        }).ToList();

The conversion of Datetime, I put in .Netfiddle

Browser other questions tagged c# entity-framework

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