CODEWITHSUNDEEP
c#linqasp.net-mvc-4datetimelinq-to-entities
Baggerz
Baggerz

Reputation: 387

C# MVC4 Linq - Filter table by either date or date + time

I have a table containing several columns which I search by column + a user entered string in order to filter the results. However I'm having trouble getting my DateTime column to filter correctly (the rest all work). I can show results of an exact match, but not if the user enters just a date with no time I get no results.

Expected results are if a user enters 14/08 or 14/08/2014 or 14/08/2014 12:56:32 all results matching this entered date or date and time should display.

My Linq

var filterresults = from m in db.cd_CleardownCore
                            select m;

My Filter Attempt 1: (Exact match only < Works)

DateTime datetime = Convert.ToDateTime(searchString);
string strDate = datetime.ToShortDateString();

filterresults = filterresults.Where(x => x.TimeStarted == datetime)

My Filter Attempt 2: (Checking if column contains date < doesn't currently Work) 

filterresults = filterresults.Where(x => x.TimeStarted.ToString().Contains(strDate));

So could someone explain why attempt 2 does not work? or could suggest a solution? + if i can provide anything to make it clearer just let me know.

Upvotes: 0

Views: 3455

Answers (3)

Mrchief
Mrchief

Reputation: 76208

DateTime matches are going to be tricky, since you accept a wide array of inputs.

If the format is more or less fixed, you can probably get away with string matching.

So assuming you always accept in DD/MM/YYYY HH:MI:SS, you can try

filterresults = filterresults.ToArray().Where(x => x.TimeStarted.ToString("dd/MM/yyyy hh:mm:ss").IndexOf(strDate) > -1));

What you're essentially doing here is normalizing your comparison base and since data and time have different delimiters, a simple substring check can give you correct results.

So assuming that TimeStarted is 14/08/2014 12:56:32:

"14/08/2014 12:56:32".IndexOf("14/08") // matches
"14/08/2014 12:56:32".IndexOf("14/08/2014") // matches
"14/08/2014 12:56:32".IndexOf("14/08/2014 12:56:32") // matches

This logic will work as long as people search for parts of date in order like:

DD
DD/MM
DD/MM/YYYY

and so on.

It may be ambiguous if user enters 09 and 09 means both day or month. But if your UI always goes by the rule the first 2 are day, second 2 are month, next 4 are year and so on, then you'd be fine.

Also note that I'm doing a ToArray since Linq2Entities doesn't understand ToString.

Upvotes: 1

Habib
Habib

Reputation: 223207

You need two queries for that:

First check if the datetime object has time other than 00:00:00 that means that some time part is specified, in that case you need exact comparison, otherwise compare the field for passed date range like:

DateTime datetime = Convert.ToDateTime(searchString);
if (datetime.TimeOfDay != TimeSpan.Zero) //comparison with 00:00:00 time
{
    filterresults = filterresults.Where(x => x.TimeStarted == datetime);
}
else
{
    DateTime plusOneDateTime = datetime.AddDays(1).Date;
    filterresults = filterresults.Where(x => x.TimeStarted >= datetime.Date 
                                           && x.TimeStarted < plusOneDateTime);
}

The reason your queries are failing is because ToString usage in LINQ to entities would fail since there is no translation available from LINQ expression to underlying data source language (SQL).

Also remember that chances are your comparison with exact time may not return any results, since searchString might not contain the precise time as stored in the database. In that case you can resort to range checking for hours/minutes/seconds.

Upvotes: 2

Hogan
Hogan

Reputation: 70513

You want to compare the date part -- so do that:

DateTime datetime = Convert.ToDateTime(searchString);


filterresults = filterresults.Where(x => x.TimeStarted.Date == datetime.Date)

You could also use ToString("d") but that is not needed.

Upvotes: 0

Related Questions