Date conversion operations within linq to entities

Question

Inside a third-party database dates are stored as strings in format YYYYMMDD.

In SQL I could use the following statement:

 WHERE  convert(datetime,convert(varchar(8),a.appt_date,112)) = convert(datetime,convert(varchar(8), dateadd(dd,+1,getdate()), 112))

Is there something similar i can do in LINQ to EF? Or is my only option doing it in memory, ex:

dim data = (from item in entities.itemsSet).ToList()
dim filtered = from item in data
       where Convert.ToDateTime(shift.starttime) >= startDate 
           && Convert.ToDateTime(shift.endtime) < endDate
       select item

Did you try your second example without `ToList()`? It should simply work ([link](http://stackoverflow.com/q/992189/335858)). — Sergey Kalinichenko, Feb 10 '15 at 14:39
First, that's *not* how you compare dates in SQL. You should cast the field to `date` from `datetime` and compare it to a date parameter. SQL Server is smart enough to convert it to a range query. Second, why `Convert.ToDateTime` if the underlying field is already a date? If it isn't, it's using the wrong type and should be fixed first — Panagiotis Kanavos, Feb 10 '15 at 14:39
Just noticed that you *do* store dates as strings. You need to fix this problem first. SQL Server does have a `date` type. There's no reason to use strings for this and a lot of reasons you shouldn't. — Panagiotis Kanavos, Feb 10 '15 at 14:57
The DB is 3rd party, not possible to change the underlying data type, but i agree, its horrible. Query all rows and then sort using Convert.ToDateTime is just resulting in very poor performance. That query should return 50-100 rows, in a table that has 100k rows. — DDulla, Feb 16 '15 at 21:44

score 1 · Answer 1 · answered Feb 10 '15 at 14:48

1

you should do something like:

dim d = SomeDate
dim d0 = d.Date().ToString("yyyyMMdd")
dim d1 = d.AddAys(1).ToString("yyyyMMdd")
dim filtered = from item in data
   where d0 >= startDate 
       && d1 < endDate
   select item

that is build your string in code and use it as parameter in you query. You will so save lot of conversion time for the same result.

answered Feb 10 '15 at 14:48

tschmit007

7,559
2
35
43

Actually, you *cause* conversions this way, because you force SQL Server to convert the strings to dates when you don't have to. EF uses parameterized queries so dates are always transferred as dates. If the underlying filed isn't a date - then there's a bug that must be fixed – Panagiotis Kanavos Feb 10 '15 at 14:54
@PanagiotisKanavos first line of the post: Inside my DB dates are stored as strings in format YYYYMMDD. – tschmit007 Feb 10 '15 at 14:55
@PanagiotisKanavos i disagree, it's not a bug if i am forced to use a 3rd party DB that has the dates stored as such. The range is typically small, comprising of 1 -5 dates, i found the fastest way to return the results was to convert the dates into strings and then just query for the string matches. – DDulla Feb 16 '15 at 21:46
Then it's a bug in that other database - too many such cases, esp. in accounting software. Using the wrong data type is always a bug and will always result in issues. In this case, the only solution is to convert to string on the client side - *and look for another vendor*. – Panagiotis Kanavos Feb 17 '15 at 07:41

Date conversion operations within linq to entities

1 Answers1