Solved

using EF 5 and linq in C# to compare two dates as equal

Posted on 2015-02-04
13
371 Views
Last Modified: 2015-02-11
Hello;

I am needing to do a compare for a date but using just the date, not the time.
I have it working but I am not sure it is the best way, everything I have tried to just compare two dates using a == does not work:

  DateTime DateToCheck1 = new DateTime (2015, 2,3);
            DateTime DateToCheck2 = DateToCheck1.AddDays(1);
            
            var allStops = from stops in context.distribution_stop_information
                           where stops.customer_no == 97808 &&
                            (stops.datetime_created > DateToCheck1 && stops.datetime_created < DateToCheck2)
                           select new
                           {
                               createdDate = stops.datetime_created,
                               stopName = stops.stop_name,
                               uniqueID = stops.unique_id_no
                           };

Open in new window


Thank you in advance,

Joe
0
Comment
Question by:Joe Ruder
  • 7
  • 6
13 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
Try it like this, then you don't need the variable DateToCheck2:
stops.datetime_created.Date == DateToCheck1

Open in new window

0
 

Author Comment

by:Joe Ruder
Comment Utility
Thank you,

Sorry, I had tried that - gives:
Error      1      'System.Nullable<System.DateTime>' does not contain a definition for 'Date' and no extension method 'Date' accepting a first argument of type 'System.Nullable<System.DateTime>' could be found (are you missing a using directive or an assembly reference?)      C:\Programming projects\Clients\1st Choice\MonitorDHL\MonitorDHL\Program.cs      52      53      MonitorDHL

I had also tried:
(stops.datetime_created.Value.Date == DateToCheck1)
Then it will compile but you get the famous:
Additional information: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

I am having to use EF 5 not 6 due to that fact that I am using dotconnect for Postgrsql to hit a postgresql database.
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
Because it's nullable, try .Value.Date
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
oops sorry, missed your remark that you already tried it, it works for me but I only tested with a simple class...
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
Then the main remark I have on your code is to use >= DateToCheck1 because in theory you can now miss out on records that are timed exactly at midnight.
0
 

Author Comment

by:Joe Ruder
Comment Utility
Good point, I will make that change.

If I find a way to do this in the next couple days I will update this, but it just seems like we should be able to somehow do a simple compare without having to do all the gyrations.

Surely checking two date objects in linq is not some strange thing I am trying to do.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Joe Ruder
Comment Utility
An update, I do have my project working with EF 6 vs 5 now if that opens up any new avenues.
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
Alas, I haven't set up an EF project to test this, like I admitted earlier, I just defined a local class and for that it works but in your case it would look like you're out of luck, unless you could add a similar method as an entity member but that may be nonsense. I don't have all that much EF experience, more of a low level guy... but if you need a date instead of datetime I would hope it's possible to add the equivalent of a calculated column in your entity/table? Or create a view which includes that info (select x, y, z, convert(date, yourcolumn) from yourtable)? Just some (in this context maybe dumb) ideas to try and kickstart some better train of thought...
0
 

Author Comment

by:Joe Ruder
Comment Utility
The below seems to work, but I would like to know from some people who have used it if I am setting myself up for performance issues later or other problems:

            DateTime DateToCheck1 = new DateTime(2015, 2, 3);

            var allStops = from stops in context.DistributionStopInformations
                           join items in context.DistributionLineItems
                           on stops.UniqueIdNo equals items.UniqueIdNo
                           where stops.CustomerNo == 97808 &&
                            (DbFunctions.TruncateTime(stops.DatetimeCreated) == DateToCheck1.Date) 
                           select new
                           {
                               createdDate = stops.DatetimeCreated,
                               stopName = stops.StopName,
                               uniqueID = stops.UniqueIdNo,
                               item = items.ItemNumber
                           };

Open in new window

0
 

Author Comment

by:Joe Ruder
Comment Utility
For anybody else checking here, here is what I found out:

First off - using this:
  (stops.DatetimeCreated >= DateToCheck1.Date && stops.DatetimeCreated <= DateToCheck2)

with the DateToCheck2 set 1 day later produces the fastest run time, with the following SQL being generated:

SELECT 
1 AS "C1",
"Extent1".datetime_created,
"Extent1".stop_name,
"Extent1".unique_id_no,
"Extent2".item_number
FROM  cops_reporting.distribution_stop_information AS "Extent1"
INNER JOIN cops_reporting.distribution_line_items AS "Extent2" ON "Extent1".unique_id_no = "Extent2".unique_id_no
WHERE ("Extent1".customer_no = 97808) AND (("Extent1".datetime_created >= :p__linq__0) AND ("Extent1".datetime_created <= :p__linq__1))

Open in new window


This runs in a average of about .26 seconds

Changing it to:
(DbFunctions.TruncateTime(stops.DatetimeCreated) == DateToCheck1.Date)

Generates this SQL:

SELECT 
1 AS "C1",
"Extent1".datetime_created,
"Extent1".stop_name,
"Extent1".unique_id_no,
"Extent2".item_number
FROM  cops_reporting.distribution_stop_information AS "Extent1"
INNER JOIN cops_reporting.distribution_line_items AS "Extent2" ON "Extent1".unique_id_no = "Extent2".unique_id_no
WHERE ("Extent1".customer_no = 97808) AND ((DATE_TRUNC('day', "Extent1".datetime_created)) = :p__linq__0)

Open in new window


And runs in a average of about 2 seconds, not sure why but it is quite a bit slower.

The strange thing is this:
(stops.DatetimeCreated >= DateToCheck1.Date

takes FOREVER to run-- well, about 70-80 seconds - even though it is return the exact same dataset.
It generates this SQL:

SELECT 
1 AS "C1",
"Extent1".datetime_created,
"Extent1".stop_name,
"Extent1".unique_id_no,
"Extent2".item_number
FROM  cops_reporting.distribution_stop_information AS "Extent1"
INNER JOIN cops_reporting.distribution_line_items AS "Extent2" ON "Extent1".unique_id_no = "Extent2".unique_id_no
WHERE ("Extent1".customer_no = 97808) AND ("Extent1".datetime_created >= :p__linq__0)

Open in new window


Now, even if I set the second date to something like 365 days into the future, as long as there is a date range vs just open it runs fast.

Very strange.
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
For me the worrying part would be that you get the same result without upper limit ;-)

If you're interested in running times, try checking out the query plans. It is a big difference I must say. The logical conclusion would be that with a limited date range, a different index on your table can be used which cuts down the number of records that need to be retrieved significantly.
0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
Comment Utility
Upon re-reading your last comments, some more remarks:

- still a non-issue probably but when you changed the filter expression to: (stops.DatetimeCreated >= DateToCheck1.Date && stops.DatetimeCreated <= DateToCheck2) you included both boundaries, my remark earlier was specifically about checking DateToCheck1, the second one should remain < DateToCheck2 otherwise the midnight boundary gets included on both sides of a check.

- now that I look at the generated code more closely, calling a function in the where clause (date_trunc) stops it from being sargable. That it 'only' slows the query to 2 secs must mean it's already operating on a subset of your data due to the customer_no restriction.

- However, I'm still not clear on how leaving out the upper boundary can give the same result set, unless there are just no records beyond that date for that customer. Then it would indeed mean that the query is significantly slower because lots of data has to be read before being discarded for other reasons such as another customer_no or could it be that even the join condition is being superseded by an index on date? Again, certainty in this situation is I think only possible from the query plan.
0
 

Author Closing Comment

by:Joe Ruder
Comment Utility
I appreciate you working through this with me.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
This article describes a technique for converting RTF (Rich Text Format) data to HTML and provides C++ source that does it all in just a few lines of code. Although RTF is coming to be considered a "legacy" format, it is still in common use... po…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now