Getting the Average of a Calculated Item With Nullable DateTime

Question

Terrible title, I know...

This works and gives me what I'm looking for, but I know there must be a better way to do it:

var query = (from a in DB
             where a.Date.HasValue
             select a).AsEnumerable();

double avg = (from a in query
              select (a.Date.Value.Subtract(a.OtherDate).Days).Average();

Basically, a.Date is a nullable datetime, and I want to get the average of (a.Date - a.OtherDate) only when a.Date exists (or else the calculation wouldn't work).

Things like this won't work:

double avg = (from a in DB
              where a.Date.HasValue
              select a.Date.Value.Subtract(a.OtherDate).Days).Average();

I know I've seen similar problems where the solution was to explicitly set the nullable field to nullable datetime, making the Average method work. I haven't seen it where the nullable field was part of a calculation like this.

Thanks in advance.

Edit: To clear things up a bit, let me give you Ben's solution and the error message it generates. I have to admit I left another part out of it...a.OtherDate is actually a bit more tricky...let me illustrate:

var avg = DB.Where(a => a.Date.HasValue)
            .Where(a => a.ForeignKeyReference.Date.HasValue)
            .Select(a => a.Date.Value.Subtract(a.ForeignKeyReference.Date.Value).Days)
            .Average();

...and the error:

InvalidOperationException: Could not translate expression 'Table(DB).Where(a => a.Date.HasValue).Where(a => a.ForeignKeyReference.Date.HasValue).Select(a => a.Date.Value.Subtract(a.ForeignKeyReference.Date.Value).Days).Average()' into SQL and could not treat it as a local expression.

Seems as though the "Subtract" method or "Days" property has no translation to SQL. My first example worked because the second query was LINQ to objects. Does anyone know if there's a way to write the LINQ query in a way that will translate to SQL?

Answer 1

How about

double AvgDays = dates.Where(d => d.Date.HasValue)
                        .Average(dd => (dd.Date.Value-dd.OtherDate).Days);

but maybe something like this is more useful

var ticks = (long)dates.Where(d => d.Date.HasValue)
                        .Average(dd => (dd.Date.Value - dd.OtherDate).Ticks);

var avg = new TimeSpan(ticks);

Console.WriteLine("It's " + avg.Days + " days and " + avg.Hours + " hours and " + avg.Seconds + " seconds.");

Answer 2

Something like this definitely does the trick - I've verified that the output is correct using the 3.5 sp1 framework:

var avg = DB
    .Where(a => a.Date.HasValue)
    .Select(a => a.Date.Value.Subtract(a.OtherDate).Days)
    .Average();

The Where() operator ensures that any elements with a.Date having a null value will be excluded from the average; this is the behavior you've indicated that you wanted, correct?

Answer 3

Probably should have read the error message a bit closer. Looks like there just isn't a translation to SQL for the "Subtract" method, but you can in fact simply subtract one date from another to create a datetime like so:

var query = from a in DB
            select (a.Date.Value - a.ForeignKeyReference.Date.Value);

Meaning the solution was:

var query = (from a in DB
             where a.Date.HasValue
             where a.ForeignKeyReference.Date.HasValue
             let ts = (TimeSpan)(a.Date.Value - a.ForeignKeyReference.Value)
             select ts.Days).Average();

Jonas Elfström's response was right, I'm just adding this since it hits on the actual cause of the issue.