Sum of Timespans using LINQ to Entities

I have a group by LINQ call and then sum the PossessionTime.Tick values as a new Timespan. This worked fine before, but now I need to keep the collection as an IQueryable instead of a List.

PossessionTime = new TimeSpan(x.Sum(p => p.PossessionTime.Ticks))

With this change I now get the following error:

Only parameterless constructors and initializers are supported in LINQ to Entities.

I've looked into using DbFunctions but it doesn't have anything that can help I dont think.

Is my only option to move the list into memory and sum the time there?

The IQueryable is as follows:

return stats.GroupBy(x => x.Id).OrderByDescending(x => x.Sum(a => a.Kills))
            .Select(x => new WeaponItem
            {         
                PossessionTime = new TimeSpan(x.Sum(p => p.PossessionTime.Ticks))
            });

I need to keep it as an IQueryable as Im using it in pagination.

Solution

I don't think you'll be able to do this very easily with Linq-to-SQL/EF using straight linq becuase of PossionTime.Ticks. C# DateTime operations are very limited when translating into sql operations.

If your only requirement is to support pagination, you could make it easy on yourself by doing your WeaponItem projection after your pagination operation and have pulled you the page into memory:

return stats
   .GroupBy(x => x.Id)
   .OrderByDescending(x => x.Sum(a => a.Kills))
   // paginatation
   .Skip(pageNumber * pageSize)
   .Take(pageSize)
   // pull paged result set into memory to make life easy
   .ToList()
   // transform into WeaponItem
   .Select(x => new WeaponItem
   {         
        PossessionTime = new TimeSpan(x.Sum(p => p.PossessionTime.Ticks))
   });

Alternatively, you could change your WeaponItem class to store PossesionTimeTicks and then offer PossesionTime as a computed property. That might get around the restricton:

public class WeaponItem
{
    public long PosseionTimeTicks {get;set;}
    public TimeSpan PossesionTime {get{ return new TimeSpan(PosseionTimeticks); }
}

Then I think you should be able to keep your query as an IQueryable:

return stats
    .GroupBy(x => x.Id)
    .OrderByDescending(x => x.Sum(a => a.Kills))
    .Select(x => new WeaponItem
    {         
        PossessionTimeTicks = x.Sum(p => p.PossessionTime.Ticks)
    });