CODEWITHSUNDEEP
c#sql-serverentity-frameworkentity-framework-6
Jaxidian
Jaxidian

Reputation: 13511

How do I average DateTime values in SQL Azure with Entity Framework?

I have a SQL query that needs to average many datetime values server-side (in SQL Server). For example purposes, let's just consider it's a simple query like this on a table with millions of records:

SELECT
    SomeField,
    AVG(CAST(ADateTime AS decimal(18,8))) AS NumericRepresentation
FROM MyTable
GROUP BY SomeField

As shown, I can't simply take AVG(ADateTime) because SQL Server isn't happy with doing that, but converting it to a Decimal (and later converting it back to a DateTime) works well enough for me.

The obvious way to do something comparable with EntityFramework is to use .Select(tbl => tbl.ADateTime.Ticks).Average(), but this fails at runtime because DateTime.Ticks doesn't translate through Linq-to-Entities.

How should I best go about this? My main need is to average datetime values in some way. The temporary representation (decimals, ticks, etc) isn't terribly important as long as it can be translated back to a DateTime either in SQL or .NET code. What is important, though, is that the averaging is done in SQL Server (I have some fairly complex calculations with this over many records) and I can somehow have the translated DateTime in .NET (whether the translation happens in SQL Server or in .NET, I don't care).

Upvotes: 0

Views: 448

Answers (2)

Ross Presser
Ross Presser

Reputation: 6255

EF LINQ expressions can make use of SqlFunctions class to make sure the conversion happens correctly.

DateTime minDate = new DateTime(1900,1,1);
var avg = MyTable.Select(myrow => SQLFunctions.DateDiff("second",myrow.ADateTime,minDate).Average();
DateTime avgDate = minDate.AddSeconds(avg);

Previous answer, should be disregarded:

Use Convert.ToDouble. EntityFramework should be able to translate this LINQ to SQL is able to CONVERT(float,...) as long as your column is actually a DateTime and not DateTime2 or DateTimeOffset, but unfortunately Entity Framework is not able to recognize this construct.

.Select(tbl => Convert.ToDouble(tbl.ADateTime)).Average()

An alternate choice is to do it client side:

.Select(tbl => tbl.ADateTime).ToArray().Select(dt => dt.Ticks).Average()

though clearly that's not preferred if you're averaging millions of rows.

Upvotes: 1

tgralex
tgralex

Reputation: 814

In pure SQL you can do average on a date field with something like this:

-- the smallest date you could possibly have in your data
DECLARE @MinDate DATE = '1/1/1900' 
SELECT
    SomeField,
    DATEADD(DAY, AVG(DATEDIFF(DAY, @MinDate, ADateTime)), @MinDate) as AvgDateTime
FROM MyTable
GROUP BY SomeField

Not sure yet how to translate this to LINQ :)

UPD: Here is the LINQ code:

private static void Test(IQueryable<SomeClass> data)
    {
        var minDate = DateTime.MinValue;
        var avgMilliseconds = data.Select(x => x.SomeDateField.Subtract(minDate).TotalMilliseconds).Average();
        var avgDate = minDate.AddMilliseconds(avgMilliseconds);
        Console.WriteLine(avgMilliseconds);
        Console.WriteLine(avgDate);
    }

Upvotes: 3

Related Questions