基于独立PriceLog最新价格的LINQ求和实现（ASP Core+EF场景）

Hey there! Let's work through this problem step by step. First, I need to make a quick assumption about the PriceLog entity since it wasn't provided—this is key because we need to know how it links to InventoryDetails and what price-related fields it contains. I'll assume it looks like this (adjust based on your actual schema):

public class PriceLog
{
    public int Id { get; set; }
    [ForeignKey(nameof(InventoryDetails))]
    public string InventoryID { get; set; }
    public decimal Price { get; set; }
    public DateTime CreatedDate { get; set; } // Tracks when the price was updated

    public InventoryDetails InventoryDetails { get; set; }
}

1. 获取每个库存物品的最新价格 #

We have two reliable ways to pull the latest price for each inventory item using LINQ with EF Core.

方法一：子查询（可读性优先） #

This approach uses a subquery to fetch the most recent price for each item by sorting price logs by CreatedDate (newest first) and picking the top result:

var inventoryWithLatestPrice = await _context.InventoryDetails
    .Select(inv => new
    {
        inv.InventoryID,
        inv.InventoryName,
        inv.Details,
        // Get the newest price for this inventory item
        LatestPrice = _context.PriceLogs
            .Where(pl => pl.InventoryID == inv.InventoryID)
            .OrderByDescending(pl => pl.CreatedDate)
            .Select(pl => pl.Price)
            .FirstOrDefault()
    })
    .ToListAsync();

Note: If some items might not have any price logs, change Price to decimal? in PriceLog so LatestPrice returns null instead of 0 (the default for non-nullable decimals).

方法二：分组+最大ID（性能优先） #

If your PriceLog uses an auto-incrementing Id, you can group logs by InventoryID and grab the log with the highest Id (since newer logs will have higher IDs). This can be faster in some scenarios:

var inventoryWithLatestPrice = await _context.InventoryDetails
    .Join(
        // Group price logs and find the latest log ID for each item
        _context.PriceLogs.GroupBy(pl => pl.InventoryID)
            .Select(g => new
            {
                InventoryID = g.Key,
                LatestPriceLogId = g.Max(pl => pl.Id)
            }),
        inv => inv.InventoryID,
        plGroup => plGroup.InventoryID,
        (inv, plGroup) => new
        {
            inv.InventoryID,
            inv.InventoryName,
            inv.Details,
            LatestPrice = _context.PriceLogs
                .Where(pl => pl.Id == plGroup.LatestPriceLogId)
                .Select(pl => pl.Price)
                .FirstOrDefault()
        }
    )
    .ToListAsync();

2. 求和汇总操作 #

Now let's cover how to calculate sums based on the latest prices.

计算所有物品最新价格的总和 #

This query first fetches the latest price for each item, then sums them up:

var totalLatestPriceSum = await _context.InventoryDetails
    .Select(inv => _context.PriceLogs
        .Where(pl => pl.InventoryID == inv.InventoryID)
        .OrderByDescending(pl => pl.CreatedDate)
        .Select(pl => (decimal?)pl.Price) // Use nullable to filter out items without prices
        .FirstOrDefault())
    .Where(price => price.HasValue) // Exclude items with no price logs
    .SumAsync(price => price.Value);

按条件汇总（示例：按物品名称筛选） #

If you need to sum only items that match a condition (e.g., items with "Electronics" in the name):

var electronicsTotal = await _context.InventoryDetails
    .Where(inv => inv.InventoryName.Contains("Electronics"))
    .Select(inv => _context.PriceLogs
        .Where(pl => pl.InventoryID == inv.InventoryID)
        .OrderByDescending(pl => pl.CreatedDate)
        .Select(pl => (decimal?)pl.Price)
        .FirstOrDefault())
    .Where(price => price.HasValue)
    .SumAsync(price => price.Value);

3. 加载关联数据（如果需要SalesItems） #

If you want to include the SalesItems collection alongside the latest price, use Include and combine it with the price subquery:

var inventoryWithLatestPriceAndSales = await _context.InventoryDetails
    .Include(inv => inv.SalesItems) // Load related SalesItems
    .Select(inv => new
    {
        inv.InventoryID,
        inv.InventoryName,
        inv.Details,
        inv.SalesItems,
        LatestPrice = _context.PriceLogs
            .Where(pl => pl.InventoryID == inv.InventoryID)
            .OrderByDescending(pl => pl.CreatedDate)
            .Select(pl => pl.Price)
            .FirstOrDefault()
    })
    .ToListAsync();

内容的提问来源于stack exchange，提问作者Faraz Ahmed Qureshi