Comparing date with string Entity Framework

Question

Lets assume that I have a SQL Server database and a table which looks like:

Id int NOT NULL,
Date date NOT NULL

I have corresponding entity framework model:

public class Bundle
{
    public int Id {get; set;}
    public DateTime Date {get; set;}
}

User can type a string that can be anything. I need to find all items where Date, or any part of date, contains string entered by user. So, basically, I need to perform query:

SELECT Id, Date
FROM Bundles
WHERE Date LIKE '%user_query_here%'

My first attempt was

query.Where(b => b.Date.ToShortDateString().Contains(filter.Date))

This throws a NotSupportedException, so I tried this:

query.Where(b => Convert.ToString(b.Date).Contains(filter.Date));

Please note, that filter.Date is string. Its not a DateTime structure.

This also throws an exception. So my question is how execute query written above?
PS: I can't perform filtering in memory, this table has many thousands of rows.

Are you sure about your requirement? This looks really dangerous to me to rely on the string representation of a date. What if the culture between client and server is different? — ken2k
– ken2k, Commented Jul 4, 2014 at 12:42
Yes, I'm totaly sure. And I'm not afraid about issues, that can happen if culture on server and client are different. — user2283683
– user2283683, Commented Jul 4, 2014 at 12:50
You're aware this is going to be dog-slow, right, as it won't use indices? If you had a Calendar table you could run the date-search against that (much smaller table, with separated columns in indices too), which could then be joined as part of a regular range query.... but I'm not sure how to deal with that in Entity Framework. — Clockwork-Muse
– Clockwork-Muse, Commented Jul 4, 2014 at 13:02
Yes, I know about indexes. This is not a big deal because I have no index on this column. — user2283683
– user2283683, Commented Jul 4, 2014 at 13:13

Raphaël Althaus · Accepted Answer · 2014-07-04 13:03:23Z

5

There's no simple way to convert a datetime to string with l2e, sadly.

You may use some SqlFunctions methods

SqlFunctions.DatePart

will return an int representing a part of the Date (year, month, day for example).

and

SqlFunctions.StringConvert

can help you to convert the int in a string (casting it to double first), which you can then concatenate.

Something like

.Where(b => (SqlFunctions.StringConvert((double)SqlFunctions.DatePart("y", b)) 
           + SqlFunctions.StringConvert((double)SqlFunctions.DatePart("m", b))
           //etc.
             ).Contains(filter.Date)

of course this is unreadable, and really not index-friendly.

A (much) easier and cleaner way, if you're working with sql server, would be to create and use a computed column.

You can create it like that (google to find how to do this properly if you use Code First and Migrations)

alter table add StringDate as convert(varchar, [Date], 112)//or another format, this will be yyyymmdd

If you use code first, you'll have to mark the property with attribute

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

Then you will be able to do

.Where(b => b.StringDate.Contains(filter.Date))

edited Jul 4, 2014 at 13:03

answered Jul 4, 2014 at 12:54

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2283683 Over a year ago

Thanks very much for solution. I used computed columns solution.

Collectives™ on Stack Overflow

Comparing date with string Entity Framework

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related