How to get group of records with latest date using Linq to Entities

Question

I have a table with the following fields:

ID | MarketID | CommodityID | CurrencyID | PriceValue | Year | Month

With the following data:

1  | 100 | 30 | 15 | 3.465 | 2018 | 03
2  | 100 | 30 | 15 | 2.372 | 2018 | 04
3  | 100 | 32 | 15 | 1.431 | 2018 | 02
4  | 100 | 32 | 15 | 1.855 | 2018 | 03
5  | 100 | 32 | 15 | 2.065 | 2018 | 04
6  | 101 | 30 | 15 | 7.732 | 2018 | 03
7  | 101 | 30 | 15 | 8.978 | 2018 | 04
8  | 101 | 32 | 15 | 4.601 | 2018 | 02
9  | 101 | 32 | 18 | 0.138 | 2017 | 12
10 | 101 | 32 | 18 | 0.165 | 2018 | 03
11 | 101 | 32 | 18 | 0.202 | 2018 | 04

As you can see the date is (unfortunately) saved as an integer in the Year and Month fields.

I want to get from the above data, using LINQ to Entities (EF6), the latest PriceValue for each Market-Commodity-Currency record.

So the expected result should be:

2  | 100 | 30 | 15 | 2.372 | 2018 | 04
5  | 100 | 32 | 15 | 2.065 | 2018 | 04
7  | 101 | 30 | 15 | 8.978 | 2018 | 04
8  | 101 | 32 | 15 | 4.601 | 2018 | 02
11 | 101 | 32 | 18 | 0.202 | 2018 | 04

I've tried with the following queries but none of them can give me the expected results:

var lastValues = (from a in Analysis
                     group a by a.ID into g
                     select g.OrderByDescending(t => ((t.Year* 100) + t.Month)));

and the following that has more sense of the previous one, but I loose the PriceValue field:

var lastValues = (from a in Analysis
                  group a by new {a.MarketID, a.CommodityID, a.CurrencyID } into g
                  select new
                  {
                     g.Key.MarketID,
                     g.Key.CommodityID,
                     g.Key.CurrencyID,
                     date = g.Max(t => ((t.Year* 100) + t.Month))
                  });

Is there a way to have a single LINQ query to get only the records with the latest date as I've described above?

Answer 1

Try This Linq Query

     var lastValues = (from ak in Analysis
                          group ak by new { ak.MarketID, ak.CommodityID, ak.CurrencyID } into g
                          select new
                          {
                              g.Key.MarketID,
                              g.Key.CommodityID,
                              g.Key.CurrencyID,
                              pricevalue = g.OrderByDescending(c=>c.Year).ThenByDescending(c=>c.Month).FirstOrDefault().PriceValue,
                              year = g.OrderByDescending(c => c.Year).ThenByDescending(c => c.Month).FirstOrDefault().Year,
                              month = g.OrderByDescending(c => c.Year).ThenByDescending(c => c.Month).FirstOrDefault().Month
                          });

Using Orderby and ThenBy

Answer 2

I've finally found the way to include also the PriceValue in the results.

Starting from the last query I've posted, I've to simply add the

g.OrderByDescending(t => ((t.Year* 100) + t.Month)).FirstOrDefault().PriceValue

So it will be:

var lastValues = (from a in Analysis
                  group a by new {a.MarketID, a.CommodityID, a.CurrencyID } into g
                  select new
                  {
                     g.Key.MarketID,
                     g.Key.CommodityID,
                     g.Key.CurrencyID,
                     date = g.Max(t => ((t.Year* 100) + t.Month)),
                     g.OrderByDescending(t => ((t.Year* 100) + t.Month)).FirstOrDefault().PriceValue
                  });

I prefer this solution as it's based on the current query I've developed.

Answer 3

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication45
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("MARKETID", typeof(int));
            dt.Columns.Add("CommodityID", typeof(int));
            dt.Columns.Add("CurrencyID", typeof(int));
            dt.Columns.Add("PriceValue", typeof(decimal));
            dt.Columns.Add("Year", typeof(int));
            dt.Columns.Add("Month", typeof(int));
            //ID | MarketID | CommodityID | CurrencyID | PriceValue | Year | Month
            dt.Rows.Add(new object[] {1  , 100 , 30 , 15 , 3.465 , 2018 , 03});
            dt.Rows.Add(new object[] {2  , 100 , 30 , 15 , 2.372 , 2018 , 04});
            dt.Rows.Add(new object[] {3  , 100 , 32 , 15 , 1.431 , 2018 , 02});
            dt.Rows.Add(new object[] {4  , 100 , 32 , 15 , 1.855 , 2018 , 03});
            dt.Rows.Add(new object[] {5  , 100 , 32 , 15 , 2.065 , 2018 , 04});
            dt.Rows.Add(new object[] {6  , 101 , 30 , 15 , 7.732 , 2018 , 03});
            dt.Rows.Add(new object[] {7  , 101 , 30 , 15 , 8.978 , 2018 , 04});
            dt.Rows.Add(new object[] {8  , 101 , 32 , 15 , 4.601 , 2018 , 02});
            dt.Rows.Add(new object[] {9  , 101 , 32 , 18 , 0.138 , 2017 , 12});
            dt.Rows.Add(new object[] {10 , 101 , 32 , 18 , 0.165 , 2018 , 03});
            dt.Rows.Add(new object[] { 11, 101, 32, 18, 0.202, 2018, 04 });

            List<DataRow> results = dt.AsEnumerable()
                .OrderByDescending(x => new DateTime(x.Field<int>("Year"), x.Field<int>("Month"), 1))
                .GroupBy(x => new { market = x.Field<int>("MarketID"), commodity = x.Field<int>("CommodityID"), currency = x.Field<int>("CurrencyID") })
                .Select(x => x.First())
                .ToList();


        }

    }
}