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

wpcxdonn 于 2022-12-06 发布在 SQLite

关注(0)|答案(2)|浏览(266)

我需要将以下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; }
}

我使用的是数据库优先。

linq

来源：https://stackoverflow.com/questions/73085624/how-to-get-last-record-using-linq-entity-framework-in-sqlite

2条答案

按热度按时间

shyt4zoc1#

你应该能够使用LINQ加入我已经模拟了一些东西，可能会指出你在正确的方向：
备注
1.首先使用联接获取要查找的集合
1.然后可以根据supplierId对max进行嵌套选择。

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)
                 };

赞(0）回复(0）举报 2022-12-06

fkaflof62#

我解决了这个问题，由于凯文的建议。我确实需要更多的网络搜索，以改善凯文的评论代码块，我做到了。

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; }
    }

赞(0）回复(0）举报 2022-12-06

我来回答

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

2条答案

相关问题

热门标签

最新问答