How to format Datetime column inside Contains method

Question

I have database table called Absence, which has a column called ABSDate. The type of this column is Datetime.

I'm trying to use a LINQ query to search inside this table. When I use the Contains method to query against other columns, I get results:

Working Query

dataGridView1.DataSource = (from A in context.Absence 
    join S in context.Stagiaire on A.STG equals S.ID 
    join G in context.Groupe on S.GRP equals G.CODE 
    join F in context.FILERE on G.FL equals F.CODE 
    select new { A.ID, A.ABSDate, A.STG, S.Nom, S.Prenom, S.GRP, G.FL })
.Where(X => X.STG.Contains(SearchBox.Text) ||
       X.Nom.Contains(SearchBox.Text) || 
       X.Prenom.Contains(SearchBox.Text) ||
       X.GRP.Contains(SearchBox.Text))
.Select(x => new { x.ID, Date = x.ABSDate, x.Nom, Prénom = x.Prenom, Filiére = x.FL, Groupe = x.GRP })
.ToList();

However, when I use ABSDate as part of the query, I get the following error:

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

Broken Query

dataGridView1.DataSource = (from A in context.Absence 
    join S in context.Stagiaire on A.STG equals S.ID 
    join G in context.Groupe on S.GRP equals G.CODE 
    join F in context.FILERE on G.FL equals F.CODE 
    select new { A.ID, A.ABSDate, A.STG, S.Nom, S.Prenom, S.GRP, G.FL })
.Where(X => X.STG.Contains(SearchBox.Text) || 
       X.Nom.Contains(SearchBox.Text) || 
       X.Prenom.Contains(SearchBox.Text) || 
       X.GRP.Contains(SearchBox.Text) || 
       X.ABSDate.ToString("dd/MM/yyyy hh:mm").Contains(SearchBox.Text))
.Select(x => new { x.ID, Date = x.ABSDate, x.Nom, Prénom = x.Prenom, Filiére = x.FL, Groupe = x.GRP })
.ToList();

Clearly, I know that this is where the issue lies:

X.ABSDate.ToString("dd/MM/yyyy hh:mm").Contains(SearchBox.Text)

How can I format the SearchBox value to dd/MM/yyyy hh:mm, and search by it using Contains method?

Does this answer your question? [Why LINQ to Entities does not recognize the method 'System.String ToString()?](https://stackoverflow.com/questions/10110266/why-linq-to-entities-does-not-recognize-the-method-system-string-tostring) — Heretic Monkey, Oct 27 '20 at 21:22

score 2 · Accepted Answer · answered Oct 27 '20 at 21:12

There are two ways to go about this:

1st - client side evaluation

dataGridView1.DataSource = (from A in context.Absence 
    join S in context.Stagiaire on A.STG equals S.ID 
    join G in context.Groupe on S.GRP equals G.CODE 
    join F in context.FILERE on G.FL equals F.CODE 
    select new { A.ID, A.ABSDate, A.STG, S.Nom, S.Prenom, S.GRP, G.FL })
.Where(X => X.STG.Contains(SearchBox.Text) || 
       X.Nom.Contains(SearchBox.Text) || 
       X.Prenom.Contains(SearchBox.Text) || 
       X.GRP.Contains(SearchBox.Text)
       ).AsEnumerable().Where(X => X.ABSDate.ToString("dd/MM/yyyy hh:mm").Contains(SearchBox.Text))
.Select(x => new { x.ID, Date = x.ABSDate, x.Nom, Prénom = x.Prenom, Filiére = x.FL, Groupe = x.GRP })
.ToList();

The second would be to format the textbox SearchBox.Text to the format of datetime.

Bear in mind that it should be formatted as a datetime, as there is no like equivalent for dates in linq to sql.

https://learn.microsoft.com/en-us/dotnet/api/system.datetime.parse?view=netcore-3.1 is an option. Using a calendar control to select a date would be the wise thing to do though, instead of allowing free text. The tryParse flavor would be better too — Athanasios Kataras, Oct 27 '20 at 21:22
Massive thanks this took me to think about a good idea, it is to convert all coming data from table as `Enumerable` using `AsEnumerable`, after that search going fine, massive thanks again sir — X Dev, Oct 27 '20 at 21:24

score 0 · Answer 2 · answered Oct 27 '20 at 21:27

Solution small developed from Athanasios Kataras answer

dataGridView1.DataSource = (from A in context.Absence 
join S in context.Stagiaire on A.STG equals S.ID 
join G in context.Groupe on S.GRP equals G.CODE 
join F in context.FILERE on G.FL equals F.CODE 
select new { A.ID, A.ABSDate, A.STG, S.Nom, S.Prenom, S.GRP, G.FL })
.AsEnumerable().Where(X => X.STG.Contains(SearchBox.Text) || X.Nom.Contains(SearchBox.Text) || X.Prenom.Contains(SearchBox.Text) || X.GRP.Contains(SearchBox.Text)|| X.ABSDate.ToString("dd/MM/yyyy hh:mm").Contains(SearchBox.Text))
.Select(x => new { x.ID, Date = x.ABSDate, x.Nom, Prénom = x.Prenom, Filiére = x.FL, Groupe = x.GRP }).ToList();

Thanks again Athanasios Kataras

How to format Datetime column inside Contains method

2 Answers2