CODEWITHSUNDEEP
c#asp.net-mvclinq
Ehsan
Ehsan

Reputation: 21

How can I convert a colum of DATETIME type to string with linq

I have a table with three columns, ID, Date, Expenses, I am trying to search an int in Date column (Type of data in Date column is DateTime). For example, if the year is 1998 and the input is 8, the whole row should be displayed.

This is my code:

public ActionResult Get(string searchString)
{
     DateTime? startDate = new DateTime(2016, 1, 1);
     DateTime? endDate = new DateTime(2018, 12, 5);

     AccountingEntities db = new AccountingEntities();
     var expensesValues = from s in db.Expenses
                                 select s;

     if (startDate.HasValue)
     {
          expensesValues = expensesValues.Where(s => s.Date > startDate && s.Date < endDate);

          //This line gives error, I need to convert the s.Date to string so I can use Contains
          expensesValues = expensesValues.Where(s => s.Date.Contains(searchString));
     }
}

Upvotes: 1

Views: 593

Answers (4)

Caius Jard
Caius Jard

Reputation: 74700

Comments notwithstanding, if you do want to persist with this search I'd suggest doing it on the individual components, then you can be certain that EF will translate the C# to SQL and search what you expect

expensesValues = expensesValues.Where(s => 
  s.Date.Year.ToString().Contains(searchString) ||
  s.Date.Month.ToString().Contains(searchString) ||
  s.Date.Day.ToString().Contains(searchString)
);

If you need times continue the pattern

This also saves the db needlessly having to do a string concat, but if you're looking for outright performance I don't think there is a good option short of redefining how the search works

Upvotes: 0

thanzeel
thanzeel

Reputation: 602

you can use the EF.Functions() method from entity framework core like below

var expensesValues = await db.Expenses.Where(w => EF.Functions.Like(w.Date.ToString(),"%" + searchString + "%")).ToListAsync();

This will do the filtering in the SQL server itself. Good perfomance and simple code

Upvotes: 0

K. L.
K. L.

Reputation: 261

You can do so by calling ToString() on your date like so:

expensesValues = expensesValues.Where(s => s.Date.ToString().Contains(searchString));

Upvotes: 0

Michael
Michael

Reputation: 91

System.DateTime provides methods like ToShortDateString().

With this, you get the date-portion of the DateTime object, formatted as a string. See System.DateTime.ToShortDateString()

Upvotes: 1

Related Questions