如何在SQLite中使用LINQ实体框架获取最后一条记录

Question

我需要将以下SQLite查询转换为C#中的LINQ

SELECT sup.SupplierName, sup.SupplierID, pr.Price, max(pr.AddedDate)
FROM Suppliers sup
LEFT JOIN ItemsPrices pr
USING(SupplierID)
WHERE pr.ItemID = '22'
GROUP BY sup.SupplierName

我搜索了所有网站，并尝试了以下LINQ查询，它确实像我想要的那样分组，但没有选择最新日期。我是LINQ的新手，请帮我

    internal List<LatestPriceDbo> GetLatestPrice(int _itemid)
    {
        using (dbContext context = new dbContext())
        {
             var groupedPrice = from a in context.ItemsPrices
                             where a.ItemId == _itemid
                             orderby a.Id descending
                             group a by new { a.ItemId, a.SupplierId } into g
                             select new ItemsPrice
                             {
                                SupplierId = g.Key.SupplierId,
                                ItemId = g.Key.ItemId,
                                Price = g.FirstOrDefault().Price,
                                AddedDate = g.Max(s => s.AddedDate)
                             };
            var result = (from c in context.Suppliers
                        from k in groupedPrice
                        where k.ItemId == _itemid && c.SupplierId == k.SupplierId
                        select new LatestPriceDbo
                        {
                             supid = c.SupplierId,
                             supname = c.SupplierName,
                             price = k.Price,
                             addeddate = k.AddedDate
                        }).ToList();

          return result;
       }
    }

internal class LatestPriceDbo
{
    public int supid { get; set; }
    public string supname { get; set; }
    public decimal price { get; set; }
    public string addeddate { get; set; }
}

我正在使用数据库优先。

Answer 1

您应该能够使用LINQ Join，我已经模拟了一些可能会为您指明正确方向的东西：

票据

1. 首先使用join来获取您要查找的集合
2. 然后，您可以根据supplierId为最大值进行嵌套选择

 from a in context.ItemsPrices 
      join s in context.Suppliers on a.SupplierId equals s.supplierId 
                 where a.ItemId == _itemid
                 orderby a.Id descending
                 select new ItemsPrice
                 {
                    SupplierName = s.SupplierName
                    SupplierId = a.SupplierId,
                    ItemId = a.ItemId,
                    Price = a.FirstOrDefault().Price,
                    AddedDate = context.ItemsPrices.Where(x => x.SupplierId == a.SupplierId).Max(s => s.AddedDate)
                 };

Answer 2

由于Kevin的建议，我解决了这个问题。我确实需要在网上进行更多的搜索来改进Kevin评论的代码块，我做到了。

    internal List<LatestPriceDbo> GetLatestPrice(int _itemid)
    {
        using (dbContext context = new dbContext())
        {
            var result = (from a in context.ItemsPrices
                          join s in context.Suppliers on a.SupplierId equals s.SupplierId
                          where a.ItemId == _itemid
                          orderby a.Id descending
                          group new { a, s } by new { a.SupplierId, a.ItemId } into grb
                          select new LatestPriceDbo
                          {
                              supname = grb.FirstOrDefault().s.SupplierName,
                              supid = grb.Key.SupplierId,
                              itemid = grb.Key.ItemId,
                              price = context.ItemsPrices
                                          .Where(x => x.ItemId == grb.FirstOrDefault().a.ItemId)
                                          .OrderByDescending(z => z.Id).Select(z => z.Price)
                                          .FirstOrDefault(),
                              addeddate = context.ItemsPrices
                                          .Where(x => x.SupplierId == grb.FirstOrDefault().a.SupplierId)
                                          .Max(s => s.AddedDate)
                          }).ToList();
            return result;
        }
    }
    internal class LatestPriceDbo
    {
        public int itemid { get; set; }
        public int supid { get; set; }
        public string supname { get; set; }
        public decimal price { get; set; }
        public string addeddate { get; set; }
        public int recordid { get; set; }
    }