Use DbFunctions to return the dateTime with mins included

Question

I have the following code, which truncates the time:

// does this already exist? 
var seekScan = (from s in aDb.Item_Scan_Event
                where s.item_detail_id == th_piece_id &&
                      s.scan_type == scantype &&
                      DbFunctions.TruncateTime(s.scan_datetime) == dt_scan.s.ScanDatetime.Date
                select s).FirstOrDefault();

What I need instead is to have it compare the dateTime including the HH:mm

I have tried using something simple like a string comparison but you can't do that inside of a Linq-to-Entities:

var seekScan = (from s in aDb.Item_Scan_Event
                where s.item_detail_id == th_piece_id &&
                      s.scan_type == scantype &&
                      s.scan_datetime.Value.ToString("MM/dd/yyyy HH:mm") == dt_scan.s.ScanDatetime.ToString("MM/dd/yyyy HH:mm")
                select s).FirstOrDefault();

The error I get is:

LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.'

Can you not define a local variable with the dates without the seconds and milliseconds and use those in your query? — lordvlad30, Nov 19 '19 at 10:23
It is the portion on the left side of the compare (the database part) that is causing the issue. I am not sure how to do what you suggest? — Joe Ruder, Nov 19 '19 at 10:25
I am not sure what you are suggesting exactly, I'm sorry. I get the concept, but I cannot figure out a way to cod that? — Joe Ruder, Nov 19 '19 at 10:29
I think it will have to do with CreateTime or something....still working on it. — Joe Ruder, Nov 19 '19 at 10:30
this might help https://stackoverflow.com/questions/30235304/how-to-use-canonical-functions-in-entity-framework-and-mysql — Arjun Vachhani, Nov 20 '19 at 07:49

score 2 · Answer 1 · edited Nov 19 '19 at 15:28

Not elegant, but works.

//does this already exist?  Ignore items scanned within the same minute.
var seekScan = (from s in aDb.Item_Scan_Event
        where
        s.item_detail_id == th_piece_id &&
        s.scan_type == scantype &&
        (s.scan_datetime.Value.Year == dt_scan.s.ScanDatetime.Year &&
        s.scan_datetime.Value.Month == dt_scan.s.ScanDatetime.Month &&
        s.scan_datetime.Value.Day == dt_scan.s.ScanDatetime.Day &&
        s.scan_datetime.Value.Hour == dt_scan.s.ScanDatetime.Hour &&
        s.scan_datetime.Value.Minute == dt_scan.s.ScanDatetime.Minute )
        select s
    ).FirstOrDefault();

score 1 · Answer 2 · answered Nov 19 '19 at 10:42

1

Take a look on this FUNCTION database.AddDays does not exist

In Mysql I've to create the function, perhabs you need to review the function in your bd.

answered Nov 19 '19 at 10:42

fmalh3iro

384
2
7

Fourat · Accepted Answer · 2019-11-20T15:17:09.220

1

Simplifying Joe Ruder's answer and using DbFunctions class :

var seekScan = (from s in aDb.Item_Scan_Event
        where
        s.item_detail_id == th_piece_id &&
        s.scan_type == scantype && (
        DbFunctions.TruncateTime(s.scan_datetime.Value).Value == dt_scan.s.ScanDatetime.Date &&
        DbFunctions.DiffMinutes(s.scan_datetime.Value, dt_scan.s.ScanDatetime) == 0 )
        select s
    ).FirstOrDefault();

Update

We could just use the DiffMinutes and that would work unless it was that exact same time and a different day ?

It will fail for the case when you have the same time span but different dates.

does DiffMinutes ignore the date portion ? So 11/11/2019 11:02 vs 11/12/2019 11:03 would be 1 ?

I'm not sure if it returns the diff between dates and time span or just time span, it has been a long time since I used it. I guess you'll test it and let us know.

edited Nov 20 '19 at 15:17

answered Nov 19 '19 at 14:37

Fourat

2,366
4
38
53

Question: Will that not chop off the minutes completely? DbFunctions.TruncateTime(s.scan_datetime.Value).Value -- ? I like it, I am just trying to understand it. – Joe Ruder Nov 20 '19 at 14:43
I think I see, the first line verifes the date is the same and the second && condition looks to see if the difference in minutes exists? – Joe Ruder Nov 20 '19 at 14:53
so I guess I have two questions: 1. we could just use the DiffMinutes and that would work unless it was that exact same time and a different day? 2. does DiffMinutes ignore the date portion? So 11/11/2019 11:02 vs 11/12/2019 11:03 would be 1? – Joe Ruder Nov 20 '19 at 14:58
@JoeRuder I edited my answer with details you asked for. – Fourat Nov 20 '19 at 15:17
I work a Midnight to noon shift as part of my team is located in a different country than I am . I will test it and let you know when I get back in......thank you – Joe Ruder Nov 20 '19 at 15:36
@JoeRuder glad I could help, please accept my answer if it solves your issue – Fourat Nov 20 '19 at 16:05

score 1 · Answer 4 · answered Nov 20 '19 at 07:42

when you define a property of type Datetime if no attribute is defined for the property by default it will save the time including hour,minute and seconds as well.

so depending on the situation below are some usecases:

Scenario 1: check only for day/month/year

a) For DateTime

var loRecords = context.ORDERS.Where(x=>x.O_ORDER_TIME.Date.Equals(DateTime.Now.Date).ToList()

b) For DateTime?

 var loRecords = context.ORDERS.Where(x=>x.O_ORDER_TIME?.Date.Equals(DateTime.Now.Date).ToList()

Scenario 2: check for day/month/year Hour and minute

a) for DateTime

var loRecords = context.ORDERS.Where(x=>x.O_ORDER_TIME.Date.Equals(DateTime.Now.Date) 
&& x.O_ORDER_TIME.Date.Hour == DateTime.Now.Date.Hour
&& x.O_ORDER_TIME.Date.Minute == DateTime.Now.Date.Minute).ToList()

Use DbFunctions to return the dateTime with mins included

4 Answers4