LINQ Query to Convert string to datetime

Question

I want to convert the string value to date time

Class

public class demoDate
{
    public DateTime DueDate;
    public int OrderReportID;

    public DateTime _DueDate 
    { 
        get { return DueDate; } 
        set { DueDate = value; } 
    }

    public int _OrderReportID 
    { 
        get { return OrderReportID; } 
        set { OrderReportID = value;} 
    }
}

Query

var DateQuery = (from o in db.Order_Reports
                 select new demoDate {
                    DueDate = System.DateTime.Parse(o.ReportDueDateTime), 
                    OrderReportID = o.OrderReportID
                 }).ToList();

This coding shows following 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.

Solution 1

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.ReportDueDateTime),
    OrderReportID= o.OrderReportID
});

Solution 2

If you need convert it with SQL you can try use SqlFunctions.DateAdd and just add zero interval.

var DateQuery = db.Order_Reports.Select(o => new demoDate {
    DueDate = SqlFunctions.DateAdd("day", 0, o.ReportDueDateTime), 
    OrderReportID = o.OrderReportID
 });

Comments

[Rune Antonsen]

Won't this load the entire table each time it's called? That might be A LOT of data if the table is big.

[i3arnon]

@RuneAntonsen very true. But unavoidable if you're using regular .Net methods that can't be translated to queries. It would probably be best to redesign this entirely.

[Giuseppe B]

or alternatively you can project your columns, then invoke ToList and then apply another select to create the instance

[qslabs]

This answer is really clever. It addresses circumstances where the conversion cannot be performed prior to the query while circumventing the need to load the dataset into memory or iterate over the dataset.