LINQ Select rows with Max of one column and Distinct in another

c#, linq, linq-to-entities, vb.net

Solution

Using `Take()` returns `IEnumerable<T>` while `FirstOrDefault()` returns just `<T>`, so:

IQueryable<RecentRate> query = from r in Rates
                               where r.IsRetired == false && r.EffectiveDate <= dateNow
                               group r by r.RateTypeID into rg
                               select new RecentRate
                               {
                                 Rate = rg.OrderByDescending(r => r.EffectiveDate).FirstOrDefault()
                               };

This might work:

   IQueryable<Rate> query = from r in Rates
                            where r.IsRetired == false && r.EffectiveDate <= dateNow
                            group r by r.RateTypeID into rg
                            select rg.OrderByDescending(r => r.EffectiveDate).FirstOrDefault();

I think this is the VB syntax

Dim result = (
    From r In Rates
    Where r.IsRetired = False And r.EffectiveDate <= dateNow
    Group r By r.RateTypeID
    Into rg = Group
    Select rg.OrderByDescending(Function(r) r.EffectiveDate).FirstOrDefault()
)

Problem

I am using LINQ to Entities to query the following MS-SQL table: ``` RateID RateTypeID Amount EffectiveDate IsRetired ------ ---------- ------ ------------- --------- 1 1 0.565 1/1/2013 0:00 FALSE 2 2 6.000 1/1/2013 0:00 FALSE 3 3 9.000 1/1/2013 0:00 FALSE 4 4 12.000 1/1/2013 0:00 FALSE 5 5 1.500 1/1/2013 0:00 FALSE 6 1 0.550 7/1/2012 0:00 FALSE 7 1 0.495 1/1/2012 0:00 FALSE 8 3 8.000 1/1/2011 0:00 FALSE 9 3 11.000 1/1/2015 0:00 FALSE 10 5 2.000 7/1/2013 0:00 TRUE ``` I want to build a function that will use LINQ to return an `IQueryable(Of Rate)` which I can then join in to other queries to efficiently determine the current rate applied to each item, by `RateTypeID`. `IsRetired` must be false and I need the row with the most recent past `EffectiveDate`. Here is the closest I have gotten (LINQPad VB Statements): ``` Dim dateNow As Date = Date.Now Dim result = ( From r In Rates Where r.IsRetired = False And r.EffectiveDate <= dateNow Group r By r.RateTypeID Into rg = Group Select New With { .cr = rg.OrderByDescending(Function(r) r.EffectiveDate).Take(1) } ) result.Dump() ``` This query returns `DbQuery(Of AnonymousType_1(Of IEnumerable(Of Rate)))` (a collection of collections each with 1 rate item), which isn't quite what I want. This Q&A solves a very similar problem but is in SQL instead of LINQ: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL? I am coding in vb.net but would also take a c# answer. Thanks!

Original source

Related problems