Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Comparing dates in linq

Posted on 2011-05-03
6
Medium Priority
?
1,326 Views
Last Modified: 2013-11-11

return context.ScheduledKeywordEntities.Where(s => ((s.every_day == true || s.day == day)
                                         && (s.start_time >= time && s.end_time.Value <= time))
                                         && ((Convert.ToDateTime(s.date_executed).Date != DateTime.Today.Date)
                                         || (Convert.ToDateTime(s.date_executed).Date == DateTime.Today.Date && (s.time_executed < s.start_time || s.time_executed > s.end_time))))
                                         .Select(s => new ScheduledKeywords
                                         {
                                             TimeTableID = s.timetable_id,
                                             KeywordID = s.keyword_id,
                                             KeyWord = s.keyword,
                                             AdDisplayURL = s.ad_display_url
                                         }).ToList();

I have the above LINQ to SQL query.  I am trying to compare dates and times and am having problems.  I keep in getting  - LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.Object)' method, and this method cannot be translated into a store expression.

How do I compare dates so that they are both in the right format?

Thanks

Kevin.
0
Comment
Question by:KABarrie
6 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 668 total points
ID: 35513571
Please try the following:

return context.ScheduledKeywordEntities.Where(s => ((s.every_day == true || s.day == day)
                                         && (s.start_time >= time && s.end_time.Value <= time))
                                         && ((s.date_executed.Date != DateTime.Today.Date)
                                         || (s.date_executed.Date == DateTime.Today.Date && (s.time_executed < s.start_time || s.time_executed > s.end_time))))
                                         .Select(s => new ScheduledKeywords
                                         {
                                             TimeTableID = s.timetable_id,
                                             KeywordID = s.keyword_id,
                                             KeyWord = s.keyword,
                                             AdDisplayURL = s.ad_display_url
                                         }).ToList();

Open in new window

0
 

Author Comment

by:KABarrie
ID: 35513610

I get "Syset.Nullable<System.DateTime> does not contain a definition for Date...."

Kev.
0
 
LVL 64

Assisted Solution

by:Fernando Soto
Fernando Soto earned 664 total points
ID: 35513817
Hi KABarrie;

It looks like from the error message that you are using the Entity Framework, Linq to Entity and NOT Linq to SQL. In Linq to EF that DateTime functions are not supported. To work around the issue read the solution on this post http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/abc19619-61ff-4bc5-bda9-df501f113c74

Fernando
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Assisted Solution

by:kumar754
kumar754 earned 668 total points
ID: 35516338
actually if you convert, you will have to take care for null values too.. alternative is don't use  .Value or .Date with Nullable<DateTime> in Linq2SQL unless you are sure there is no Nullable type.. This this approach, same your condition but just approach is different.
return context.ScheduledKeywordEntities
	.Where
	(s => 
		(	
			(s.every_day == true || s.day == day)
			&& 
			(s.start_time >= time && s.end_time <= time)
		)
		&& 
		(
			(s.date_executed < DateTime.Now || s.date_executed > DateTime.Now)
            || 
			(
				(s.date_executed >= DateTime.Now && s.date_executed <= DateTime.Now)
				&& 
				(s.time_executed < s.start_time || s.time_executed > s.end_time)
			)
		)
	)
    .Select(s => new ScheduledKeywords
    {
		TimeTableID = s.timetable_id,
		KeywordID = s.keyword_id,
		KeyWord = s.keyword,
	    AdDisplayURL = s.ad_display_url
    }
).ToList();

Open in new window

0
 

Author Comment

by:KABarrie
ID: 35705254

It was me being stupid.

final code -


return context.ScheduledKeywordEntities.Where(s => (s.every_day == true || s.day == day)
                                         && (s.start_time.Value <= time && s.end_time.Value >= time)
                                         && ((s.date_executed != DateTime.Today.Date)
                                         || (s.date_executed == DateTime.Today.Date 
                                         && (s.time_executed < s.start_time || s.time_executed > s.end_time))
                                         || s.date_executed == null))
                                         .Select(s => new ScheduledKeywords
                                         {
                                             TimeTableID = s.timetable_id,
                                             CustomerID = (int)s.customer_id,
                                             KeywordID = s.keyword_id,
                                             KeyWord = s.keyword,
                                             AdDisplayURL = s.ad_display_url
                                         }).ToList();

Open in new window

0
 

Author Closing Comment

by:KABarrie
ID: 35705262
Thanks Guys
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month10 days, 18 hours left to enroll

572 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