CODEWITHSUNDEEP
c#sqlentity-frameworklinq
dmod40
dmod40

Reputation: 59

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!

Upvotes: 2

Views: 203

Answers (2)

Falle1234
Falle1234

Reputation: 5063

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.

Upvotes: 0

StriplingWarrior
StriplingWarrior

Reputation: 156524

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.

Upvotes: 3

Related Questions