CODEWITHSUNDEEP
c#entity-frameworksumtimespanentity-framework-core
Chris
Chris

Reputation: 637

Entiity Framework 7 / LINQ - Get sum of timespan objects

I'm trying to build a new web app in VS2015 using the RC of the new asp.net and Entity Framework 7.

One thing I would like to be able to display is a "total of timespan" columns.

I'm trying to find a way of being able to retrieve the total duration across all video data that has been uploaded (I'm using NReco and FFProbe to get the media info from the file, which is where I get the duration from to upload to the DB)

When I'm uploading video data to a database, one of the properties in my Video model is a TimeSpan object.

I've been trawling the net and SO for a while, some of the suggested solutions I have tried haven't worked (more details below)

For context, here is my model

public class Video
{
    public int Id { get; set; }
    public DateTime DateOfVideo { get; set; }
    public string Road { get; set; }
    public string RegistrationNumber { get; set; }
    public List<Keyword> Keywords { get; set; }
    public TimeSpan VideoDuration { get; set; }
    public string User { get; set; }
    public DateTime UploadDate { get; set; }
}

and my controller

public class HomeController : Controller
{
    private readonly UserManager<ApplicationUser> _userManager;
    private readonly IHostingEnvironment _environment;
    private readonly CcContext _context;

    public HomeController(UserManager<ApplicationUser> userManager, IHostingEnvironment environment, CcContext context)
    {
        _userManager = userManager;
        _environment = environment;
        _context = context;
    }
    ... 
    Various controller actions and methods removed for brevetiy 
    ...
}

Things I've tried so far

based on this SO answer

 var minutesOfVideo = new TimeSpan(_context.Videos.Sum(v => v.VideoDuration.Ticks));

but throws an

An exception of type 'System.InvalidCastException' occurred in EntityFramework.Core.dll but was not handled in user code

Additional information: Specified cast is not valid.

I've also tried to do this

var minutesOfVideo  = _context.Videos.Aggregate(TimeSpan.Zero, (sumSoFar, nextMyObject) => sumSoFar + nextMyObject.VideoDuration);

but this throws an

An exception of type 'System.NotImplementedException' occurred in EntityFramework.Core.dll but was not handled in user code

Additional information: Remotion.Linq.Clauses.ResultOperators.AggregateFromSeedResultOperator

Is this even possible or do I need to look at writing either some Linq->Sql or some native SQL

I appreciate any answers of if this is doable

EDIT

Based on @Ricky's suggestion (thanks by the way)

If I try

var minutesOfVideo = _context.Videos.Select(v => v.VideoDuration).ToArray();

I get An exception of type 'System.InvalidCastException' occurred in EntityFramework.Core.dll but was not handled in user code, trying 

var minutesOfVideo = _context.Videos.Select(v => v.VideoDuration).ToArray().Sum();

I get compiler errors

'TimeSpan[]' does not contain a definition for 'Sum' and the best extension method overload 'AsyncEnumerable.Sum(IAsyncEnumerable)' requires a receiver of type 'IAsyncEnumerable'

I'm starting to wonder if its worth me removing the timespan property from my video model and replacing it with two int's for hours and minutes...

Upvotes: 1

Views: 1339

Answers (2)

Ricky
Ricky

Reputation: 10751

Usually the EF 7 will try to convert your LINQ expressions into SQL statement. Although it has limited capabilities that combine the database query and class properties accessing.

I suggest you may query all VideoDuration from database, ToArray then invoke Sum over the Array:

_context.Videos.Select(v => v.VideoDuration)
    .ToArray()
    .Select(vd => vd.TotalMinutes)
    .Sum() // the return value is of type double

Upvotes: 1

Chris
Chris

Reputation: 637

So, I ended up taking the approach of editing my Video model with two new properties

public int VideoMinutes { get; set; }
public int VideoSeconds { get; set; }

then from NReco.GetMediaInfo I get my TimeSpan object, from that I populate my two new fields as properties from the constructed TimeStamp object.

That then allows me to do the following in my HomeController

        var totalMinsOfVideo = _context.Videos.Select(v => v.VideoMinutes).Sum();
        var totalSecsOfVideo = _context.Videos.Select(v => v.VideoSeconds).Sum();
        var total = new TimeSpan(0, totalMinsOfVideo, totalSecsOfVideo);

It may not be the cleanest solution but this is providing me what I need currently.

Upvotes: 0

Related Questions