Группировка элементов Linq to SQL сгенерирует тайм-аут
У меня есть таблица, которая выглядит так:
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();
Этот код работает, но проблема в том, что иногда я получаю ошибку таймаута, потому что запрос выполняется слишком долго. Как я могу изменить этот код, чтобы избежать проблемы с таймаутом?
Ответы
Ответ 1
Самый простой способ сделать запрос - группировать по 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):
FruitType | Count
----------+------
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 включен в запрос, и если этот список очень большой, он может замедлить ваш запрос, Вы ничего не можете с этим поделать, если не создадите этот список из предыдущего запроса в базу данных. В этом случае вы должны стараться избегать вытягивания списка идентификаторов фруктов на стороне клиента. Вместо этого вы должны комбинировать запрос, который выбирает идентификатор с этим запросом, который учитывает типы. Это обеспечит выполнение всего запроса на стороне сервера.
Вы, похоже, обеспокоены структурным изменением кода. Пока вы создаете анонимные объекты, трудно написать многоразовый код. Вы могли бы просто использовать словарь с подсчетами или что-то подобное. Другой вариант - создать динамический объект со счетами. Лично мне не нравится это решение, но вы можете найти его полезным.
Для упрощения кода необходим класс для хранения счетчиков:
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 является динамическим, вы не получите никакой intellisense.
Ответ 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 оценивается снова, генерируя очень большую рабочую нагрузку для больших списков идентификаторов.
Вы должны разделить работу в два этапа.
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(),
};
Ответ 3
ваш LINQ генерирует saperate 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();
Ответ 4
Вы можете сделать 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();
Ответ 5
Вы должны помнить, что 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
Ответ 6
Вот как я всегда реализую это (я создаю простую консольную программу для демонстрации):
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(), чтобы получить число элементов в этой группе.
Ответ 7
Здесь динамический способ сделать это, когда вы не ограничены 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;
