Linq Query efficiency

Question

Hello,

Any ideas on how to make the below more efficient? It is taking too long. The lstAllCombs has 150,000 records.

        List<SpecLevel> lstRankRangeSumRankPartition = lstAllCombs

.GroupBy(p => (p.RankRange0Count, p.RankRange1Count, p.RankRange2Count, p.RankRange3Count, p.RankRange4Count, p.TotalSum, p.TotalFrequency))

.Select(group => new SpecLevel

{

RankRange0Count = group.Key.RankRange0Count,

RankRange1Count = group.Key.RankRange1Count,

RankRange2Count = group.Key.RankRange2Count,

RankRange3Count = group.Key.RankRange3Count,

RankRange4Count = group.Key.RankRange4Count,

RankSum = group.Key.TotalFrequency,

TotalSum = group.Key.TotalSum,

Counter = group.Count()

}).ToList();

        lstRankRangeSumRankPartition = UpdateListByPercentage(lstRankRangeSumRankPartition);

        foreach (var ItemSpecs in lstRankRangeSumRankPartition)

        {

            if (ItemSpecs.Percentage > 50)

                break;

            string CombFilter = " RankRange0Count == " + ItemSpecs.RankRange0Count.ToString() +

" && RankRange1Count == " + ItemSpecs.RankRange1Count.ToString() +

" && RankRange2Count == " + ItemSpecs.RankRange2Count.ToString() +

" && RankRange3Count == " + ItemSpecs.RankRange3Count.ToString() +

" && RankRange4Count == " + ItemSpecs.RankRange4Count.ToString() +

" && TotalFrequency == " + ItemSpecs.RankSum.ToString() +

" && TotalSum == " + ItemSpecs.TotalSum.ToString();

            List<CombinationFrequency> FilteredSubset = InitialData.AsQueryable().Where(CombFilter).ToList();

            FilteredData.AddRange(FilteredSubset);

        }

Answer 1

Set-Based Operations vs. Procedural Loops

Relational database management systems (RDBMS) like SQL Server are fundamentally optimized for set-based operations.

The LINQ/C# Approach: Iterating through items in a foreach loop and querying or filtering collections one by one forces procedural execution. Even with an in-memory dictionary lookup reducing complexity to $O(M + N)$, the application layer is still doing heavy lifting that the database engine can execute natively in parallel.

The Stored Procedure Approach: A stored procedure allows the database engine to generate an optimal execution plan, leverage indexes efficiently, and process the filtering and aggregation in a single, cohesive round-trip.

Eliminating Network I/O and Memory Pressure

Pulling entire tables or massive subsets (like 150,000 unnormalized rows) into application RAM causes unnecessary memory allocation, triggers garbage collection pressure, and wastes bandwidth moving data across the network stack.

Pushing the logic down to a stored procedure ensures that only the final, filtered result set travels back to the application.

Fixing the Underlying Schema (Denormalization)

Columns named RankRange0Count, RankRange1Count, through RankRange4Count violate fundamental normalization principles (specifically avoiding repeating groups/horizontal splitting).

The Maintenance Trap: If business requirements change tomorrow to support RankRange5Count, a horizontal schema design forces you to alter table definitions, rewrite C# models, update LINQ projections, and modify every query.

The Normalized Alternative: A properly normalized schema shifts those ranges into a related child table (CombinationRanges with a RangeIndex and Count). A stored procedure can then easily query and aggregate this child table using standard relational JOINs or conditional aggregation (PIVOT/GROUP BY), keeping the schema flexible, clean, and indexed correctly.

Alternative Approach:

Rather than tweaking the C# code to squeeze better performance out of a bad memory pattern:

Move Aggregation to SQL: Write a stored procedure that accepts parameters or performs the initial GROUP BY and frequency counts directly inside the database engine.

Batch the Filtering: Instead of looping through thresholds in C# and hitting the dataset iteratively, write a set-based query or temporary table join inside the stored procedure that filters rows meeting the criteria in a single execution pass.

Address the Schema: Restructure the repeating RankRangeXCount columns into a proper normalized child table if long-term maintainability and indexing performance are priorities.

Answer 2

Hello @Naji Afache ,

Thanks for your question.

The slowness might be happening because the loop searches the entire InitialData list from the very beginning for every single item, using a text string (CombFilter).

I recommend using a LINQ Join. This approach attempts to line up both lists and find matches in a single step, which might help avoid searching the same data multiple times.

You can refer to these code example:

var targetSpecs = lstRankRangeSumRankPartition.Where(x => x.Percentage <= 50);

var FilteredData = InitialData.Join(
    targetSpecs,
    data => new { data.RankRange0Count, data.RankRange1Count, data.RankRange2Count, data.RankRange3Count, data.RankRange4Count, data.TotalSum, RankSum = data.TotalFrequency },
    spec => new { spec.RankRange0Count, spec.RankRange1Count, spec.RankRange2Count, spec.RankRange3Count, spec.RankRange4Count, spec.TotalSum, RankSum = spec.RankSum },
    (data, spec) => data
).ToList();

Note: this assumes lstRankRangeSumRankPartition is already ordered so that Percentage <= 50 selects the same specs your original break did. If ordering matters in the output, add an OrderBy at the end.

I hope this addresses your question. If this response was helpful, please consider following the guidance to provide feedback. Thank you.

Answer 3

The main issue is likely not the initial GroupBy. LINQ can have performance costs, but the expensive part here is the repeated filtering inside the foreach, especially this pattern:

InitialData.AsQueryable().Where(CombFilter).ToList()

That re-runs a query for every item in lstRankRangeSumRankPartition, and the dynamic string-based predicate adds extra overhead.

A more efficient approach is to group or index InitialData once using the same composite key used in the first query, then look up matching subsets instead of scanning repeatedly.

Example shape:

var groupedInitialData = InitialData
    .GroupBy(x => (
        x.RankRange0Count,
        x.RankRange1Count,
        x.RankRange2Count,
        x.RankRange3Count,
        x.RankRange4Count,
        x.TotalFrequency,
        x.TotalSum))
    .ToDictionary(g => g.Key, g => g.ToList());

var lstRankRangeSumRankPartition = lstAllCombs
    .GroupBy(p => (
        p.RankRange0Count,
        p.RankRange1Count,
        p.RankRange2Count,
        p.RankRange3Count,
        p.RankRange4Count,
        p.TotalSum,
        p.TotalFrequency))
    .Select(group => new SpecLevel
    {
        RankRange0Count = group.Key.RankRange0Count,
        RankRange1Count = group.Key.RankRange1Count,
        RankRange2Count = group.Key.RankRange2Count,
        RankRange3Count = group.Key.RankRange3Count,
        RankRange4Count = group.Key.RankRange4Count,
        RankSum = group.Key.TotalFrequency,
        TotalSum = group.Key.TotalSum,
        Counter = group.Count()
    })
    .ToList();

lstRankRangeSumRankPartition = UpdateListByPercentage(lstRankRangeSumRankPartition);

foreach (var itemSpecs in lstRankRangeSumRankPartition)
{
    if (itemSpecs.Percentage > 50)
        break;

    var key = (
        itemSpecs.RankRange0Count,
        itemSpecs.RankRange1Count,
        itemSpecs.RankRange2Count,
        itemSpecs.RankRange3Count,
        itemSpecs.RankRange4Count,
        itemSpecs.RankSum,
        itemSpecs.TotalSum);

    if (groupedInitialData.TryGetValue(key, out var filteredSubset))
    {
        FilteredData.AddRange(filteredSubset);
    }
}

Why this is better:

1. InitialData is grouped once instead of filtered repeatedly.
2. ToDictionary caches the grouped results, which matches the documented LINQ optimization pattern of materializing results when repeated execution would otherwise occur.
3. It avoids repeated query execution caused by deferred evaluation.
4. It removes the overhead of string-based dynamic filtering.

Additional notes:

• If the same query results are used repeatedly, materializing with ToList() or ToArray() can reduce repeated execution overhead.
• LINQ and lambda-heavy code can have noticeable performance costs in hot paths.
• If this code still runs frequently, measure whether replacing some LINQ with direct loops improves throughput.

If duplicates in FilteredData are not wanted, add a deduplication step because multiple SpecLevel items could map to overlapping results depending on the data model.

---

References:

• Work with Language-Integrated Query (LINQ)
• LINQ queries
• Writing Large, Responsive .NET Framework Apps