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.

CodePudding user response：

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

Notes

1. use the join first to get the set you looking for
2. 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)
                 };

CodePudding user response：

I would like to send this as a comment but i don't have enough reputation.

So if you do the query you want and after that you order it by date descending and if you scale it with top 1 i think it might be work.