MB34 MB34 - 1 month ago 5
C# Question

Linq order by issue

I'm trying to order a result set by a

VARCHAR2
field that contains date time values. They are obtained from GoToWebinar like this and I can't convert them to
DateTime
:

9/29/2016 6:00:00 PM
8/31/16 5:48 PM
9/28/16 5:58 PM
9/14/16 5:57 PM
9/1/16 5:57 PM
9/21/16 5:52 PM
9/22/16 5:59 PM
9/15/16 5:59 PM
9/7/16 5:56 PM
9/8/16 5:55 PM


I need them sorted correctly like this:

8/31/16 5:48 PM
9/1/16 5:57 PM
9/7/16 5:56 PM
9/8/16 5:55 PM
9/14/16 5:57 PM
9/15/16 5:59 PM
9/21/16 5:52 PM
9/22/16 5:59 PM
9/28/16 5:58 PM
9/29/2016 6:00:00 PM


In trying to do this:

using (webinarAttendeesListDbContext context = new webinarAttendeesListDbContext(connectionString))
{
System.Linq.IQueryable<WebinarAttendeesList> result = null;
result = from w in context.WebinarAttendeesList
where webinarkeys.Contains(w.webinarKey) && w.AttendedWebinar > 0
orderby w.PID, DateTime.Parse(w.JoinTime)
select w;
r = result.ToList();
}


I get this error:


LINQ to Entities does not recognize the method 'System.DateTime
Parse(System.String)' method, and this method cannot be translated
into a store expression.


How do I need to do this order by?

Answer
  1. Use ToList() to retrieve the data and then order it:

    using (webinarAttendeesListDbContext context = new webinarAttendeesListDbContext(connectionString))
    {
        var result = (from w in context.WebinarAttendeesList
                      where webinarkeys.Contains(w.webinarKey) && w.AttendedWebinar > 0
                      select w).ToList()
                               .OrderBy(w => w.PID)
                               .ThenBy(w => DateTime.Parse(w.JoinTime));
    }
    
  2. Another option is to use the DbFunctions.CreateDateTime function.

  3. You can also use SqlFunctions.DateAdd like in this answer
Comments