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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

Related Questions

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

I fixed this problem by explicitly putting the following MySQl query inside of C#:...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

By projecting into an anonymous type and then afterwards projecting into a specific type, you may solve your issue....Post... once the DB has already sent the data back....(from .... select new { /* stuff */, Date = c.Date }) .AsEnumerable() .Select(p => new Post { /* stuff */, PostingDate = p.Date.ToString() }) .ToList(); ...However, given that y...
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... Linq2SQL supports this. These are the only supported Linq to Entities methods: ...http://msdn.microsoft.com/en-us/library/bb738681.aspx...about your issue, try to convert...startdate... and ...enddate... in string and use a linq expression to compare the value of the string.
.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: I'm going to try again now that I understand the question....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.Original...
entity-framework linq linq-to-sql
asked by mccow002

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

Inside SQL Server, I have a field of the Biginit type called assetid. So tell me how to convert a string to a biginit within of entity framework and map this field. As an XML string value, the biginit will be received as follows:-...<operation> <operationstatus>Success</operationstatus> <resourcename>serverrr090909</resourcename> <assetid>2701</...
c# entity-framework
asked by Asp.netmvc Asp.netmvc

LINQ Query to Convert string to datetime

Prior to parsing the query, you must materialize it, or load the data into your application. Entity Framework lacks execution skills. Net techniques, like...DateTime.Parse... ), it only understands how to convert them to...SQL...var DateQuery = db.Order_Reports.ToList().Select(o => new demoDate { DueDate=DateTime.Parse(o.ReportDueDateTime), ...
.net c# datetime entity-framework linq
asked by i3arnon

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

I'm not sure how to put it into practice the manner you suggest, because...The dates in string format can't be filtered by inequality using a SQL function (at least those that work with your date format and are convertable from LINQ to SQL query)....Your database design has a fault that prohibits "normal" procedures from functioning....There are, h...
c# datetime entity-framework linq
asked by DarkWanderer

DateTime or int to string inside IQueryable?

ZZZ_tmp
c# entity-framework entity-framework-6 iqueryable
asked by SoftwareFactor

How can I convert DateTime to String in Linq Query?

I typically approach this challenge by first simply gathering the facts and then choosing it from memory....var performancereviews = from pr in db.PerformanceReviews .Include(a => a.ReviewedByEmployee) .ToArray() .Select( ....); ...By u...
c# entity-framework linq linq-to-entities
asked by Haedrian

Converting string to datetime in mapping to viewmodel

Use DateTime is an option. Set your strict format using ParseExact(). ...https://msdn.microsoft.com/en-us/library/w2sa9yss(v=vs.110).aspx...var datetime = DateTime.ParseExact(date, "MM/dd/yyyy", CultureInfo.InvariantCulture); ...You ought to receive something similar to this:...[ChildActionOnly] public PartialViewResult SomePartial() { var vm =...
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