CODEWITHSUNDEEP
sqllinqentity-framework
Sumit Pannu
Sumit Pannu

Reputation: 177

converting SQL join with group by and MAX to Linq

I have Created following query in sql but i am not able to find out how to implement StatusAndTrackingNotes table join in my linq:

SELECT *
FROM [Application] APP
join User USR on APP.ApplicationId = USR.UserApplicationId
join 
(Select MAX(TrackingDate) as MaxDateTD,TrackingApplicationId
 From StatusAndTrackingNotes 
 where TrackingLoanType = 1 and ((TrackingStatusCode <= 52 and TrackingStatusCode >= 50) or TrackingStatusCode = 62)
 group by TrackingApplicationId) MTND on APP.ApplicationId = MTND.TrackingApplicationId
join Details DTL on APP.ApplicationId = DTL.ApplicationId
join ApplicationFees AF on APP.ApplicationId = AF.ApplicationId
where APP.LatestStatus = 'F' and DTL.Type = 1 and DTL.FundingDate >= '2011-06-01'  and   DTL.FundingDate <= '2013-06-30'
    and AF.FirstRefPaidDate is not null

Kindly help me with the syntax.First i was using using simple query with joins and then

([Linq Query]).GroupBy(i => i.TrackingApplicationId).Select(g => g.OrderByDescending(c => c.TrackingDate).FirstOrDefault());

but it doesnt return any result. Kindly help. Thanks in advance

Upvotes: 0

Views: 76

Answers (1)

Torbj&#246;rn Hansson
Torbj&#246;rn Hansson

Reputation: 19423

I would solve it by creating StatusAndTrackingNotes as separate query and join and group it to the "main query". I.e:

var trackingQuery =
    from t in statusAndTrackingNotes
    where t.TrackingLoanType == 1 && (t.TrackingStatusCode <= 52 && t.TrackingStatusCode >=50) || t.TrackingStatusCode == 62
    group t by t.TrackingApplicationId into trackings
    select new 
    { 
        TrackingApplicationId = trackings.Key,
        MaxDate = trackings.Max(t => t.TrackingDate)
    };

var appQuery =
    from app in Application
    join t in trackingQuery on app.ApplicationId equals t.TrackingApplicationId
    /* other joins here */
    select new
    {
        app,
        t.MaxDate,
    };

Upvotes: 1

Related Questions