How to get record with max date with join?

Question

How can I do this in LINQ?

select  MAX(d.DepartureDateRange),MAX(d.ReturnDateRange)   
from Tour t join
TourCategory tc on t.ID = tc.TourID
join TourDates td on t.ID = td.TourID
join Dates d on d.ID = td.DatesID
where tc.CategoryID = 3 and t.ID = 12

Database diagram is here ->

For example joins is like this but i cannot get Max of DepartureDateRange & ReturnDateRange

var query2 = from t in db.Tour
    join tc in db.TourCategory on t.ID equals tc.TourID
    join td in db.TourDates on t.ID equals td.TourID
    join d in db.Dates on td.DatesID equals d.ID
    where tc.CategoryID == 3
    select new IndexTour
    {
        ID = t.ID,
        TourName = t.TourName,
        //DepartureDateRange = 
        //ReturnDateRange = 
        Description = t.SmallDesc,
        Price = t.Price,
        CoverPhotoUrl = t.CoverPhotoUrl,
        TourProgram = t.TourDesc
    };

Thanks in advance.

Your SQL returns one row with two records (grouping by whole dataset), but LINQ expression return list of IndexTour. Do you need max dates for each Tour or max dates for whole selection (as per SQL)? — pakeha_by, Mar 29 '19 at 08:15
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, Mar 29 '19 at 20:28

pakeha_by · Accepted Answer · 2019-03-29T23:54:16.717

Here it is (dates are grouped by Tour):

var query2 = 
    from t in db.Tour
    join tc in db.TourCategory on t.ID equals tc.TourID
    where tc.CategoryID == 3
    // join dates aggregates grouped by tour id
    join tdates in
        from td in db.TourDates 
        join d in db.Dates on td.DatesID equals d.ID
        group d by td.TourID into grp
        select new 
        { 
            tourID = grp.Key, 
            departure = grp.Max(g => g.DepartureDateRange), 
            rtrn = grp.Max(g => g.ReturnDateRange)
        }
    on t.ID equals tdates.tourID
    select new IndexTour
    {
        ID = t.ID,
        TourName = t.TourName,
        DepartureDateRange = tdates.departure,
        ReturnDateRange = tdates.rtrn,
        Description = t.SmallDesc,
        Price = t.Price,
        CoverPhotoUrl = t.CoverPhotoUrl,
        TourProgram = t.TourDesc
    };

score 0 · Answer 2 · answered Mar 29 '19 at 10:35

I think this is what you want?

var dateRanges = tours
    .Join(tourCategories,
        t => t.Id,
        tc => tc.TourId,
        (t, tc) => (t, tc))
    .Join(tourDates,
        ttc => ttc.t.Id,
        td => td.TourId,
        (ttc, td) => (ttc, td))
    .Join(dates, 
        ttctd => ttctd.td.DateId,
        d => d.Id,
        (ttctd, d) =>
        new {
            TourId = ttctd.ttc.t.Id,
            CategoryId = ttctd.ttc.tc.CategoryId,
            DepartureDateRange = d.DepartureDateRange,
            ReturnDateRange = d.ReturnDateRange
        });

var filtered = dateRanges
    .Where(r => r.CategoryId == 3 && r.TourId == 12);

var maxDepartureDateRange = filtered.Max(d => d.DepartureDateRange);
var maxReturnDateRange = filtered.Max(d => d.ReturnDateRange);

How to get record with max date with join?

2 Answers2