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

c# entity-framework linq

Question

I have a date in the string format in a table and I need to compare it with the parameter to retrieve all rows that match with the given date. how can I do this? I can't use DateTime.Parse and Convert.ToDateTime. both of them are not supported (I get an error). Is this possible at all in Linq OR do I have to write a stored procedure for it due to this limitation?

Note: I can't change the datatype of the column from varchar to DateTime, because that column contains other values as well, not just dates. it is a generic table and the column may contain different types of values based on the values in other columns. changing this design is outside the scope of this question.

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};

Update: if I use DateTime.Parse, I get the error "LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method, and this method cannot be translated into a store expression." similar error for Convert.ToDateTime as well.

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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

(Replicating my comment to paste some sample code)

If the date is in string format, couldn’t you apply a ToString on your DateTime (presumably x) and then do a string comparison?

Since you are working on string representations, you need to take care of several issues that would otherwise be handled transparently by DateTime, including:

  • Date-time format discrepancies (dd/MM/yyyy vs MM/dd/yyyy).
  • Presence or absence of leading zeros for single-digit days and months (e.g. 01/01/2011 vs 1/1/2001).
  • Two-digit or four-digit representation of years (e.g. 01/01/2011 vs 01/01/11).
  • Timezone offsets. For example, the date for 2011-01-01 23:30 -01:00 would actually be 2011-01-02.

The sample code below will work if all your dates are in US format, with two-digit days and months, four-digit years, and no timezone offsets.

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

Edit: Alternate solution:

If you can define a view in your database, you can circumvent the problem by casting your VARCHAR to DATETIME selectively. I’m assuming that Value is the name of your date column.

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

Then, in your LINQ, do a simple DateTime equality check:

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

This works. You need an Extensionmethod to make the dateTime parsing safe. After that you can use the result of that method in the Linq query. It will fetch all rows from the table so performance wise this might be a less optimal (!) solution. It answers the question though.

    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;
        }
    }
rene

Related Questions

How to convert an integer to a string using the ToString() function in LINQ

I resolved this issue by directly writing MySQl query inside C# as below - ...string queryTemplate = @"select inv.* from invoices as inv where userID = '123' and date like '%abc%'"; List<invoice> totalSearch = context.ExecuteStoreQuery<invoice>(queryTemplate).ToList();
c# entity entity-framework linq mysql
asked by CHash_Mike

DateTime to string conversion using LINQ

You can remedy your problem by projecting into an anonymous type, and then at a later step project into ...Post... after the data has already been returned from the DB....(from .... select new { /* stuff */, Date = c.Date }) .AsEnumerable() .Select(p => new Post { /* stuff */, PostingDate = p.Date.ToString() }) .ToList(); ...However, given that yo...
asp.net-mvc entity entity-framework linq
asked by Anthony Pegram

Using linq-to-entities to convert a string to a datetime has a significant problem.

Convert.ToDatetime... is supported by Linq2SQL. The only supported method of Linq to entities are these: ...http://msdn.microsoft.com/en-us/library/bb738681.aspx...about your problem ... try to convert ...startdate... and ...enddate... in string and compare the string value in linq expression.
.net c# entity-framework linq-to-entities winforms
asked by Max Zerbini

In a LINQ to SQL query, convert a datetime to a formatted text.

EDIT: Now that I understand the question, I'm giving it another shot :)...var offer = (from p in dc.CustomerOffer join q in dc.OffersInBranch on p.ID equals q.OfferID where q.BranchID == singleLoc.LocationID let value = (p.OriginalPrice - p.NewPrice) * 100 / p.O...
entity-framework linq linq-to-sql
asked by mccow002

How map a biginit inside entity framework, and how to convert a string into biginit

I have a field named assetid inside sql server with type Biginit.so how I can map this field inside entity framework, and convert a string to biginit. The biginit will be received as an XML string value such as:-...<operation> <operationstatus>Success</operationstatus> <resourcename>serverrr090909</resourcename> <assetid>2701</assetid> <message...
c# entity-framework
asked by Asp.netmvc Asp.netmvc

LINQ Query to Convert string to datetime

You need to first materialize the query (i.e. load the data) to your application and then parse it. Entity Framework doesn't know how to execute .Net methods (like ...DateTime.Parse...), it only knows how to translate them to ...SQL...var DateQuery = db.Order_Reports.ToList().Select(o => new demoDate { DueDate=DateTime.Parse(o.ReportDueDateTim...
.net c# datetime entity-framework linq
asked by i3arnon

In LINQ Query convert the string to datetime and check with today's date

I don't know a way to implement it as you describe it, because ...There's no function in SQL that will filter the dates in string format by inequality (at least those that work with your date format and are convertable from LINQ to SQL query)....You have a flaw in your database design which prevents "normal" methods from working...However, there ar...
c# datetime entity-framework linq
asked by DarkWanderer

DateTime or int to string inside IQueryable?

How do I convert DateTime or int to string inside IQueryable? I need to do something like this, but without ...ToString()..., which throws ...System.NotSupportedException... exception:...IQueryable filteredEntities = myIQueryableCollection .Where(o => o.SomeDateTime.ToString().Contains(searchParameter) || o.SomeInt.ToString().Contains(...
c# entity-framework entity-framework-6 iqueryable
asked by SoftwareFactor

How can I convert DateTime to String in Linq Query?

The way I generally solve this problem is by first just getting the data and then selecting it in memory'....var performancereviews = from pr in db.PerformanceReviews .Include(a => a.ReviewedByEmployee) .ToArray() .Select( ....); ...By ...
c# entity-framework linq linq-to-entities
asked by Haedrian

Converting string to datetime in mapping to viewmodel

you can use DateTime.ParseExact() and set your strict format. ...https://msdn.microsoft.com/en-us/library/w2sa9yss(v=vs.110).aspx...var datetime = DateTime.ParseExact(date, "MM/dd/yyyy", CultureInfo.InvariantCulture); ...it should give you something like that :...[ChildActionOnly] public PartialViewResult SomePartial() { var vm = _ctx.Something...
asp.net-mvc c# datetime entity-framework-6
asked by Demonia

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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