CODEWITHSUNDEEP
c#sql-server-2008linq-to-entities
Luke101
Luke101

Reputation: 65268

Linq: Sort by Date when its stored as text

I need to sort by date but the date is stored as text in the database. I am using Linq to entities to perform queries.

The way the database is designed it is not feasible to change the column to a date column because many different data types are in that column. There is a descriminator column named type so I will know what type a particular row is.

Upvotes: 5

Views: 912

Answers (2)

Jay
Jay

Reputation: 57939

You can add a computed column to the table that will convert those strings to dates when your discriminator has a specific value (here I've just used 'date').

ALTER TABLE Foo
ADD trueDate AS
    CASE 
        WHEN type = 'date' THEN CONVERT(date, 'mixedColumn', 101)
        ELSE NULL
    END
PERSISTED

If you have time information, then date should be datetime in the CONVERT() function.

Also, the 101 is a style code indicating an expected format of MM/dd/yyyy. If you have something different, refer to this: http://msdn.microsoft.com/en-us/library/ms187928.aspx, but keep in mind that if you use a style below 100 your expression will be considered non-deterministic and you cannot make your computed column PERSISTED, so the conversions will be done on the fly with each query (you don't want that).

The computed column will update itself when the row values change; otherwise the values are persisted and queryable just like in any other column. No triggers required.

Upvotes: 2

TDaver
TDaver

Reputation: 7264

If all your rows containing dates use the same date format you could just order by their string value. But since the most common (i.e english) date format does NOT start with the year, that could prove problematic.

Option A.: Sort in memory.

var records = db.YourTable.Where(o=>o.Discriminator =="date").AsEnumerable()
    .Select(o=>new {Entity= o, Date=DateTime.Parse(o.YourColumn)})
    .OrderBy(o.Date).Select(o=>o.Entity);

Do NOT do this if you have a lot of rows, or if you have to, at least have the decency to cache the ordered result...

Option B.: Database magic
Add an extra column to your DB, make THAT Date (nullable), and update that if the discriminator is date. You can either update it in a (DML) Trigger or from C#...
OR if the conversion is simple you could build a view out of it...

Upvotes: 0

Related Questions