如何在SQLite中使用LINQ-Entity Framework获取最新记录

先明确你的原SQL逻辑：关联供应商表与商品价格表，筛选指定商品ID的记录，按供应商名称分组，获取每个供应商的名称、ID，以及对应商品的最新价格日期和该日期的价格。

你的现有代码核心问题是：groupedPrice中通过Id降序取FirstOrDefault().Price，但Id的顺序并不等同于AddedDate的最新顺序，导致取到的价格和最新日期不匹配。

正确的LINQ实现（查询语法） #

internal List<LatestPriceDbo> GetLatestPrice(int _itemid)
{
    using (dbContext context = new dbContext())
    {
        // 先筛选指定商品，按供应商分组，取每组内最新日期的价格记录
        var latestPricePerSupplier = from pr in context.ItemsPrices
                                     where pr.ItemId == _itemid
                                     group pr by pr.SupplierId into g
                                     let latestRecord = g.OrderByDescending(x => x.AddedDate).FirstOrDefault()
                                     select new 
                                     {
                                         SupplierId = g.Key,
                                         Price = latestRecord.Price,
                                         AddedDate = latestRecord.AddedDate
                                     };

        // 关联供应商表获取名称，组装结果
        var result = from sup in context.Suppliers
                     join price in latestPricePerSupplier on sup.SupplierId equals price.SupplierId
                     select new LatestPriceDbo
                     {
                         supid = sup.SupplierId,
                         supname = sup.SupplierName,
                         price = price.Price,
                         addeddate = price.AddedDate.ToString() // 根据需求可指定日期格式，如ToString("yyyy-MM-dd")
                     };

        return result.ToList();
    }
}

方法语法版本 #

internal List<LatestPriceDbo> GetLatestPrice(int _itemid)
{
    using (dbContext context = new dbContext())
    {
        var latestPricePerSupplier = context.ItemsPrices
            .Where(pr => pr.ItemId == _itemid)
            .GroupBy(pr => pr.SupplierId)
            .Select(g => 
            {
                var latestRecord = g.OrderByDescending(x => x.AddedDate).FirstOrDefault();
                return new 
                {
                    SupplierId = g.Key,
                    Price = latestRecord.Price,
                    AddedDate = latestRecord.AddedDate
                };
            });

        var result = context.Suppliers
            .Join(latestPricePerSupplier,
                  sup => sup.SupplierId,
                  price => price.SupplierId,
                  (sup, price) => new LatestPriceDbo
                  {
                      supid = sup.SupplierId,
                      supname = sup.SupplierName,
                      price = price.Price,
                      addeddate = price.AddedDate.ToString()
                  })
            .ToList();

        return result;
    }
}

关键优化说明 #

• 匹配最新日期的价格：通过OrderByDescending(x => x.AddedDate).FirstOrDefault()直接获取分组内最新日期的完整记录，确保价格和日期一一对应，解决你之前两者不匹配的问题。
• 简化逻辑：先处理价格表的分组筛选，再与供应商表做内连接（原SQL中WHERE pr.ItemID = '22'会将左连接转为内连接，只有匹配的价格记录会被保留），避免多层查询的冗余。
• 类型适配：你的LatestPriceDbo中addeddate为string类型，需将DateTime类型的AddedDate转换为字符串，可根据业务需求指定格式。

如果需要保留原SQL的左连接逻辑（即使供应商没有对应商品的价格也显示，此时价格和日期为空），可修改为左连接：

var result = from sup in context.Suppliers
             join price in latestPricePerSupplier on sup.SupplierId equals price.SupplierId into priceGroup
             from p in priceGroup.DefaultIfEmpty()
             select new LatestPriceDbo
             {
                 supid = sup.SupplierId,
                 supname = sup.SupplierName,
                 price = p?.Price ?? 0, // 无价格时设置默认值
                 addeddate = p?.AddedDate?.ToString() ?? string.Empty
             };

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