CODEWITHSUNDEEP
oracle-databaseentity-frameworklinq-to-entities
Nirman
Nirman

Reputation: 6793

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

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.

Upvotes: 0

Views: 1569

Answers (2)

Dave Williams
Dave Williams

Reputation: 2246

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:

  1. Use the function described in the answer here: How to I use TryParse in a linq query of xml data?

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

Upvotes: 0

Fabio S
Fabio S

Reputation: 1122

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.

Upvotes: 1

Related Questions