C# LINQ Datetime in a String Value

Question

I have a database with a string column that indicates a datetime value with this format: yyyyMMdd.

For example the value 20160908 indicate the 08 of Semptember 2016.

I have two datetimepicker for filter dateFrom and dateTo value. I take the datetime value in my datepicker textbox with this simply code:

DateTime dataFromSel = Convert.ToDateTime(txtDatFrom.Text);
DateTime dataToSel = Convert.ToDateTime(txtDatTo.Text);

My query is:

var query = from c in snd.WineOWines.OrderByDescending(x => x.DDT_DATA)
   select new
   {
      c.ID,
      c.VABRMA,
      c.VABNCL,
      c.DDT_DATA,
   };

If I have a datetime filter i add this code:

if (txtDatDa.Text != "")
{
    string dataDaSel = Convert.ToDateTime(txtDatDa.Text).ToString("yyyyMMdd");
    int dataDa = Convert.ToInt32(dataDaSel);
    query = query.Where(x => int.Parse(x.DDT_DATA) >= dataDa);
}

The problem is that i can't to list the query before the filter because i have a lot of rows and if i use this query i can't do an int.parse in the LINQ statement.

How can i write a LINQ statement that select the row in my DB with datetime between from and to, if the value in the column is a string?For now my query works fine, but i need a where clause for this problem.

Thanks to all

Answer 1

If dates have the same format you do not need to cast them to int. You should be able to compare stings and remove the cast...

if (!string.IsNullOrEmpty(txtDatDa.Text))
{
    string dataDaSel = Convert.ToDateTime(txtDatDa.Text).ToString("yyyyMMdd");
    var res = query.Where(x => string.Compare(dataDaSel, x.Name) <= 0);
}

Linq to SQL supports string.Compare(string, string) as described here https://social.msdn.microsoft.com/Forums/en-US/98180ae0-4ccd-4ecd-89d5-576a04169219/linq-to-entities-with-string-comparison?forum=adodotnetentityframework

Answer 2

You don't have to put int.Parse, you can do a direct string comparison it is going to work ok. Neither you have to convert your dataDaSel into integer.

if (txtDatDa.Text != "")
{
    string dataDaSel = Convert.ToDateTime(txtDatDa.Text).ToString("yyyyMMdd");
    query = query.Where(x => x.DDT_DATA >= dataDaSel);
}

E.g.

"20120201" >= "20120201"   // true
"20120101" >= "20120201"   // false
"20120301" >= "20120201"   // true

As long as you keep format as yyyyMMdd it is going to work ok even with string.