CODEWITHSUNDEEP
c#entity-frameworklinq
X Dev
X Dev

Reputation: 97

How to format Datetime column inside Contains method

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?

Upvotes: 1

Views: 232

Answers (2)

X Dev
X Dev

Reputation: 97

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

Upvotes: 0

Athanasios Kataras
Athanasios Kataras

Reputation: 26342

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();
  1. 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.

Upvotes: 2

Related Questions