LinQ实现不同类型列表分组求和并生成字典的技术咨询

Got it, let's break down how to translate that SQL query into LINQ that fits your needs—including generating a mutable dictionary you can access and modify frequently, and avoiding those duplicate key exceptions you ran into before.

First, let's recap your core requirements:

• Combine stockA (with BucketNo, ReferenceNo, Productionpieces) and stockB (with ReferenceNo, pieces)
• Group by a cleaned ReferenceNo (spaces removed)
• Sum Productionpieces from stockA and pieces from stockB, treating nulls as 0
• Output a dictionary that's easy to read/write to

Option 1: LINQ to Entities (Database-Side Execution, Recommended) #

This runs most of the logic directly in the database (like your original SQL), which is more efficient for large datasets. We'll use EF's GroupJoin to simulate the left join, then clean and group the results:

// Using EF Core (adjust for EF6 if needed)
var inventoryDictionary = await _context.StockA
    // Filter out null ReferenceNo first, matching your SQL's WHERE clause
    .Where(stockA => stockA.ReferenceNo != null)
    // Left join stockA to stockB on ReferenceNo
    .GroupJoin(
        _context.StockB.Where(stockB => stockB.ReferenceNo != null),
        stockA => stockA.ReferenceNo,
        stockB => stockB.ReferenceNo,
        (a, bGroup) => new
        {
            // Clean the ReferenceNo by removing spaces
            CleanRef = a.ReferenceNo.Replace(" ", ""),
            // Handle null Productionpieces by defaulting to 0
            ProductionPieces = a.Productionpieces ?? 0,
            // Sum stockB's pieces for this ReferenceNo, default to 0 if no matches
            TotalPiecesB = bGroup.Sum(b => (long?)b.pieces) ?? 0
        }
    )
    // Group by the cleaned ReferenceNo to ensure unique keys
    .GroupBy(item => item.CleanRef)
    // Calculate the total sum for each group
    .Select(group => new
    {
        ReferenceNo = group.Key,
        TotalPieces = group.Sum(item => item.ProductionPieces) + group.Sum(item => item.TotalPiecesB)
    })
    // Convert to a mutable dictionary (no duplicate keys here since we grouped first)
    .ToDictionaryAsync(k => k.ReferenceNo, v => v.TotalPieces);

Option 2: In-Memory LINQ (If Data Is Already Loaded) #

If you already have stockA and stockB as in-memory lists, use this approach to combine and group them:

// Assume stockAList and stockBList are your in-memory collections
var cleanedStockA = stockAList
    .Where(a => a.ReferenceNo != null)
    .Select(a => new
    {
        CleanRef = a.ReferenceNo.Replace(" ", ""),
        ProdPieces = a.Productionpieces ?? 0
    });

var cleanedStockB = stockBList
    .Where(b => b.ReferenceNo != null)
    .Select(b => new
    {
        CleanRef = b.ReferenceNo.Replace(" ", ""),
        Pieces = b.pieces ?? 0
    });

// Combine both datasets, group by cleaned ReferenceNo, and sum totals
var combinedTotals = cleanedStockA
    .GroupBy(a => a.CleanRef)
    .Select(g => new { Ref = g.Key, Sum = g.Sum(x => x.ProdPieces) })
    .Concat(
        cleanedStockB
            .GroupBy(b => b.CleanRef)
            .Select(g => new { Ref = g.Key, Sum = g.Sum(x => x.Pieces) })
    )
    .GroupBy(x => x.Ref)
    .Select(g => new { Ref = g.Key, Total = g.Sum(x => x.Sum) });

// Generate a mutable dictionary (safe from duplicate keys now)
var inventoryDict = combinedTotals.ToDictionary(k => k.Ref, v => v.Total);

Why You Had Duplicate Key Exceptions #

Your earlier issue with KeyValuePair was likely because you tried to create a dictionary before grouping—so the same ReferenceNo appeared multiple times. By grouping first (either in the database or in memory), we ensure each cleaned ReferenceNo is unique, which eliminates duplicate key errors.

Quick Notes #

• If ReferenceNo has case differences (e.g., "ABC123" vs "abc123"), add .ToUpper() (or .ToLower()) when cleaning the reference to avoid treating them as separate keys.
• For EF6, replace Replace with DbFunctions.Replace if the standard string Replace isn't supported.
• The resulting Dictionary<string, long> is fully mutable—you can update values directly like inventoryDict["MY_REF"] = 100; whenever you need to.

内容的提问来源于stack exchange，提问作者rkNN