I'm trying to convert some SQL queries into Linq to avoid multiple trips to the database.
The old SQL I'm trying to convert does:
SELECT
AVG(CAST(DATEDIFF(ms, A.CreatedDate, B.CompletedDate) AS decimal(15,4))),
AVG(CAST(DATEDIFF(ms, B.CreatedDate, B.CompletedDate) AS decimal(15,4)))
FROM
dbo.A
INNER JOIN
dbo.B ON B.ParentId = A.Id
So I've created two C# classes:
class B
{
public Guid Id { get; set; }
public DateTime CreatedDate { get; set; }
public DateTime CompletedDate { get; set; }
}
class A
{
public Guid Id { get; set; }
public DateTime CreatedDate { get; set; }
public List<B> BList { get; set; }
}
And I've got a List<A>
object that I want to query. It's populated from the database, so each A in the list has a sub-list with a load of Bs. I want to use Linq-to-objects to query this list.
So I need to use Linq to get the average time between an A's start and the completion of its child Bs, and the average time between each B's start and completion. I didn't write the original SQL so I'm not entirely sure it does what it's supposed to!
I've got several of these averages to calculate, so I'd like to do them all inside one magical Linq query. Is this possible?