Filtering DateTime in IQueryable Result

Question

I have an application that is displaying a list of users on a page. The client has an option to filter the users. In my repository, I have a Linq query that returns an IQueryable of User. I am applying the filter and paging to the IQueryable result and calling the .ToList(). I have not been able to get the date filtering to work properly. It does not return anything if I include the "/" as part of a date search. For example, I have a patient with DOB 07/10/2000. It returns the patient when I type "07" but nothing if I type "07/". What changes can I make to the below query, without calling .ToList() before paging, to get the date filtering to work correct?

  IQueryable<User> users = _db.Users.Where(x => x.UserID == userID && x.Active == true);

  if (!string.IsNullOrWhiteSpace(param.filterValue))
            {
                users = users.Where(x => x.firstName.ToLower().Contains(param.filterValue.ToLower())
                 || x.lastName.ToLower().Contains(param.filterValue.ToLower())
                 || x.dateOfBirth.ToString().Contains(param.filterValue.ToLower())
                );
            }

Updated 08/17/2018

dateOfBirth is a DateTime field. However, the time is being ignored. The param.filterValue is an instance of the Parameters class shown below. It contains the value that is entered in the search box.

public class Parameters
{
    public int MAX_PAGE_SIZE {
        get {
            return 100;
        }
    }
    public string filterValue { get; set; }
    public int PageIndex { get; set; }
    public int PageSize { get; set; }
}

Also, I try it with this change.

x.dateOfBirth.ToString("MM/dd/yyyy").Contains(param.filterValue.ToLower())

It throws an exception

LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

Answer 1

Change you code like below:

if (!String.IsNullOrWhiteSpace(SearchString))
        {
            users = users.Where(s => s.LastName.ToLower().Contains(SearchString.ToLower())
                  || s.FirstName.ToLower().Contains(SearchString.ToLower())
                  || s.DateOfBirth.ToString("MM/dd/yyyy").Contains(SearchString.ToLower()));
        }

Converts the value of the current DateTime object to its equivalent short date string representation.

Update: s.DateOfBirth.ToString("MM/dd/yyyy") is supported in EntityFrameworkCore, it seems you are using EntityFramework which is only support s.DateOfBirth.ToString() after EF 6.1. Before EF 6.1, it even not support s.DateOfBirth.ToString.

For a workaround, try to change IQueryable to AsEnumerable.

            var users = db.Product.AsEnumerable();
        if (!string.IsNullOrWhiteSpace(filterValue))
        {
            users = users.Where(x => 

              x.CreationTime.ToString("MM/dd/yyyy")
              .Contains(filterValue.ToLower()));                 

        }
        var result = users.ToList();