EFCore How can i extract hour from datetime and use it in where clause?

Question

Using

• EFCore (7.0.5)
• ASPNetCore (7.0.0) and
• Pomelo.EFCore.Mysql (7.0.0)

i have the following code to retrieve data from a table.

var v = await _dbContext.HistoLogs!
                 .Where(h => h.Datecre > DateTime.Today.AddDays(-31))
                 .ToListAsync();

Simple and it works in my case, except i want to exclude range from 7AM to 9PM (7:00 to 21:00).

I can make it in MySql with the following request

SELECT EXTRACT(HOUR FROM h.Datecre), h.Datecre, h.`Action` 
from myschema.HistoLogs h
WHERE Datecre > DATE_SUB(NOW(), INTERVAL 31 DAY)  AND 
(EXTRACT(HOUR FROM h.Datecre) BETWEEN 22 AND 23 OR EXTRACT(HOUR FROM h.Datecre) BETWEEN 0 AND 6)

but i don't understand how to deal with such a request in EFCore. Can please someone point me in the right direction ?

It seems not to be working, but could it be something like

var v = await _dbContext.HistoLogs!
                 .Where(h => h.Datecre > DateTime.Today.AddDays(-31))
                 .Where(h => h.Datecre.Hour < 7)
                 .Where(h => h.Datecre.Hour > 21)
                 .ToListAsync(); 

Regards, Pierre

Answer 1

 DateTime pastDate = DateTime.Today.AddDays(-31);
 var v = await _dbContext.HistoLogs
             .Where(h => !(h.Datecre.Hour <= 7 || h.Datecre.Hour >= 21))
             .ToListAsync(); // Where Not for exclusion 

var v2 = await _dbContext.HistoLogs
             .Where(h => (h.Datecre.Hour <= 7 || h.Datecre.Hour >= 21))
             .ToListAsync(); // Where for inclusion