How to search date in a dynamic query using IQueryable in c#

user_1050179 0
Viewed 448

I need to search in sql server database table. I am using IQueryable to build a dynamic query like below

var searchTerm = "12/04";      
var samuraisQueryable = _context.Samurais.Include(x => x.Quotes).AsQueryable();
samuraisQueryable = samuraisQueryable.Where(x => x.Name.Contains(searchTerm) ||
                                                    x.CreatedDate.HasValue && x.CreatedDate.Value.ToString()
                                                        .Contains(searchTerm)

var results = samuraisQueryable.ToList();

The above query is just an example, actual query in my code is different and more complicated.

Samurai.cs looks like

public class Samurai
{

    public Samurai()
    {
        Quotes = new List<Quote>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime? CreatedDate { get; set; }
    public List<Quote> Quotes { get; set; }
}

The data in the table looks like

enter image description here

I don't see any results becasue the translated SQL from the above query converts the date in a different format (CONVERT(VARCHAR(100), [s].[CreatedDate])). I tried to specify the date format in the above query but then I get an error that The LINQ expression cannot be translated.

samuraisQueryable = samuraisQueryable.Where(x => x.Name.Contains(searchTerm) ||
                                                         x.CreatedDate.HasValue && x.CreatedDate.Value.ToString("dd/MM/yyyy")
                                                            .Contains(searchTerm)
iqueryable linq c#
1 Answers

If (comments) users will want to search partially on dates, then honestly: the best thing to do is for your code to inspect their input, and parse that into a range query. So; if you see "12/04", you might parse that into a day (in the current year, applying your choice of dd/mm vs mm/dd), and then do a range query on >= that day and < the next day. Similarly, if you see "2021", your code should do the same, but as a range query. Trying to do a naïve partial match is not only computationally expensive (and hard to write as a query): it also isn't very useful to the user. Searching just on "2" for example - just isn't meaningful as a "contains" query.

Then what you have is:

(var startInc, var endExc) = ParseRange(searchTerm);
samuraisQueryable = samuraisQueryable.Where(
    x => x.CreatedDate >= startInc && x.CreationDate < endExc);
Marc_Gravell_233540
Related