Display Results by Date ASP.NET MVC

Question

Can anybody please tell me where I am going wrong with this. I have a list of errors and I want to be able to search by date. For example, if I selected the date, 20/08/2015, from a date picker, then I want the list to just show errors from that date. By default it will show all the errors in the database, but when a date is selected, I want to show only the errors from that date.

Controller

[HttpPost]
public ActionResult Index(string sortOrder, int? page, DateTime? datePicker)
{
    ViewBag.CurrentSort = sortOrder;
    ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";

    DateTime userSelectedDate = DateTime.Parse(Request["datePicker"]);

    var applications = from s in db.ElmahErrors
                       where s.TimeUtc == userSelectedDate
                       select s;

    switch (sortOrder)
    {
        default:
            applications = applications.OrderByDescending(x => x.TimeUtc);
            break;
    }

    int pageSize = Int32.Parse(System.Configuration.ConfigurationManager.AppSettings["DefaultPageSize"]);
    int pageNumber = (page ?? 1);
    return View(applications.ToPagedList(pageNumber, pageSize));
}

View

 @using (Html.BeginForm())
{
    <label for="datePicker">Find By Date:</label>

    @Html.TextBox("datePicker", null, new { @type = "date" })

    <input id="submitBtn" type="submit" value="Search" />
}

The userSelectedDate variable is taking in the value from the date picker, but it doesn't match any of the errors. Can somebody please help?

Thanks in advance.

Why do you have a parameter `DateTime? datePicker` (which will contain the selected date), then ignore it and use `DateTime userSelectedDate = DateTime.Parse(Request["datePicker"]);`? — , Aug 26 '15 at 07:56
possible duplicate of [How to compare only date components from DateTime in EF?](http://stackoverflow.com/questions/1478215/how-to-compare-only-date-components-from-datetime-in-ef) — Chris Disley, Aug 26 '15 at 07:57

score 4 · Answer 1 · edited May 23 '17 at 12:03

4

You're selecting based on a DateTime, not a Date, so the records would have to occur at exactly the time (down to the millisecond) that your DateTime variable is set to. To do what you're after, you'll need to do a little more to your select query to match just the date parts. There are a few ways to do this, but this SO question sums them up pretty well:

How to compare only date components from DateTime in EF?

Essentially, the simplest way is to use the EntityFunctions.TruncateTime method on both dates within your predicate.

edited May 23 '17 at 12:03

Community

1
1

answered Aug 26 '15 at 07:56

Chris Disley

1,286
17
30

Apologies. Just realised that this method has been superseded by DbFunctions.TruncateTime(DateTime). Same code works with one replaced with the other. – Chris Disley Aug 26 '15 at 08:03

score 1 · Accepted Answer · answered Aug 26 '15 at 08:02

As @Chris Disley pointed out you need to select errors based on a date. The usual approach for this is when you have a date, select all the lines where the date is between the date and the next day. Something like this:

[HttpPost]
public ActionResult Index(string sortOrder, int? page, DateTime? datePicker)
{
    ViewBag.CurrentSort = sortOrder;
    ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";

    DateTime userSelectedDate = DateTime.Parse(Request["datePicker"]);

    var startDate = userSelectedDate.Date;
    var endDate = startDate.AddDays(1);

    var applications = from s in db.ElmahErrors
                   where s.TimeUtc >= startDate && s.TimeUtc < endDate
                   select s;

    switch (sortOrder)
    {
        default:
            applications = applications.OrderByDescending(x => x.TimeUtc);
            break;
    }

    int pageSize = Int32.Parse(System.Configuration.ConfigurationManager.AppSettings["DefaultPageSize"]);
    int pageNumber = (page ?? 1);
    return View(applications.ToPagedList(pageNumber, pageSize));
}

Is there any way of doing this without using `[HttpPost]` as I am wanting to implement PagedList which uses `FormMethod.Get` — D.M, Aug 27 '15 at 12:39
@D.M, yes. Just replace `[HttpPost]` with `[HttpGet]` and make sure the routing engine selects the proper route. — RePierre, Aug 27 '15 at 12:46

score 0 · Answer 3 · answered Aug 26 '15 at 08:09

0

I think your s.TimeUtc is clearly never equal to userSelectedDate;

Why, because you have a datePicker which implies you can't select time on it, then you parse it to a DateTime which defaults the time to some value, then you compare it to s.TimeUtc and I believe it will be a millisecond comparision which you are not likely to hit in the nearest decade.

So I suggest you do something like

s.TimeUtc.ToLocalTime().Date == userSelectedDate.Date

answered Aug 26 '15 at 08:09

evictednoise

587
1
7
19

Unfortunately, although my first instinct also, EF won't evaluate that properly to be able to do the comparison as part of the predicate. The TruncateTime method I suggested does exactly that though, removes the time element from the equation (sets to midnight on the given date). – Chris Disley Aug 26 '15 at 08:13
I haven't worked with EF much, so my answer may not be literally suitable for the specific case – evictednoise Aug 27 '15 at 06:43

Display Results by Date ASP.NET MVC

Controller

View

3 Answers3