Doing Regular Old Database Programming (RODP) with LINQ to Entities

 

ICC has me on a project where I am essentially writing a service backend to a video-enabled LMS of sorts.  I need to track interactions with a set of videos, and present completion percentages by user, video or category.

I am storing every interaction wit hthe video for every user, with a start second and a stop second.  So if you log in and watch a video, and start at Seconds 100 and stop at Seconds 240, I record that.  Since the front end software won’t let you go past where you last stopped, the highest Stopped figure for a given video and user is the total watched minutes – even if they replayed something.

To do this, I created a public PercentComplete method for each service, and then created private TotalSeconds and SecondsWatched methods for each as well.  Here is the common PercentComplete method, that calls the two private methods.

public static double PercentComplete(Guid userId, int videoId)
{
double result = 0.0;
int elapsed = WatchedSeconds(userId, videoId);
int total = TotalAvailableSeconds(videoId);
try
{
result = Math.Round(Convert.ToDouble(elapsed) / Convert.ToDouble(total), 2);
}
catch (DivideByZeroException)
{
result = 0;
}
return result;
}

To calculate TotalSeconds, I just needed to add up all of the lengths for all of the videos.  That was easy enough.  The length is stored in the database, and available to the entity model.

private static int TotalAvailableSeconds(int videoId)
{
VirtualVideoEntities context = new VirtualVideoEntities();
var videoData = context.Videos.FirstOrDefault(c => c.VideoId == videoId);
return Convert.ToInt32(videoData.Length);
}

Calculating the watched seconds was another matter, and would have to be custom to each entity.  Video seemed the easiest – what percent of a given video has a given user watched?  I can just get the Stopped value from all of the Interactions in the database, then get Max, right?

private static int WatchedSeconds(Guid userId, int videoId)
{
using (VirtualVideoEntities context = new VirtualVideoEntities())
{
var watchedSeconds = from c in context.Interactions
where c.User == userId && c.Video == videoId
select c.Stopped;
int sumSeconds = watchedSeconds.Max().GetValueOrDefault();
return sumSeconds;
}
}

Jammin.  Now, how about for a User?  Now I need to get all of the max values for all of the videos and sum them.  That’s harder, but it can be done with a GroupBy (hat tip to @jimwooley and @craigstuntz).

private static int WatchedSeconds(Guid userId)
{
using (VirtualVideoEntities context = new VirtualVideoEntities())
{
var maxWatchedSeconds = from c in context.Interactions
where c.User == userId && c.Stopped != null
group c by c.Video into g
select new {Video = g.Key, MaxStopped =
(from t2 in g select t2.Stopped).Max()};
int sumSeconds = maxWatchedSeconds.Sum(m => m.MaxStopped).GetValueOrDefault();
return sumSeconds;
}
}

Right on.  Now, categories.  Uh, how am I going to do that?  In SQL, I would use a JOIN on Category with the VideoId, but I’m not USING SQL.  Seems weird to use a Join in LINQ but it does have one … hmm.  Not sure what to do here.

Then I though – wait a minute.  I remember someone saying “If you have to use a Join in L2E, your entity model isn’t right.  So the context.Video should have a Category collection, right?  I tried to add a conditional of c.Category but after the c I pressed dot … and got nothing.  Bummer. Makes sense though.  Interactions don’t have categories.

Then I deleted the dot, and intellisense for c came up.  There was ‘Videos.’  Boom.  I selected Videos, then dot, then there was Category.  Amazing.

private static int WatchedSeconds(Guid userId, int categoryId)
{
VirtualVideoEntities context = new VirtualVideoEntities();
var maxWatchedSeconds = from c in context.Interactions
where c.User == userId && c.Videos.Category == categoryId
group c by c.Videos into g
select new { Video = g.Key, MaxStopped =
(from t2 in g select t2.Stopped).Max() };
int sumSeconds = maxWatchedSeconds.Sum(m => m.MaxStopped).GetValueOrDefault();
return sumSeconds;
}

I am sold on Linq.  I still don’t think it makes a good ORM, like Linq2SQL tries to be, but I am totally sold on using it for object manipulation when a domain model is present.  I won’t use anything else, unless I have to.

Comments are closed

Bill Sempf

Husband. Father. Pentester. Secure software composer. Brewer. Lockpicker. Ninja. Insurrectionist. Lumberjack. All words that have been used to describe me recently. I help people write more secure software.

PageList

profile for Bill Sempf on Stack Exchange, a network of free, community-driven Q&A sites

MonthList