CODEWITHSUNDEEP
sql-server-2008entity-framework-4linq-to-entities
Tom Styles
Tom Styles

Reputation: 1082

How to OrderBy Date stored in a Varchar field in EF4

We have a legacy database (SQLServer 2008) with thousands of rows in it. Each record has a logdate field which is a date but stored as a varchar in the format 21/04/2010 16:40:12.

We only need to return the rows where the logdate is in the future, and order them by date. We could pull back all the rows and filter on the server but this seems wrong and won't scale.

Is there a way of doing the filtering and ordering in Entity Framework 4.

This is what we thought might work but it's failed. 

from c in db.changes
where [DateTime]c.logdate > DateTime.Today()
orderby [DateTime]c.logdate
select c;

Any help is appreciated.

Upvotes: 1

Views: 205

Answers (3)

HLGEM
HLGEM

Reputation: 96572

The order in a varchar field will be considerably different than the order in date field. Fix your structure to correctly store dates or add an additional date field that is populated through a trigger. Likely you have bad dates in there as well since there are no controls on a varchar field to diallow dates from being put in. You will need to fix these as well.

Upvotes: 0

Craig Stuntz
Craig Stuntz

Reputation: 126547

You can't parse a string into a date on the DB server with any built-in L2E function.

You can:

  1. map a DB function yourself,
  2. write SQL and execute it with ObjectContext.ExecuteStoreQuery, or
  3. fix the metadata.

I'd pick the latter, if it were me.

Upvotes: 2

Mark S. Rasmussen
Mark S. Rasmussen

Reputation: 35476

I'm not sure you can do it through pure LINQ unless you create your own LINQ functions. If you can execute an ad-hoc query and have EF4 translate it back into objects, like you can on the DataContext.Translate LINQ2SQL method, you can convert it like this:

CONVERT(datetime, logdate, 103)

And thus your query would be:

SELECT
    *
FROM
    changes
WHERE
    CONVERT(datetime, logdate, 103) > GETDATE()
ORDER BY
    CONVERT(datetime, logdate, 103)

Alternatively, if you can add to the schema (I assume you can't modify the varchar column to store it as a datetime natively), you could add a computed column like so:

ALTER TABLE
    changes
ADD
    logdateDatetime AS CONVERT(datetime, logdate, 103) PERSISTED

And then query the logdateDatetime column instead of logdate.

Upvotes: 1

Related Questions