How to use ToShortDateString in linq lambda expression?

Question

I need to call ToShortDateString in a linq query suing lambda expressions:

toRet.Notification = Repositories
    .portalDb.portal_notifications.OrderByDescending(p =>       p.id)
    .FirstOrDefault(p => p.date.ToShortDateString() == shortDateString);

but I get the error:

An exception of type 'System.NotSupportedException' occurred in System.Data.Entity.dll but was not handled in user code

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

What can I do, considering that I do need to use ToShortDateString() ?

Thanks.

This thread may help: http://stackoverflow.com/questions/5370402/entity-framework-4-linq-how-to-convert-from-datetime-to-string-in-a-query — Brian Snow, Jul 15 '13 at 13:28
This thread should help: http://stackoverflow.com/questions/18233495/linq-to-entities-does-not-recognize-the-method-system-string-tostring-method — ps ps, Jun 12 '16 at 13:40

Sergey Berezovskiy · Answer 1 · 2013-07-15T13:31:56.980

24

Linq to Entities cannot convert ToSortDateString method into SQL code. You can't call it on server side. Either move filtering to client side (that will transfer all data from server to client), or consider to use server-side functions to take date part of date (you should pass DateTime object instead of shortDateString):

EntityFunctions.TruncateTime(p.date) == dateWithoutTime

edited Jul 15 '13 at 13:31

answered Jul 15 '13 at 13:26

Sergey Berezovskiy

232,247
41
429
459

Thank you for the quick response. After writing FirstOrDefault(p => (EntityFunctions.TuncateTime(p.date) == shortDateString) I received the error An exception of type 'System.Data.EntityCommandExecutionException' occurred in System.Data.Entity.dll but was not handled in user code Additional information: An error occurred while executing the command definition. See the inner exception for details. If there is a handler for this exception, the program may be safely continued. – Octavian Epure Jul 15 '13 at 13:54
@OctavianEpure as I stated above, instead of passing string `shortDateString` you should pass `DateTime` object – Sergey Berezovskiy Jul 15 '13 at 14:44
8

Using EF6, I had to use `DbFunctions.TruncateTime()` – Bpainter Dec 09 '15 at 20:23
using System.Data.Entity; .Select(a => DbFunctions.TruncateTime(a.MyDate)) – Mahesh Aug 11 '16 at 17:16

score 5 · Accepted Answer · answered Jul 15 '13 at 13:32

5

You shouldn't be forcing a string comparison when what you're working with is Date/time data - as soon as you force string comparisons, you're suddenly having to deal with how the strings are formatted.

Instead, have something like:

var endDate = targetDate.AddDays(1);

toRet.Notification = Repositories
.portalDb.portal_notifications.OrderByDescending(p =>       p.id)
.FirstOrDefault(p => p.date >= targetDate && p.date < endDate);

(Assuming that targetDate is whatever DateTime variable you had that was used to produce shortDateString in your code, and is already a DateTime with no time value)

answered Jul 15 '13 at 13:32

Damien_The_Unbeliever

234,701
27
340
448

I was looking for solution to this issue as well. Within database I may have dates like 2012-06-06 20:14:50.367. When looking for records that are posted on 2012.06.06 I have to convert date I search for from 2012.06.06 to 2012.06.05 23:59 or go for similar trick. I wanted to avoid it by removing the time component somehow but not from the searching date but dates in the database. Greets for everyone. really nice portal, lot's of useful info. – Mariusz May 11 '15 at 10:37

score 0 · Answer 3 · answered Jul 15 '13 at 13:40

0

Try this,

You can also used with below code.

Activity = String.Format("{0} {1}", String.Format("{0:dd-MMM-yyyy}", s.SLIDESHEETDATE), String.Format("{0:HH:mm}", s.ENDDATETIME))

answered Jul 15 '13 at 13:40

Jeet Bhatt

744
1
7
22

rana · Answer 4 · 2014-12-17T15:31:35.973

-3

ToShortDateString() method usually used to work only with date and ignore time stamps.

You will get exactly today result-set by using the following query.

Repositories.portalDb.portal_notifications.OrderByDescending(p =>       p.id)
        .FirstOrDefault(p => p.date.Date == DateTime.Now.Date);

By using Date property of DateTime struct you can just fetch record of that date only.

Note: Linq to Objects. Only works if you CAN (or have option) to bypass ToShortDateString() method

edited Dec 17 '14 at 15:31

answered Dec 03 '14 at 09:20

rana

145
1
2
8

1

`p.date.Date` will throw an exception. You can't do that in a LINQ to Entities query. – Corey Adler Dec 16 '14 at 14:31
You misunderstood me. `date` is a `DateTime` type property here. you can change it with `CreatedDate`. First look the question please ... – rana Dec 17 '14 at 14:01
I understand that `date` is a `DateTime` property. What I'm saying is that you can't use `date.Date` in LINQ to Entities since it doesn't know how to translate that into SQL. – Corey Adler Dec 17 '14 at 14:10
you can take this `p.CreatedDate.Date`. hope you get it :-) – rana Dec 17 '14 at 14:11
Yes that's when you're translating to `ToShortDateString` from `Date` in Linq query. You can do manipulation on `Date` object in Linq. If I understand you correctly. – rana Dec 17 '14 at 14:17
1

That's fine in LINQ to Objects, but *not* LINQ to Entities. – Corey Adler Dec 17 '14 at 14:20

How to use ToShortDateString in linq lambda expression?

4 Answers4

Linked

Related