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.

What does `p.TimeTo` look like? Can't you just order by the string? — Gert Arnold, Aug 30 '12 at 06:58
1. You could maybe use SqlFunctions (http://msdn.microsoft.com/en-us/library/dd466176.aspx ). But could you show examples of TimeTo ? 2. If TimeTo represents Time values, why is it a Varchar in DB ? — Raphaël Althaus, Aug 30 '12 at 06:59
@RaphaëlAlthaus basically i m using sql server 2005 and u know that Time type is not available in this version — Muhammad Adnan, Aug 30 '12 at 08:51

score 1 · Answer 1 · answered Aug 30 '12 at 06:59

1

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.

answered Aug 30 '12 at 06:59

Habib

219,104
29
407
436

You may see this thread as well http://stackoverflow.com/questions/4146300/linq-to-entityframework-datetime – Habib Aug 30 '12 at 08:55
This is a dangerous work-around, what happens if ToList() return a huge amount of data? – Muhammad Adnan Aug 30 '12 at 09:30
@MuhammadAdnanSaleem, didn't you see the last line on my answer ? I already said that. This is the problem with this approach. Its upto you – Habib Aug 30 '12 at 09:32
Thanks Habib, but is there any other workaround?? or wait for the next release of linq and entity framework? :) – Muhammad Adnan Aug 30 '12 at 09:36
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/16016/discussion-between-muhammad-adnan-saleem-and-habib) – Muhammad Adnan Aug 30 '12 at 09:39
@MuhammadAdnanSaleem, its not there in LINQ and I don't think it can be there in future versions, because its hard to implement, The best approach would be to use `Time` data type in SQL server rather than varchar – Habib Aug 30 '12 at 09:40

Gert Arnold · Accepted Answer · 2012-08-30T14:16:36.657

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

edited Aug 30 '12 at 14:16

answered Aug 30 '12 at 12:15

Gert Arnold

105,341
31
202
291

I have 1:00 to 24:00 in the field. – Muhammad Adnan Aug 30 '12 at 12:59
i think not because you know that 11:00 and 10:00 have the same length so how we ensure that 10:00 come first???? – Muhammad Adnan Aug 30 '12 at 13:49
GREAT WORK GERT ARNOLD :) ACCEPTED – Muhammad Adnan Aug 30 '12 at 14:27

QueryExtender Linq Orderby using TimeSpan.Parse

2 Answers2