LinqToEntities can convert a string to a datetime (inside the query)

c# entity-framework linq

Question

I need to compare a date in a database that is in string format with the parameter in order to extract all rows that match the specified date. How can I go about this? I am unable to utilize DateTime. Convert and parse. ToDateTime. They are not both supported (I get an error). Is this even remotely doable with Linq, or do I have to create a stored method because of this restriction?

Due to the fact that the column includes more than simply dates, it is not possible for me to alter the datatype from varchar to DateTime. It is a generic table, therefore depending on the values in the other columns, the column may contain various kinds of data. This inquiry is not about modifying this design.

sample code ("x" is the date parameter passed into this method):

    from t1 in Table1
    where EntityFunctions.DiffDays(DateTime.Parse(t1.Value),x) == 0
    select new {t1.Col1, t1.Col2};

The error message "LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method, and this method cannot be translated into a store expression" appears when I use DateTime.Parse. identical problem also occurs with Convert.ToDateTime.

1
3
1/31/2012 7:37:24 PM

Accepted Answer

(I'm copying and pasting my remark here to include some example code.)

Couldn't you use a string conversion if the date is in that format?ToString to youDateTime (presumably x ) after which do a string comparison?

Since you are dealing with string representations, you must address a number of problems that would otherwise be transparently addressed byDateTime consisting of:

  • Differences in date and time formats (dd/MM/yyyy vs MM/dd/yyyy ).
  • the presence or absence of leading zeros for months and days with a single digit (e.g.01/01/2011 vs 1/1/2001 ).
  • Years represented by two or four digits (e.g.01/01/2011 vs 01/01/11 ).
  • time zone differences. For instance, the deadline for2011-01-01 23:30 -01:00 would in fact be2011-01-02 .

If all of your dates are in US format with two-digit days and months, four-digit years, and no timezone offsets, the example code shown below will function.

from t1 in Table1
where t1.Value.StartsWith(x.ToString(@"MM\/dd\/yyyy"))
select new {t1.Col1, t1.Col2};

Edit: Another approach:

If you can create a database view, you can get around the issue by casting yourVARCHAR to DATETIME selectively. My assumption is thatValue your date column's name should be.

SELECT CONVERT(DATE, Value, 101) AS Value, Col1, Col2
FROM Table1
WHERE ISDATE(Value) = 1

Do a basic LINQ query after that.DateTime equality test

from t1 in Table1
where t1.Value == x.Date
select new {t1.Col1, t1.Col2};
1
1/31/2012 8:04:15 PM

Popular Answer

That works. For the dateTime parsing to be secure, you need an Extensionmethod. The outcome of that procedure may then be used in the Linq query. This may not be the best (!) answer in terms of speed since it will get every row from the database. But it offers a response to the query.

    void Main()
    {
         var stringDates = new List<string> { "2011-13-01", "2011-01-12" };

         DateTime paramDate = new DateTime(2011,01,13);

         var q = from stringDate in stringDates
            let realdate = stringDate.SafeParse()
            where realdate == paramDate
            select new { stringDate, realdate };

         q.Dump();
    }


    static class StringDateParseExt
    {
       public static DateTime SafeParse(this string  any)
        {
          DateTime parsedDate;
          DateTime.TryParseExact(any, 
                "yyyy-dd-MM", 
                System.Globalization.CultureInfo.InvariantCulture , 
                System.Globalization.DateTimeStyles.None, 
                out parsedDate);
          return parsedDate;
        }
    }


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow