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

Question

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!

if u change the last line to Select r (instead of new With.....) which type does the query return? — Omer Eldan, Dec 05 '13 at 16:55
@OmerEldan `r` is no longer in scope after the Group statement. At that point you have `DbQuery>>`. If you `Select rg` you get `DbQuery>`. — pseudocoder, Dec 05 '13 at 16:58
The problem I am running into is after the Group By, the collections of `Rate` are separated by `RateTypeID`, which is necessary for the MAX sort, but I need to recombine them after the fact...not sure how, or if there is another technique for finding the entire row with `Max(EffectiveDate)`. — pseudocoder, Dec 05 '13 at 17:00

score 6 · Accepted Answer · edited Dec 05 '13 at 17:38

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()
)

Using `FirstOrDefault` instead of `Take` gives `DbQuery>` which doesn't quite make it...however... — pseudocoder, Dec 05 '13 at 17:04

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

1 Answers1