Linq: Sort by Date when its stored as text

Question

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.

Answer 1

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.