Linq filter part of the date

user_9578398 0
Viewed 676

I have an Input and a datatable, when the user type in that input I filter data on KeyUp depending on what the user typed in. just like datatables.net but it is from scratch for learning.

The datatable has 3 columns fullname (string), city (string) and dateofbirth (datetime).

I want if the user type for example: "2/" it will give him all the DOB's that has 2/ in them. so far this is my code (I tried many but none worked).

    var data = _context.Person.Where(p => p.DateOfBirth.Value
                       .ToString("d").Split()[0].Contains(SearchQuery)).ToList();

// Split() to remove the time part.

And:

var data = _context.Person.Where(p => p.DateOfBirth.Value
                           .ToString().Contains(SearchQuery)).ToList();

But both giving me an exception: 'Translation of method System.DateTime.ToString' failed

entity-framework linq c#
3 Answers

When you use LINQ-to-entities, your C# code isn't actually executed. It is parsed. The runtime will parse the C# code as an expression and translate each symbol into some portion of a SQL string. It then sends this string to the database to be executed on the server. The server doesn't support the same kinds of date formatting, so your method call expression is not supported; there is no way to translate it into a SQL string.

However, the bigger problem here is that you should not be hitting the database with every keystroke; that is going to cause problems.

Instead, hit the database only once, when you load the form. Cache the list in memory. You can do this by calling ToList. Store the results in a member variable.

List<Person> _data;

void Form_Load(object sender, EventArgs e)
{
    _data = _context.Person.ToList();
}

Now that you have a local copy, you can use that instead of calling the database. And because your LINQ query doesn't have to be translated into a SQL statement, you can use any c# method you want.

void SearchQuery_KeyUp(object sender, KeyEventArgs e)
{
     var data = _data.Where(p => p.DateOfBirth.Value
                   .ToString("d").Split()[0].Contains(SearchQuery)).ToList();
     Display(data);
}
user_27915400

You can use a LIKE statement which will get close to achieving what you want.

var data = _context.Person.Where(p => EF.Functions.Like(p.DateOfBirth, $"%{searchquery}%")).ToList();

A problem will be that if you are saving the dates as a DateTime, DateTime2 or DateTimeOffset field then their representation in the database will be different to the string representation that you've entered. i.e. they probably won't have the '/' in them (although it's impossible to say since you don't mention your database technology).

user_85327480

If you load the table to memory first by calling eg context.person.ToList() your method should work, and shouldn't fail converting DateTime to a string.

I'd suggest the following:

_context.Person.ToList().Where(p => p.DateOfBirth.Value
                           .ToString("dd/MM/yyyy").Contains(SearchQuery)).ToList();
user_90693730
Related