CODEWITHSUNDEEP
entity-framework
Doug
Doug

Reputation: 6518

Calculating Average Timespan between two dates in Entity Framework

I have a database table with two columns: StartDateTime and FinishDateTime. both are nullable datetime columns.

I'm wanting to calculate the Average time between both fields per row. ie the average duration of my recorded event.

I'm getting a "DbArithmeticExpression arguments must have a numeric common type."

Example EF code with a touch of simplification for the demo.

from p in new DbContext()
where p.user_id = 123
&& p.StartDateTime != null
&& p.FinishDateTime != null
select new {p.StartDateTime, p.FinishDateTime})
.Average(p=> (p.FinishDateTime.Value - p.StartDateTime.Value).Ticks)

I'd love an example of the above, as SQL makes this a breeze.

Upvotes: 0

Views: 1750

Answers (1)

George
George

Reputation: 787

Its depends on your data provider, it may support DbFunctions and you could do something like this:

(from p in new DbContext()
where p.user_id = 123
&& p.StartDateTime != null
&& p.FinishDateTime != null
select new {p.StartDateTime, p.FinishDateTime})
.Average(x=> DbFunctions.DiffMilliseconds(p.FinishDateTime,p.StartDateTime))

if it doesn't, i think you have to go linq to objects after the select:

(from p in new DbContext()
where p.user_id = 123
&& p.StartDateTime != null
&& p.FinishDateTime != null
select new {p.StartDateTime, p.FinishDateTime})
.ToArray()
.Average(x=> (p.FinishDateTime -p.StartDateTime).Ticks)

Upvotes: 2

Related Questions