Reputation: 51947
Linq to SQL count grouped elements generating a timeout
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?
Upvotes: 14
Views: 1420
Answers (7)
Reputation: 106836
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.
Upvotes: 7
Reputation: 4815
Here's a dynamic way to do it where you're not limited by 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;
Upvotes: 0
Reputation: 12085
Here is how I always implement this (I build a simple console program to demonstrate):
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();
}
}
The one you need to focus in is query. After using GroupBy, you will have a list of groups. For each group, the Key is the criteria to group (here is the FruitType). Then, we call Count() to get the number of element in that group.
Upvotes: 0
Reputation: 12855
You can do the group by in-memory. Combining a group by with multuple counts will generate lots of subqueries, which can perform quite badly.
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();
Upvotes: 1
Reputation: 2835
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(),
};
Upvotes: 2
Reputation: 799
your LINQ generate saperate sql for each count, so u need to use TheFruits to count your items
try this
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();
Upvotes: 1
Reputation: 57
You need to remember that select is being executed for each iteration!!
So try something like:
'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);'
This will give you a dictionary with: Key- FruitId, Value- Count
Upvotes: 0
Related Questions
- Complicated SQL query to Linq
- Grouped Counts With linq
- Iterate through LINQ to SQL query containing COUNT and GROUP BY
- Group and Count in Linq issue
- grouping and counting in linq-to-sql
- Counting grouped data with Linq to Sql
- LINQ to SQL .Count takes way to much process time and decreases performances
- counting items in grouped results in Linq to Sql
- Grouping and count with LinqToSQL
- LINQ and .COUNT timing out