logo标签列表

Linq to SQL计算分组元素时出现超时问题

c#.netlinqlinq-to-sql
14

我有一个看起来像这样的表格：

FruitID | FruitType
  23    |    2
  215   |    2
  256   |    1
  643   |    3

我想统计给定TheFruitIDs列表中每个FruitType的数量。这是我的代码：

var TheCounter = (from f in MyDC.Fruits    
                  where TheFruitIDs.Contains(f.FruitID) 
                  group f by 0 into TheFruits
                  select new MyCounterMode()
                  {
                     CountType1 = (int?) TheFruits.Where(f => f.FruitType == 1).Count() ?? 0,
                     CountType2 = (int?) TheFruits.Where(f => f.FruitType == 2).Count() ?? 0,
                     .... all the way to CountType6      
                  }).Single();

这段代码能够工作，但问题在于有时候由于查询运行时间过长导致超时错误。我该如何修改代码以避免超时问题？

1
你确定这可以转换成一个查询吗？似乎投影可能会为每一行产生6个新的查询。这是真的吗？ - Wiktor Zychla
这并没有回答你的问题，但是Count()返回一个int而不是int?，即使转换为SQL；因此，空合并运算符是不必要的（可能甚至不起作用）。这是因为当强制转换为int?时，它永远不会有一个空值，因为0的值不是空值。我很惊讶这个代码在没有将转换括在额外一层括号中就能编译通过，但这可能是复制粘贴的事情。 - JNYRanger
1
你需要查看生成的 SQL 并在查询分析器中运行它。 - Magnus
“group f by 0 into TheFruits” 的意义是什么？ - leppie
“TheFruitIDs” 通常会包含多少个元素？ - Gert Arnold
@GertArnold：小于2000个（我已经因为2000项限制而受伤） - frenchie
7个回答
7

最简单的查询方法是按FruitType分组，然后计算行数：

var countsDictionary = MyDC
  .Fruits
  .Where(f => TheFruitIDs.Contains(f.FruitID))
  .GroupBy(
    f => f.FruitType,
    (fruitType, fruits) => new { FruitType = fruitType, Count = fruits.Count() }
  )
  .ToDictionary(c => c.FruitType, c => c.Count);

这将高效地创建以下字典（假设没有数据被 where 语句排除）: 
水果类型 | 数量
----------+------
1         | 1
2         | 2
3         | 1
如果你真的想把它合并为一个单一的对象，具有特定水果类型的计数，你需要创建这个对象:
var TheCounter = new {
  CountType1 = countsDictionary.ContainsKey(1) ? countsDictionary[1] : 0,
  CountType2 = countsDictionary.ContainsKey(2) ? countsDictionary[2] : 0,
  CountType3 = countsDictionary.ContainsKey(3) ? countsDictionary[3] : 0
};

你的查询中还有一件事可能会导致性能问题，从而导致超时：在where部分中包含了水果ID列表，如果该列表非常大，可能会减慢查询速度。除非您从先前的数据库查询中创建此列表，否则您无法对其进行任何操作。在这种情况下，您应该尝试避免将水果ID列表拉到客户端。相反，您应该将选择ID的查询与计数类型的查询组合起来。这将确保整个查询在服务器端执行。

您似乎担心代码的结构变化。只要您创建匿名对象，就很难编写可重用的代码。您可以考虑仅使用具有计数的字典或类似的东西。另一个选择是创建带有计数的动态对象。个人而言，我不喜欢这个解决方案，但您可能会发现它有用。

为了简化代码，需要一个存储计数的类：

class TypeCount {

  public TypeCount(Int32 type, Int32 count) {
    Type = type;
    Count = count;
  }

  public Int32 Type { get; private set; }

  public Int32 Count { get; private set; }

}

一个动态对象，它具有基于元组序列的属性CountType0、CountType1、CountType2等。
class CountsDictionary : DynamicObject {

  readonly IDictionary<Int32, Int32> counts;

  public CountsDictionary(IEnumerable<TypeCount> typeCounts) {
    if (typeCounts== null)
      throw new ArgumentNullException("typeCounts");
    this.counts = typeCounts.ToDictionary(c => c.Type, c => c.Count);
  }

  public override Boolean TryGetMember(GetMemberBinder binder, out Object result) {
    Int32 value;
    if (binder.Name.StartsWith("CountType") && Int32.TryParse(binder.Name.Substring(9), NumberStyles.None, CultureInfo.InvariantCulture, out value) && value >= 0) {
      result = this.counts.ContainsKey(value) ? this.counts[value] : 0;
      return true;
    }
    result = 0;
    return false;
  }

}

创建动态对象的扩展方法：

static class CountExtensions {

  public static dynamic ToCounts(this IEnumerable<TypeCount> typeCounts) {
    return new CountsDictionary(typeCounts);
  }

}

将它们组合起来：

将所有内容结合在一起：

var counts = MyDC
  .Fruits
  .Where(f => TheFruitIDs.Contains(f.FruitID))
  .GroupBy(
    f => f.FruitType,
    (fruitType, fruits) => new TypeCount(fruitType, fruits.Count())
  )
  .ToCounts();

您可以检索属性counts.CountType1，counts.CountType2和counts.CountType3。其他count.CountType#属性将返回0。但是，由于counts是动态的，您将无法获得任何智能感知。

@frenchie：分组和计数都将在服务器端执行。执行 ToList 或在这种情况下执行 ToDictionary，然后将包含水果类型和计数的行拉到客户端。我决定使用 ToDictionary 来使每个计数的查找更有效率，但如果只有几行，您也可以使用 ToList，然后在列表中执行线性搜索以获取每个计数。 - Martin Liversage
是的，但问题在于 MyCounterModel 在数据库中没有填充。我该如何像在我的问题查询中那样填充它？ - frenchie
@frenchie：你遇到超时问题是因为你的问题中的查询效率低下。我的答案（以及至少另一个答案）提供了我认为最有效的分组和计数记录的方法，不应该遇到超时问题。我不理解你关于“_MyCounterModel未在DB中填充”的说法。任何C#对象都存在，并且从数据库返回的表格数据在客户端上“填充”了它。你的查询返回一行六列，而我的查询返回两列六行。此外，你的查询会导致服务器超时。 - Martin Liversage
@frenchie：根据你目前的问题，我不明白为什么用两行代码来返回所需对象会成为问题。也许你可以进一步阐述你的问题，这样更容易理解你想要实现什么？ - Martin Liversage
@frenchie：你的需求似乎纯粹是美学上的。你正在进行一种类似于数据透视表的操作，将来自数据库的组计数作为行，并将其旋转为一个匿名对象，每行都有一个属性。这在可重用的方式下并不是真正可能的，除非你使用的不是匿名对象。我已经编辑了我的答案，提供了一种可能的解决方案。然而，就个人而言，我不会这样做。我宁愿坚持使用字典或类似字典的对象，然后在需要时查询计数。 - Martin Liversage
显示剩余4条评论
2

这是您的查询所翻译的内容：

SELECT
[Limit1].[C1] AS [C1],
[Limit1].[C2] AS [C2],
[Limit1].[C3] AS [C3],
[Limit1].[C4] AS [C4],
[Limit1].[C5] AS [C5],
[Limit1].[C6] AS [C6],
[Limit1].[C7] AS [C7]
FROM ( SELECT TOP (2)
    [Project13].[C1] AS [C1],
    CASE WHEN ([Project13].[C2] IS NULL) THEN 0 ELSE [Project13].[C3] END AS [C2],
    CASE WHEN ([Project13].[C4] IS NULL) THEN 0 ELSE [Project13].[C5] END AS [C3],
    CASE WHEN ([Project13].[C6] IS NULL) THEN 0 ELSE [Project13].[C7] END AS [C4],
    CASE WHEN ([Project13].[C8] IS NULL) THEN 0 ELSE [Project13].[C9] END AS [C5],
    CASE WHEN ([Project13].[C10] IS NULL) THEN 0 ELSE [Project13].[C11] END AS [C6],
    CASE WHEN ([Project13].[C12] IS NULL) THEN 0 ELSE [Project13].[C13] END AS [C7]
    FROM ( SELECT
        [Project12].[C1] AS [C1],
        [Project12].[C2] AS [C2],
        [Project12].[C3] AS [C3],
        [Project12].[C4] AS [C4],
        [Project12].[C5] AS [C5],
        [Project12].[C6] AS [C6],
        [Project12].[C7] AS [C7],
        [Project12].[C8] AS [C8],
        [Project12].[C9] AS [C9],
        [Project12].[C10] AS [C10],
        [Project12].[C11] AS [C11],
        [Project12].[C12] AS [C12],
        (SELECT
            COUNT(1) AS [A1]
            FROM [dbo].[Fruits] AS [Extent13]
            WHERE ([Extent13].[FruitID] IN (23, 215, 256, 643)) AND ([Project12].[C1] = 0) 
            AND (6 = [Extent13].[FruitType])) AS [C13]
        FROM ( SELECT
            [Project11].[C1] AS [C1],
            [Project11].[C2] AS [C2],
            [Project11].[C3] AS [C3],
            [Project11].[C4] AS [C4],
            [Project11].[C5] AS [C5],
            [Project11].[C6] AS [C6],
            [Project11].[C7] AS [C7],
            [Project11].[C8] AS [C8],
            [Project11].[C9] AS [C9],
            [Project11].[C10] AS [C10],
            [Project11].[C11] AS [C11],
            (SELECT
                COUNT(1) AS [A1]
                FROM [dbo].[Fruits] AS [Extent12]
                WHERE ([Extent12].[FruitID] IN (23, 215, 256, 643)) 
                AND ([Project11].[C1] = 0) 
                AND (6 = [Extent12].[FruitType])) AS [C12]
            FROM ( SELECT
                [Project10].[C1] AS [C1],
                [Project10].[C2] AS [C2],
                [Project10].[C3] AS [C3],
                [Project10].[C4] AS [C4],
                [Project10].[C5] AS [C5],
                [Project10].[C6] AS [C6],
                [Project10].[C7] AS [C7],
                [Project10].[C8] AS [C8],
                [Project10].[C9] AS [C9],
                [Project10].[C10] AS [C10],
                (SELECT
                    COUNT(1) AS [A1]
                    FROM [dbo].[Fruits] AS [Extent11]
                    WHERE ([Extent11].[FruitID] IN (23, 215, 256, 643)) 
                    AND([Project10].[C1] = 0) 
                    AND (5 = [Extent11].[FruitType])) AS [C11]
                FROM ( SELECT
                    [Project9].[C1] AS [C1],
                    [Project9].[C2] AS [C2],
                    [Project9].[C3] AS [C3],
                    [Project9].[C4] AS [C4],
                    [Project9].[C5] AS [C5],
                    [Project9].[C6] AS [C6],
                    [Project9].[C7] AS [C7],
                    [Project9].[C8] AS [C8],
                    [Project9].[C9] AS [C9],
                    (SELECT
                        COUNT(1) AS [A1]
                        FROM [dbo].[Fruits] AS [Extent10]
                        WHERE ([Extent10].[FruitID] IN (23, 215, 256, 643))
                        AND ([Project9].[C1] = 0) 
                        AND (5 = [Extent10].[FruitType])) AS [C10]
                    FROM ( SELECT
                        [Project8].[C1] AS [C1],
                        [Project8].[C2] AS [C2],
                        [Project8].[C3] AS [C3],
                        [Project8].[C4] AS [C4],
                        [Project8].[C5] AS [C5],
                        [Project8].[C6] AS [C6],
                        [Project8].[C7] AS [C7],
                        [Project8].[C8] AS [C8],
                        (SELECT
                            COUNT(1) AS [A1]
                            FROM [dbo].[Fruits] AS [Extent9]
                            WHERE ([Extent9].[FruitID] IN (23, 215, 256, 643)) 
                            AND ([Project8].[C1] = 0) 
                            AND (4 = [Extent9].[FruitType])) AS [C9]
                        FROM ( SELECT
                            [Project7].[C1] AS [C1],
                            [Project7].[C2] AS [C2],
                            [Project7].[C3] AS [C3],
                            [Project7].[C4] AS [C4],
                            [Project7].[C5] AS [C5],
                            [Project7].[C6] AS [C6],
                            [Project7].[C7] AS [C7],
                            (SELECT
                                COUNT(1) AS [A1]
                                FROM [dbo].[Fruits] AS [Extent8]
                                WHERE ([Extent8].[FruitID] IN (23, 215, 256, 643)) 
                                AND ([Project7].[C1] = 0) 
                                AND (4 = [Extent8].[FruitType])) AS [C8]
                            FROM ( SELECT
                                [Project6].[C1] AS [C1],
                                [Project6].[C2] AS [C2],
                                [Project6].[C3] AS [C3],
                                [Project6].[C4] AS [C4],
                                [Project6].[C5] AS [C5],
                                [Project6].[C6] AS [C6],
                                (SELECT
                                    COUNT(1) AS [A1]
                                    FROM [dbo].[Fruits] AS [Extent7]
                                    WHERE ([Extent7].[FruitID] IN (23, 215, 256, 643)) 
                                    AND ([Project6].[C1] = 0) 
                                    AND (3 = [Extent7].[FruitType])) AS [C7]
                                FROM ( SELECT
                                    [Project5].[C1] AS [C1],
                                    [Project5].[C2] AS [C2],
                                    [Project5].[C3] AS [C3],
                                    [Project5].[C4] AS [C4],
                                    [Project5].[C5] AS [C5],
                                    (SELECT
                                        COUNT(1) AS [A1]
                                        FROM [dbo].[Fruits] AS [Extent6]
                                        WHERE ([Extent6].[FruitID] IN (23, 215, 256, 643)) 
                                        AND ([Project5].[C1] = 0) 
                                        AND (3 = [Extent6].[FruitType])) AS [C6]
                                    FROM ( SELECT
                                        [Project4].[C1] AS [C1],
                                        [Project4].[C2] AS [C2],
                                        [Project4].[C3] AS [C3],
                                        [Project4].[C4] AS [C4],
                                        (SELECT
                                            COUNT(1) AS [A1]
                                            FROM [dbo].[Fruits] AS [Extent5]
                                            WHERE ([Extent5].[FruitID] IN (23, 215, 256, 643)) 
                                            AND ([Project4].[C1] = 0) 
                                            AND (2 = [Extent5].[FruitType])) AS [C5]
                                        FROM ( SELECT
                                            [Project3].[C1] AS [C1],
                                            [Project3].[C2] AS [C2],
                                            [Project3].[C3] AS [C3],
                                            (SELECT
                                                COUNT(1) AS [A1]
                                                FROM [dbo].[Fruits] AS [Extent4]
                                                WHERE ([Extent4].[FruitID] IN (23, 215, 256, 643)) 
                                                AND ([Project3].[C1] = 0) 
                                                AND (2 = [Extent4].[FruitType])) AS [C4]
                                            FROM ( SELECT
                                                [Project2].[C1] AS [C1],
                                                [Project2].[C2] AS [C2],
                                                (SELECT
                                                    COUNT(1) AS [A1]
                                                    FROM [dbo].[Fruits] AS [Extent3]
                                                    WHERE ([Extent3].[FruitID] IN (23, 215, 256, 643)) 
                                                    AND ([Project2].[C1] = 0) 
                                                    AND (1 = [Extent3].[FruitType])) AS [C3]
                                                FROM ( SELECT
                                                    [Distinct1].[C1] AS [C1],
                                                    (SELECT
                                                        COUNT(1) AS [A1]
                                                        FROM [dbo].[Fruits]AS [Extent2]
                                                        WHERE ([Extent2].[FruitID] IN (23, 215, 256, 643)) 
                                                        AND ([Distinct1].[C1] = 0) 
                                                        AND (1 = [Extent2].[FruitType])) AS [C2]
                                                    FROM ( SELECT DISTINCT
                                                        0 AS [C1]
                                                        FROM [dbo].[Fruits]AS [Extent1]
                                                        WHERE [Extent1].[FruitID] IN (23, 215, 256, 643)
                                                    )  AS [Distinct1]
                                                )  AS [Project2]
                                            )  AS [Project3]
                                        )  AS [Project4]
                                    )  AS [Project5]
                                )  AS [Project6]
                            )  AS [Project7]
                        )  AS [Project8]
                    )  AS [Project9]
                )  AS [Project10]
            )  AS [Project11]
        )  AS [Project12]
    )  AS [Project13]
)  AS [Limit1]

请注意，对于每个分组，IN操作符都会被重新评估，这样在ID列表很长的情况下会产生非常大的工作量。
你需要将任务分为两个步骤。
List<int> theFruitIDs = new List<int> { 23, 215, 256, 643 };

var theCounter = (from f in MyDC.Fruits
                  where theFruitIDs.Contains(f.FruitID)
                  group f by f.FruitType into theFruits
                  select new { fruitType = theFruits.Key, fruitCount = theFruits.Count() })
                  .ToList();

这将转换为更快的SQL。注意结尾处的ToList()会强制执行单个查询。

SELECT
[GroupBy1].[K1] AS [FruitType],
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
    [Extent1].[FruitType] AS [K1],
    COUNT(1) AS [A1]
    FROM [dbo].[Fruits] AS [Extent1]
    WHERE [Extent1].[FruitID] IN (23, 215, 256, 643)
    GROUP BY [Extent1].[FruitType]
)  AS [GroupBy1]

现在，您可以将生成的列表在内存中进行透视，以获取您的MyCounterMode。
var thePivot = new MyCounterMode
                {
                    CountType1 = theCounter.Where(x => x.fruitType == 1).Select(x => x.fruitCount).SingleOrDefault(),
                    CountType2 = theCounter.Where(x => x.fruitType == 2).Select(x => x.fruitCount).SingleOrDefault(),
                    CountType3 = theCounter.Where(x => x.fruitType == 3).Select(x => x.fruitCount).SingleOrDefault(),
                };
不，我需要将分组和计数器对象填充到数据库中，而不是在内存中。 - frenchie
我提出和解释的都是在数据库上完成的。列表“theCounter”只是一个包含六个元素的列表，第二个分组“thePivot”只是将这六行转置为六列。 - deramko
问题在于，在某些查询中，我使用相同的模式来填充计数器作为更大查询的子查询，因此我需要将计数器查询编写为一个独立的查询。 - frenchie
我已经对此进行了评论。我可以再详细解释一下：查询是错误的，它会引发异常。生成的 SQL 与您的原始示例几乎完全相同，因此这并没有改进。顺便说一句，要查看由 Linq 语句生成的 SQL，请使用以下代码：MyDC.Database.Log = Console.WriteLine; - deramko
1
看起来你展示的SQL是由Entity Framework生成的。这个问题标记为[linq-to-sql]，它生成的SQL略有不同。然而，我确信你对生成的SQL的整体结构的结论是正确的。 - Martin Liversage
显示剩余4条评论
1

你的LINQ为每个计数生成单独的SQL，所以你需要使用TheFruits来计算你的项目数量。

尝试这个

var TheCounter = (from f in MyDC.Fruits    
                  where TheFruitIDs.Contains(f.FruitID) 
                  group new {f.FruitType} by f.FruitType into TheFruits
                  select new MyCounterMode()
                  {
                     CountType1 = TheFruits.Count(f => f.FruitType == 1),
                     CountType2 = TheFruits.Count(f => f.FruitType == 2),
                     .... all the way to CountType6      
                  }).Single();
这看起来很不错，因为分组和计数是在数据库中完成而不是在内存中完成。 - frenchie
此查询将生成6行数据，然后Single运算符将引发异常。 - deramko
1
你可以在内存中执行 group by。将多个计数与 group by 结合使用会生成大量子查询，性能可能相当差。
var tempResult = (from f in MyDC.Fruits where TheFruitIDs.Contains(f.FruitID)).ToList();

var TheCounter = (from f in tempResult
                  group f by f.FruitType into TheFruits
                  select new MyCounterMode()
                  {
                     CountType1 = (int?) TheFruits.Count(f => f.FruitType == 1),
                     CountType2 = (int?) TheFruits.Count(f => f.FruitType == 2),
                     .... all the way to CountType6      
                  }).Single();
虽然这通常适用于LINQ-to-SQL，但在这里，“group by 0”仅创建一个组和一个查询。尽管如此，我认为在内存中进行分组（透视）更好，这样数据库就可以专注于生成数据。 - Gert Arnold
不，我需要将分组和计数器对象填充到数据库中，而不是在内存中。 - frenchie
0

你需要记住，select会在每次迭代中执行！

所以尝试类似以下的代码：

'var TheCounter = (from f in MyDC.Fruits     
                  group f by f.FruitID into TheFruits
                  select new KeyValuePair<int, int>(TheFruits.Key,TheFruits.Count())).ToDictionary(r=>r.Key,r=>r.Value);'

这将会给你一个带有：键 - FruitId，值 - Count 的字典

0

这是我通常实现它的方式（我构建了一个简单的控制台程序来演示）：

Fruit.cs

public class Fruit
{
    public Fruit(int fruitId, int fruitType)
    {
        FruitId = fruitId;
        FruitType = fruitType;
    }

    public int FruitId { get; set; }
    public int FruitType { get; set; }
}

Program.cs

class Program
{
    static void Main(string[] args)
    {
        // Data
        var fruits = new List<Fruit>
        {
            new Fruit(23, 2),
            new Fruit(215, 2),
            new Fruit(256, 1),
            new Fruit(643, 3)
        };

        // Query
        var query = fruits
            .GroupBy(x => x.FruitType)
            .Select(x => new {Name = x.Key, Total = x.Count()});

        // Output
        foreach (var item in query)
        {
            Console.WriteLine(item.Name + ": " + item.Total);
        }
        Console.ReadLine();
    }
}

你需要关注的是query。使用GroupBy后，你将得到一组列表。对于每个组，Key是分组的标准（这里是FruitType）。然后，我们调用Count()来获取该组中元素的数量。
0
这里有一种动态的方式可以做到这一点，使你不受 CountType 数字的限制：
int typesOfCounts = 6;

IEnumerable<Fruit> theCounter = fruitList.Where(x => theFruitIDs.Contains(x.FruitID));

Dictionary<string, int> myCounterMode = new Dictionary<string, int>();

for (var i = 1; i < typesOfCounts + 1; i++)
{
    string counterType = "CountTypeX";
    counterType = counterType.Replace("X", i.ToString());

    myCounterMode.Add(counterType, theCounter.Count(x => x.FruitType == i));
}

return myCounterMode;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接