Solved

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

Posted on 2015-02-04
13
477 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40590580
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
ID: 40591088
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
ID: 40591139
Because it's nullable, try .Value.Date
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 35

Expert Comment

by:Robert Schutt
ID: 40591210
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
ID: 40591228
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
ID: 40591349
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
 

Author Comment

by:Joe Ruder
ID: 40591355
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
ID: 40591373
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
ID: 40591411
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
ID: 40592292
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
ID: 40593122
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
ID: 40600618
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
ID: 40604383
I appreciate you working through this with me.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows a few slightly more advanced techniques for Windows 7 gadget programming, including how to save and restore user settings for your gadget and how to populate the "details" panel that is displayed in the Windows 7 gadget gallery.  …
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 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