寻求可存储区间映射的类字典数据结构（按数值返回阶段名）

Great question! The standard Dictionary<TKey, TValue> works with discrete single-value keys, so it doesn’t directly support range-based lookups like your BETWEEN y1 AND y2 SQL logic. But you can build a custom structure that behaves like a dictionary for interval-to-value mapping, or use optimized workarounds tailored to your needs. Here are practical approaches for C#:

1. Basic Custom RangeDictionary Class #

Start with a simple implementation that stores range-value pairs and handles linear lookups—perfect for small datasets:

First, define a helper structure to represent your integer ranges:

public struct IntRange
{
    public int LowerBound { get; }
    public int UpperBound { get; }

    public IntRange(int lower, int upper)
    {
        LowerBound = lower;
        UpperBound = upper;
    }

    // Check if a value falls within the range (matches SQL's BETWEEN logic)
    public bool Contains(int value)
    {
        return value >= LowerBound && value <= UpperBound;
    }
}

Then build the range dictionary class:

public class RangeDictionary<TValue>
{
    private readonly List<(IntRange Range, TValue Value)> _entries = new();

    // Add a range-to-value mapping (mimics Dictionary.Add)
    public void Add(IntRange range, TValue value)
    {
        // Optional: Add validation here to prevent overlapping ranges if needed
        _entries.Add((range, value));
    }

    // Look up the value for a given point
    public TValue? GetValue(int point)
    {
        foreach (var entry in _entries)
        {
            if (entry.Range.Contains(point))
            {
                return entry.Value;
            }
        }
        return default; // Or throw an exception if no match is expected
    }
}

Usage Example #

Match your exact scenario with this code:

var phaseLookup = new RangeDictionary<string>();
phaseLookup.Add(new IntRange(200, 350), "Project Management");
phaseLookup.Add(new IntRange(351, 400), "Controlling");

// Test the lookups
Console.WriteLine(phaseLookup.GetValue(300)); // Output: Project Management
Console.WriteLine(phaseLookup.GetValue(360)); // Output: Controlling

2. Optimized Sorted Range Lookup #

For large datasets, linear scans get slow. Sort your ranges by their lower bound and use binary search to speed up lookups:

public class SortedRangeDictionary<TValue>
{
    private readonly List<(IntRange Range, TValue Value)> _sortedEntries = new();

    public void Add(IntRange range, TValue value)
    {
        _sortedEntries.Add((range, value));
        // Keep entries sorted by lower bound for binary search
        _sortedEntries.Sort((a, b) => a.Range.LowerBound.CompareTo(b.Range.LowerBound));
    }

    public TValue? GetValue(int point)
    {
        int left = 0;
        int right = _sortedEntries.Count - 1;

        while (left <= right)
        {
            int mid = left + (right - left) / 2;
            var currentEntry = _sortedEntries[mid];

            if (currentEntry.Range.Contains(point))
            {
                return currentEntry.Value;
            }
            else if (point < currentEntry.Range.LowerBound)
            {
                right = mid - 1;
            }
            else
            {
                left = mid + 1;
            }
        }
        return default;
    }
}

Key Considerations #

• Overlap Handling: If your ranges can overlap, define a priority rule (e.g., first match, highest upper bound) and adjust the lookup logic accordingly.
• Edge Cases: Decide how to handle values outside all ranges—return a default value, throw an exception, or use a fallback phase name.

内容的提问来源于stack exchange，提问作者Björn Karpenstein