CODEWITHSUNDEEP
c#.netlinqlinq-to-entitiestimespan
Bad Dub
Bad Dub

Reputation: 1593

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.

Upvotes: 3

Views: 3410

Answers (2)

NetMage
NetMage

Reputation: 26926

You can create a TimeSpan from HMS using DBFunctions.CreateTime, so you could do math to convert:

return stats.GroupBy(x => x.Id).OrderByDescending(x => x.Sum(a => a.Kills))
            .Select(x => new WeaponItem
            {         
                PossessionTime = DBFunctions.CreateTime(0, 0, x.Sum(p => DBFunctions.DiffSeconds(p.PossessionTime, TimeSpan.Zero)))
            });

Note: I am assuming CreateTime will accept a seconds argument >= 60 seconds, if not, you would need to convert the sum to hours, minutes and seconds.

Upvotes: 1

Philip Pittle
Philip Pittle

Reputation: 12305

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)
    });

Upvotes: 2

Related Questions