How to convert a string into a datetime in Linq to Entities query?

Question

My Linq to entities query is written as below. The datatype of DATECOLUMN1 in my ORACLE database is of string.

Datetime FilterStartDate = DateTime.Now;
            var query = from c in db.TABLE1
                        join l in db.TABLE2 on c.FK equals l.PK
                        where (FilterStartDate >= DateTime.ParseExact(l.DATECOLUMN1, "dd/MM/yyyy", CultureInfo.InvariantCulture) : false) == true
                        select c;

Writing above query gives me an error of not supported. How can I convert DATECOLUMN1 into a datetime to compare it.

P.S. I do not have control over database schema, so changing datatype of column in Oracle database is not a feasible solution for me.

it is in dd/MM/yyyy format

Nirman
– Nirman

2013-06-25 05:57:00 +00:00
Commented Jun 25, 2013 at 5:57 — Nirman
– Nirman, Commented Jun 25, 2013 at 5:57

Fabio S · Accepted Answer · 2013-06-25 10:27:45Z

1

In you Model, add the following property to your partial class TABLE2:

public DateTime DATECOLUMN1_NEW
{
    get
    {           
        return  DateTime.ParseExact(DATECOLUMN1, "dd/MM/yyyy", CultureInfo.InvariantCulture);
    }

    set { }
}

Then, in you LINQ query, use DATECOLUMN1_NEW (it's already in DateTime format) in place of DATECOLUMN1.

answered Jun 25, 2013 at 10:27

Fabio S

1,1226 silver badges8 bronze badges

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

1 Comment

Nirman Over a year ago

thanks, this is the trick.. lately, i realized there is no direct way to convert a string into datetime in L2E... thanks for this

Community · Accepted Answer · 2017-05-23 12:21:02Z

Erm.. I think the problem you are having is that you are putting ": false" in there.

It looks like you are trying to use a condtional operator (?:) but you forgot the "?".

I don't think you actually need this as you are just trying to determine if the date is greater or not. Also if ParseExact fails it will throw an exception (not what you want) so you should use TryParse instead and handle the true/false returned and the out value to determine whether or not the date is (a) Actually a date (b) less then FilterStartDate.

You can use two alternatives:

Use the function described in the answer here: How to I use TryParse in a linq query of xml data?
Use the following fluent syntax version which I think is more readable.

var query = db.Table1.Join(db.Table2, x => x.FK, y => y.PK, (x, y) => x).Where(x => { DateTime Result; DateTime.TryParse(x.Date, out Result); return DateTime.TryParse(x.Date, out Result) && FilterStartDate >= Result; });

Collectives™ on Stack Overflow

How to convert a string into a datetime in Linq to Entities query?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related