How to Get Last Record Using LINQ - Entity Framework in SQLite

Question

I need to translate the following SQLite query to LINQ in C#

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

I've searched about all web site and tried the following LINQ query and it does group like what I want but doesn't select latest date. I'm newbie in LINQ please help me

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

I am using Database-First.

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jul 22 '22 at 20:58

score 0 · Answer 1 · answered Jul 22 '22 at 21:40

You should be able to use a LINQ Join I have mocked up something which might point you in the correct direction:

Notes

use the join first to get the set you looking for
you can then do a nested select for the max based on 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)
                 };

Thanks for your reply Kevin! Your query returns all record in the database and doesn't group suppliers. Where I should type the "GroupBy" clause into like "var groupedPrice" above? — airboss, Jul 22 '22 at 22:02

score 0 · Accepted Answer · answered Jul 25 '22 at 12:35

I solved the problem owing to Kevin's suggestion. I did need to be more search on web to improve the code block of Kevin's comment and I did.

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

How to Get Last Record Using LINQ - Entity Framework in SQLite

2 Answers2