LINQ query NotSupportedException OrderBy Average of TimeSpan

Question

var seasonPlayer = (from SeasonPlayer in db.SeasonPlayerSet 
                    orderby SeasonPlayer.StatisticsPlayer.Average(x => x.STP_timeplay.Ticks) descending 
                    select SeasonPlayer).ToList();

SeasonPlayer has an ICollection of StatisticsPlayer so i want to get a average of time spent on the court ordered descending by STP_timeplay which is a typ of TimeSpan. I can't get average by STP_timeplay because it isn't a decimal so i tried get average by Ticks. It throws an exception:

The specified type member 'Ticks' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

score 1 · Answer 1 · answered Jan 05 '15 at 04:55

1

Try this:-

var seasonPlayer = db.SeasonPlayerSet.ToList()
                     .OrderByDescending(x => x.StatisticsPlayer
                                             .Average(z => z.STP_timeplay.Ticks);

answered Jan 05 '15 at 04:55

Rahul Singh

21,585
6
41
56

I got "Sequence contains no elements" when added .ToList() at the end to get ICollection of SeasonPlayer. – luki9696 Jan 05 '15 at 06:41
@luki9696 - You have added `ToList()` to table or SeasonPlayer? Please note that message means there is no data in collection. – Rahul Singh Jan 05 '15 at 07:02
It was my bad. I tried the solution which gave me StuartLC and i got the same error. I corrected his solution and now i get correct result. I tried to add the same condition to your solution but I don't know how to add it correctly. It would be nice to see also your idea. – luki9696 Jan 05 '15 at 07:39

score 1 · Accepted Answer · answered Jan 05 '15 at 04:58

1

The problem is that the Linq to Entities query provider isn't able to translate your LINQ into a Sql query which joins to the Statistics Player, averages the timeplay, grouped by season player.

Given that you appear to be iterating all Season Players, if the number of records isn't too large you could bring this all into memory like so:

var seasonPlayer = db.SeasonPlayerSet
                      .Include(sp => sp.StatisticsPlayer)
                      .ToList()
                      .Select(sp => new {SeasonPlayer = sp, Average = sp.StatisticsPlayer.Average(stp => stp.STP_timeplay.Ticks)})
                      .OrderByDescending(sp => sp.Average)
                      .Select(sp => SeasonPlayer)
                      .ToList();

answered Jan 05 '15 at 04:58

StuartLC

104,537
17
209
285

I corrected `Include(sp => sp.StatisticsPlayer)` to `Include("StatisticsPlayer")` because it returned error. And I didn't notice that some of SeasonPlayer don't have StatisticsPlayer yet so it returned "Sequence contains no elements", I have added a condition `Average = sp.StatisticsPlayer.Count() == 0 ? 0 : sp.StatisticsPlayer.Average(stp => stp.STP_timeplay.Ticks)`. Now it's all right. Thx for help! – luki9696 Jan 05 '15 at 07:38
Glad to be of help. The [strongly typed `Include` overload](http://stackoverflow.com/questions/6102909/entity-framework-include-strongly-typed) is preferable -you just need to add `using System.Data.Entity;`. Also, good catch that Average needs some data to work with. – StuartLC Jan 05 '15 at 08:36
1

Following your advice I changed back to `Include(sp => sp.StatisticsPlayer)` with `using System.Data.Entity;`. Now it's also work. Thx for this hint! – luki9696 Jan 05 '15 at 08:54

LINQ query NotSupportedException OrderBy Average of TimeSpan

2 Answers2