Get custom string from SQL and turn into c# date object to sort by

I have a table that has a sql column with a date as a string like 'January 1, 2018'. I'm trying to turn that into a DateTime object in C# so I can use it to sort a list by. I'm currently grouping everything by the ID so I can return the highest revision. This is working great but I also need to OrderByDescending date from a the column that represents a date. The below code will order everything alphanumerically but I need to sort by DateTime.

    using (dbEntities entities = new dbEntities())
    {
        var db = entities.db_table
        .GroupBy(x => x.ID) //grouping by the id
        .Select(x => x.OrderByDescending(y => 
                     y.REVISIONID).FirstOrDefault());
       return db.OrderBy(e => e.Date_String).ToList(); 
    }

Thanks, I appreciate any help on this!

2 answers

  • answered 2018-03-20 18:43 StriplingWarrior

    You'll need to materialize the objects and use LINQ-to-Objects to do the conversion to a C# DateTime.

    return db.AsEnumerable().OrderBy(e => DateTime.Parse(e.Date_String)).ToList(); 
    

    If at all possible, I would strongly recommend changing your column to a datetime2 or datetimeoffset at the database level, though.

  • answered 2018-03-20 18:43 Falle1234

    If you don't mind some of the work being done on the client side you could do something like this:

    using (dbEntities entities = new dbEntities())
    {
        var db = entities.db_table
        .GroupBy(x => x.ID) //grouping by the id
        .Select(x => x.OrderByDescending(y => 
                     y.REVISIONID).FirstOrDefault()).ToList();
       return db.OrderBy(e => DateTime.Parse(e.Date_String)).ToList(); 
    }
    

    The parsing of the DateTime needs to be modified so it matches the format in the database, but otherwise it should work.