Linq to SQL对生成超时的分组元素进行计数
本文关键字：元素 SQL to 超时 Linq | 更新日期: 2023-09-27 18:07:24
我有一个表，看起来像这样:
FruitID | FruitType
23 | 2
215 | 2
256 | 1
643 | 3
我想获得FruitType的计数给定FruitIDs的列表称为TheFruitIDs。这是我的文件:
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();
这段代码工作，但问题是，有时我得到一个超时错误，因为查询运行太长时间。如何更改此代码以避免超时问题?
查询的最简单方法是按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部分排除):
如果你真的想将它折叠成一个包含特定水果类型计数的单一对象，那么你必须创建这个对象:
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是动态的，您将无法获得任何智能感知。
这是你的查询翻译成:
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]
现在您可以使用生成的列表并在内存中pivot以获得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(),
};
您的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();
您可以在内存中执行group by。将一个组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();
您需要记住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);'
这将给你一个字典:Key- FruitId, Value- Count
我总是这样实现的(我构建了一个简单的控制台程序来演示):
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()获取该组元素的个数。
这是一种不受counttype# 's:
限制的动态方法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;