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.

You'll need to materialize db.Order_Reports (e.g. .ToList()) before doing the projection, as EF can't parse the convert into a sql expression. — StuartLC
– StuartLC, Commented Jan 10, 2014 at 8:06
@Grundy That too shows error LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression. — Golda
– Golda, Commented Jan 10, 2014 at 8:08
@StuartLC - I have removed the .ToList() then it shows error at the time of bind grid- LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression. — Golda
– Golda, Commented Jan 10, 2014 at 8:12
Not related to your problem, but (a) why do you public fields and properties to access them and (b) why do your property names start with _ instead of the fields? — CompuChip
– CompuChip, Commented Jan 10, 2014 at 8:26

Volodymyr Baydalka · Accepted Answer · 2015-10-30 18:59:05Z

21

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

answered Oct 30, 2015 at 18:59

Volodymyr Baydalka

3,6451 gold badge15 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

qslabs Over a year ago

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.

i3arnon · Accepted Answer · 2014-06-03 10:41:19Z

6

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

edited Jun 3, 2014 at 10:41

answered Jan 10, 2014 at 8:05

i3arnon

117k33 gold badges334 silver badges358 bronze badges

3 Comments

Rune Antonsen Over a year ago

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 Over a year ago

@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 Over a year ago

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

Tunaki · Accepted Answer · 2015-11-28 17:12:32Z

0

DateTime myDateTime = Convert.ToDateTime(DateTimePicker.Text);

var kayitVarmi = (from myfTable in db.tbl_myfTable 
                              .
                              .
                              .
                              .                      
                              where
                                tbl_donemAyYil.donemAyYilKu == myDateTime

edited Nov 28, 2015 at 17:12

Tunaki

138k46 gold badges370 silver badges443 bronze badges

answered Nov 28, 2015 at 17:08

Halil

1

Collectives™ on Stack Overflow

LINQ Query to Convert string to datetime

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related