CODEWITHSUNDEEP
c#linqiqueryable
mosquito87
mosquito87

Reputation: 4440

Find all entries where nullable DateTime contains keyword

I have a model with a nullable DateTime. I'm trying to use an IQueryable object and find all entries where the DateTime matches to a string, if set:

query.Where(s => s.MyDate.HasValue && s.MyDate.Value.ToString("{ 0:dd.MM.yyyy}").Contains(keyword));

However this doesn't work as an exception is thrown: LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

As I've done some research, the problem seems to be that my where condition can't be translated to SQL.

How can I solve this problem?

Example data shown to the user:

Possible search terms:

Upvotes: 0

Views: 555

Answers (2)

NetMage
NetMage

Reputation: 26917

Since you aren't concerned about date formats (assuming the user knows what they are querying) just use the SQL Server default conversion:

query.Where(s => s.MyDate.HasValue && s.MyDate.Value.ToString().Contains(keyword));

Upvotes: 1

Jodn
Jodn

Reputation: 324

In case you're using the Entity Framework: Lets assume s is type DemoClass:

public partial class DemoClass 
{
  public Nullable<DateTime> MyDate;
  ...
}

would be the DemoClass.cs for your Entity. Just have an additional partial class in an extra file (this is the best way otherwise EF could override your edits if you're using the designer e.g.):

DemoClass_Additional.cs

public partial class DemoClass 
{
  [NotMapped]
  public string MyDateString {
    get
    { 
      if(this.MyDate.HasValue)
      {
        return this.MyDate.Value.ToString("{ 0:dd.MM.yyyy}");
      }
      else
      {
        return "";
      }
    }
}

[NotMapped] will exclude the property from the database mapping and finally your query would be

 query.Where(s => s.MyDate.HasValue && s.MyDateString.Contains(keyword));

Upvotes: 0

Related Questions