Menu
I have a table that looks like this:
FruitID | FruitType
23 | 2
215 | 2
256 | 1
643 | 3
I want to get the count by FruitType given a list of FruitIDs called TheFruitIDs. This is what I have:
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();
This code works but the problem is that sometimes I get a timeout error because the query runs for too long. How can I change this code to avoid the timeout problem?
811
The simplest way to do you query is to group by FruitType and then count the rows:
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);
This will efficiently create the following dictionary (assuming no data was excluded by the where part):
FruitType | Count ----------+------ 1 | 1 2 | 2 3 | 1
If you really want to collapse this into a single object having counts for specific fruit types you then have to create this object:
var TheCounter = new {
CountType1 = countsDictionary.ContainsKey(1) ? countsDictionary[1] : 0,
CountType2 = countsDictionary.ContainsKey(2) ? countsDictionary[2] : 0,
CountType3 = countsDictionary.ContainsKey(3) ? countsDictionary[3] : 0
};
There is another thing in your query that might be causing performance problems potentially resulting in timeouts: The list of fruit ID's in the where part is included in the query and if that list is very big it may slow down your query. There is nothing you can do about it unless you create this list from a previous query to the database. In that case you should try to avoid pulling the list of fruit ID's to the client side. Instead you should combine the query that selects the ID's with this query that counts the types. This will ensure that the entire query is executed server side.
You seem to be concerned about the structural change of the code. As long as you are creating anonymous objects it is hard to write reusable code. You could consider to just use the dictionary with the counts or something similar. Another option is to create a dynamic object with the counts. Personally, I do not like this solution but you may find it useful.
To simplify the code a class to store counts is needed:
class TypeCount {
public TypeCount(Int32 type, Int32 count) {
Type = type;
Count = count;
}
public Int32 Type { get; private set; }
public Int32 Count { get; private set; }
}
A dynamic object that has properties CountType0, CountType1, CountType2 etc. based on a sequence of tuples:
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;
}
}
An extension method to create the dynamic object:
static class CountExtensions {
public static dynamic ToCounts(this IEnumerable<TypeCount> typeCounts) {
return new CountsDictionary(typeCounts);
}
}
Putting it all together:
var counts = MyDC
.Fruits
.Where(f => TheFruitIDs.Contains(f.FruitID))
.GroupBy(
f => f.FruitType,
(fruitType, fruits) => new TypeCount(fruitType, fruits.Count())
)
.ToCounts();
You can then retrieve properties counts.CountType1, counts.CountType2 and counts.CountType3. Other count.CountType# properties will return 0. However, as counts is dynamic you will not get any intellisense.
100
This is what your query translates to:
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]
Note that for each grouping the IN is evaluated again, generating a very large workload for large lists of IDs.
You have to split the job in two steps.
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();
This translate to a much faster SQL. Note the ToList() at the end that force the execution of a single query.
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]
Now you can take the generated list and pivot it in memory to obtain your 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(),
};
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
