CODEWITHSUNDEEP
c#linqentity-framework-6
Silvia Parfeni
Silvia Parfeni

Reputation: 528

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();

Upvotes: 2

Views: 2252

Answers (4)

Gilad Green
Gilad Green

Reputation: 37299

TimeOfDay Is not supported by the linq provider and it does not know how to parse it into sql. Use instead DbFunctions.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 = DbFunctions.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();

Upvotes: 3

Cetin Basoz
Cetin Basoz

Reputation: 23797

ds.TrafficJamMorning = (from row in orderQuery
                        where 
                            DbFunctions.DiffMinutes( DbFunctions.TruncateTime(row.AcceptedTime), row.AcceptedTime) >= 6 * 60 + 30 &&
                            DbFunctions.DiffMinutes( DbFunctions.TruncateTime(row.AcceptedTime), row.AcceptedTime) <= 9 * 60 + 30
                        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();

Upvotes: 0

Jon Koeter
Jon Koeter

Reputation: 1094

I use the following where clause on my IQueryable:

var query = dbContext.GetAllItems().AsQueryable();

//... other filters

if(MusBeBetween6and8){
    query = query.Where(item => item.AcceptedTime.Hour > 6 && item.AcceptedTime.Hour < 8);
}

//... other filters

return query.ToList();

Hope it helps. This also works for Oracle + Odac.

Upvotes: 0

Dynamic
Dynamic

Reputation: 1663

I had a similar problem.

You can compare the date parts instead.

where row.Year > s.Year && r.Month > s.Month && row.Day > s.Day

Upvotes: -1

Related Questions