QueryExtender Linq Orderby using TimeSpan.Parse

Question

Basically I have a QueryExtender control of ASP.NET and First I need to Convert sql varchar value to TimeSpan of CSharp type then apply Linq OrderBy Clause on it, but get an error when execute.

Here is my code:

  protected void FilterProducts(object sender, CustomExpressionEventArgs e)
    {
      e.Query = (from p in e.Query.Cast<accounts>()
                  select p).OrderBy(p=> TimeSpan.Parse(p.TimeTo));
    }

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

Answer 1

Without knowing all about the shape of your p.TimeTo data I think you can use the string value to order by, this way:

from p in e.Query.Cast<accounts>()
select p).OrderBy(p => p.TimeTo.Length).ThenBy(p => p.TimeTo)

This way, a value 1:00 will be sorted before 11:00.

EDIT

Take:

var s = new[] { "12:10", "8:00", "8:20",  "1:00", "1:02", "10:00", "11:10" };

And see the difference between

s.OrderBy (x => x);

and

s.OrderBy (x => x.Length).ThenBy (x => x);

Which is:

1:00
1:02
10:00
11:10
12:10
8:00
8:20

and

1:00
1:02
8:00
8:20
10:00
11:10
12:10

Answer 2

That is because TimeSpan.Parse is not available on the store, your query gets translated into SQL if you are using SQLServer, and that store doesn't have TimeSpan.Parse, one approach would be convert it to a list and then query again.

e.Query = (from p in e.Query.Cast<accounts>()
           select p).ToList().OrderBy(p=> TimeSpan.Parse(p.TimeTo));

But the above solution may cause some problem if ToList returns huge set of data.