LINQ的 - 在一个查询中计算平均倍数 [英] Linq - calculate multiple averages in one query

问题描述

我想一些SQL查询转换为LINQ到避免多次往返到数据库。

I'm trying to convert some SQL queries into Linq to avoid multiple trips to the database.

旧的SQL我试图转换所做的：

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

所以，我创建了两个C＃类：

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; }
}

和我有一个列表< A> ; 我要查询的对象。它从数据库中填入，因此，在列表中的每个A具有子清单与货币的负荷。我想使用LINQ到对象查询此列表中。

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.

所以，我需要使用LINQ获得A的开始和其子完成之间的平均时间BS，而且每个B的开始和完成之间的平均时间。我没有写原来的SQL，所以我不能完全肯定它做什么，它应该！

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!

我有几个这样的平均数来计算的，所以我倒是喜欢做的所有这些是一个神奇的Linq查询中。这可能吗？

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?

推荐答案

更有趣的问题是如何做到这样的事情在服务器上，例如使下面的查询转换为LINQ到SQL。

The more interesting question would be how to do such a thing on the server, for example to make the following query translate to LINQ to SQL.

        var q = from single in Enumerable.Range(1, 1)
                let xs = sourceSequence
                select new
                {
                    Aggregate1 = xs.Sum(),
                    Aggregate2 = xs.Average(),
                    // etc
                };

但是，没有任何一点努力，如果你使用LINQ to对象将它塞进一个查询。刚分开来写的集合体：

But there's no point trying to cram it into one query if you're using LINQ to Objects. Just write the aggregates separately:

var aggregate1 = xs.Sum();
var aggregate2 = xs.Average();
// etc

在换句话说：

var xs = from a in listOfAs
         from b in a.Bs
         select new { A=a, B=b };

var average1 = xs.Average(x => (x.B.Completed - x.A.Created).TotalSeconds);
var average2 = xs.Average(x => (x.B.Completed - x.B.Created).TotalSeconds);

我才明白吧？

Did I understand right?

编辑大卫乙同样的回答。我忘了时间跨度转换为用平均法支持一个简单的数字类型。使用TotalMilliseconds /秒/分钟或其他规模是适当的。

David B answered similarly. I forgot to convert the TimeSpan to a simple numeric type supported by the Average method. Use TotalMilliseconds/Seconds/Minutes or whatever scale is appropriate.

这篇关于LINQ的 - 在一个查询中计算平均倍数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！