C# - SQL to LINQ Convertion (Select latest record by ID)

Question

I'm trying to convert this sql statement to LINQ

select tm.pr_title, tm.pr_number, tm.pr_req, tm.pr_req_owner, tm.pr_dept_req, ts.pr_hdr_name , 
        ts.pr_id_ctgy_date, ts.pr_hdr_step from tb_pr_mst_record tm
inner join tb_pr_dtl_record ts on tm.pr_number = ts.pr_number
where (ts.pr_number, ts.pr_id_ctgy_date) in (
        select ts.pr_number, max(ts.pr_id_ctgy_date)
        from tb_pr_dtl_record ts
        group by ts.pr_number
);

Here is my table with the complete data

PR Title    PR Number    PR Req   PR Req Owner   PR Dept Req   PR HDR Name  PR ID CTGY DATE  PR HDR Step
TL1         E1900        USR1     USR1           DEP1          ESX          12/1/2022        8
TL1         E1900        USR1     USR1           DEP1          ESX          12/5/2022        8
TL1         E1900        USR1     USR1           DEP1          ESX          12/6/2022        8
TL2         E1300        USR2     USR2           DEP4          EPX          12/1/2022        8
TL2         E1300        USR2     USR2           DEP4          EPX          12/4/2022        8
TL2         E1300        USR2     USR2           DEP4          EPX          12/5/2022        8

This is the result I get when executing the query

PR Title    PR Number    PR Req   PR Req Owner   PR Dept Req   PR HDR Name  PR ID CTGY DATE  PR HDR Step
TL1         E1900        USR1     USR1           DEP1          ESX          12/6/2022        8
TL2         E1237        USR2     USR2           DEP4          EPX          12/5/2022        8

I tried to transcribe in LINQ but I can't interpret the WHERE statement of the above SQL query

var listq = from tm in entddb.TB_PR_MST_RECORD
            join ts in entddb.TB_PR_DTL_RECORD on tm.PR_NUMBER equals ts.PR_NUMBER
            select new InvIndexModels { 
                            mPR_TITLE = tm.PR_TITLE, 
                            mPR_REQ = tm.PR_REQ, 
                            mPR_REQ_OWNER = tm.PR_REQ_OWNER, 
                            mPR_DEPT_REQ = tm.PR_DEPT_REQ, 
                            mPR_HDR_NAME = ts.PR_HDR_NAME, 
                            mPR_ID_CTGY_DATE = ts.PR_ID_CTGY_DATE, 
                            mPR_HDR_STEP = ts.PR_HDR_STEP 
                        };

Answer 1

With EF Core 5 and lower, you can do the following:

var dtlRecords = entddb.TB_PR_DTL_RECORD.AsQueryable();

var latest =
    from d in dtlRecords.Select(d => new { d.PR_NUMBER }).Distinct()
    from dtl in dtlRecords
        .Where(dtl => dtl.PR_NUMBER == d.PR_NUMBER)
        .OrderbyDescending(dtl => dtl.PR_ID_CTGY_DATE)
        .Take(1)
    select dtl;

var listq = 
    from tm in entddb.TB_PR_MST_RECORD
    join ts in latest on tm.PR_NUMBER equals ts.PR_NUMBER
    select new InvIndexModels 
    { 
        mPR_TITLE = tm.PR_TITLE, 
        mPR_REQ = tm.PR_REQ, 
        mPR_REQ_OWNER = tm.PR_REQ_OWNER, 
        mPR_DEPT_REQ = tm.PR_DEPT_REQ, 
        mPR_HDR_NAME = ts.PR_HDR_NAME, 
        mPR_ID_CTGY_DATE = ts.PR_ID_CTGY_DATE, 
        mPR_HDR_STEP = ts.PR_HDR_STEP 
    };

With EF Core 6 you can build latest via GroupBy:

var latest =
    from dtl in dtlRecords
    group dtl by dtl.PR_NUMBER into g
    select g.OrderbyDescending(x => x.PR_ID_CTGY_DATE).First();

If it is repetitive task, the same result you can achieve via my implementation of DistinctBy from this answer.

var latest = dtlRecords.DistinctBy(d => d.PR_NUMBER, d => d.PR_ID_CTGY_DATE);