CODEWITHSUNDEEP
c#asp.net
Ethel Patrick
Ethel Patrick

Reputation: 975

How to search by date using Entity Framework?

I am trying to convert a date to a string so I can search on the value entered. I am using a lamda expression and DateTime.ParseExact as I just want to use the short date entered.

This is my connection to the database:

var devices = db.Devices
            .Include(d => d.DeviceType)
            .Include(d => d.ManufacturerModel)
            .Include(d => d.ManufacturerModel.Manufacturer);

and my search 

if (!String.IsNullOrEmpty(searchString5))
{
    devices = devices.Where(s => DateTime.ParseExact(s.DateReceived,'dd/MM/yyyy');
}

Upvotes: 0

Views: 3595

Answers (3)

Jamey Geoghagan
Jamey Geoghagan

Reputation: 7

Searching dates with entity can be rough I converted my string into a DateTime format then searched each part of the date to pull the date that was needed I'm not sure if this is the direction you wanted to go but this worked for me

                    DateTime date = Convert.ToDateTime(SearchText); 
                    query = query.Where(x => x.Date.Month == date.Month
                                          && x.Date.Day == date.Day
                                          && x.Date.Year == date.Year);

// Let me know if this worked for you

Upvotes: 1

Leonardo Herrera
Leonardo Herrera

Reputation: 8406

Don't use strings. Let the framework do the heavy lifting for you.

DateTime dt;
if (DateTime.TryParse(searchString5, out dt)) {
    qry = qry.Where(s => s.Fecha.Date == dt.Date);
} else {
    throw new Exception("Bad input");
}

The generated SQL (in my case) is as follows, which is quite good.

-- Region Parameters
DECLARE @p0 DateTime = '2016-03-19 00:00:00.000'
-- EndRegion
SELECT 
   ...
FROM 
   ...
WHERE CONVERT(DATE, [t0].[Fecha]) = @p0

Here's a tip: use LINQPad to write and analyze your queries. It's free (and the paid version is cheap but it's absurdly powerful and useful.)

Upvotes: 0

Win
Win

Reputation: 62260

You cannot compare two dates easily, because you still need to compare hours, minutes and seconds.

Instead, you want to let user to choose ranges - From Date and To Date.

For example,

var query = db.Devices
            .Include(d => d.DeviceType)
            .Include(d => d.ManufacturerModel)
            .Include(d => d.ManufacturerModel.Manufacturer);

string fromDate = "1/15/2016", toDate = "1/30/2016";
    DateTime fromDateTime, toDateTime;

if(!DateTime.TryParse(fromDate, out fromDateTime))
{
    // Make fromDateTime to Start of Day - 1/15/2016 12:00:00 AM
    fromDateTime = fromDateTime.Date;
    query = query.Where(x => x.Date >= fromDateTime);
}

if (!DateTime.TryParse(toDate, out toDateTime))
{
    // Make toDateTime to End of day - 1/30/2016 11:59:59 PM
    toDateTime = toDateTime.Date.AddDays(1).AddTicks(-1);
    query = query.Where(x => x.Date <= toDateTime);
}

var result = query.ToList();

Upvotes: 2

Related Questions