Silvia Parfeni Silvia Parfeni - 1 month ago 16
C# Question

Entity framework filtering data by time

I am so sorry from the question, but I can not take a period from a

DateTime
. for exemple: If I have date
"10.10.2016 7:00", 10.10.2016 10:00"
, I need to take only the rows with the time between "6:00" and "8:00". Next is my code by return an error :
"can not use TimeOfDay ",
help me please

ds.TrafficJamMorning = (from row in orderQuery
where row.AcceptedTime.TimeOfDay >= new TimeSpan(6, 30, 0) &&
row.AcceptedTime.TimeOfDay <= new TimeSpan(9, 30, 0)
group row by row.AcceptedTime.Date
into grp
select new TrafficJamPeriodInfo
{
CurrentDateTime = grp.Key,
ReceptionCount = grp.Count(r => r.OrderOriginId == (int)OrderOrigin.Reception),
InternetCount = grp.Count(r => r.OrderOriginId == (int)OrderOrigin.Internet),
ExchangeSystemCount = grp.Count(r => r.OrderOriginId == (int)OrderOrigin.ExchangeSystem)
}).ToList();

Answer

TimeOfDay Is not supported by the linq provider and it does not know how to parse it into sql. Use instead EntityFunctions.CreateTime:

Also instantiate the timespans before the linq query so you do not instantiate a new object every time

var startTime = new TimeSpan(6, 30, 0);
var endTime = new TimeSpan(9, 30, 0);

var result = (from row in orderQuery
              let time = EntityFunctions.CreateTime(row.AcceptedTime.Hour, row.AcceptedTime.Minute, row.AcceptedTime.Second)
              where time  >= startTime &&
                    time  <= endTime
              group row by DbFunctions.TruncateTime(row.AcceptedTime) into grp
              select new TrafficJamPeriodInfo
              {
                   CurrentDateTime = grp.Key,
                   ReceptionCount = grp.Count(r => r.OrderOriginId == (int)OrderOrigin.Reception),
                   InternetCount = grp.Count(r => r.OrderOriginId == (int)OrderOrigin.Internet),
                   ExchangeSystemCount = grp.Count(r => r.OrderOriginId == (int)OrderOrigin.ExchangeSystem)
              }).ToList();

Looking again at the question - If all you want to check is that it is between 2 hours then use the Hour property (This won't be nice to write if you want to check for example Hour and Minues and in that case I'd go for my first suggestion):

var result = (from row in orderQuery
              where row.AcceptedTime.Hour >= 6
                    row.AcceptedTime.Hour < 8
              group row by DbFunctions.TruncateTime(row.AcceptedTime) into grp
              select new TrafficJamPeriodInfo
              {
                   CurrentDateTime = grp.Key,
                   ReceptionCount = grp.Count(r => r.OrderOriginId == (int)OrderOrigin.Reception),
                   InternetCount = grp.Count(r => r.OrderOriginId == (int)OrderOrigin.Internet),
                   ExchangeSystemCount = grp.Count(r => r.OrderOriginId == (int)OrderOrigin.ExchangeSystem)
              }).ToList();
Comments