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 
                        };

Does this answer your question? [Linq to Entities - SQL "IN" clause](https://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause) — Jim G., Dec 06 '22 at 18:38
Declare a variable with **let** and put the sub-request you are going to use to check the presence of the data couple `(ts.pr_number, ts.pr_id_ctgy_date)`. Next, in the parent request's `where` clause you check if the couple is in the sub-request. — Rivo R., Dec 06 '22 at 18:38
@JimG. Thanks but not working for me because only using a list while I need to do another sub process inside the where — BlackSD, Dec 06 '22 at 19:30
With EF Core 6 this query can be written via GroupBy, for lower versions it is needed another implementation. With `linq2db` we can use Window Functions. So, which ORM do you use? — Svyatoslav Danyliv, Dec 06 '22 at 20:01
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, Dec 06 '22 at 21:45

score 1 · Accepted Answer · answered Dec 06 '22 at 21:22

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

Works!! but there is a detail, when executing this query in PLSQL if in max(ts.pr_id_ctgy_date) it returns the last field that it detects with date with its respective PR HDR Step running this in linq skips this as it shows me the last record max(ts.pr_id_ctgy_date) even though it is null — BlackSD, Dec 06 '22 at 22:29
I do not know nature your data, but simple tuning may help: `var dtlRecords = entddb.TB_PR_DTL_RECORD.Where(dtl => dtl.PR_ID_CTGY_DATE != null);` — Svyatoslav Danyliv, Dec 06 '22 at 22:37

Rivo R. · Answer 2 · 2022-12-06T19:07:55.363

I don't know if it'll work, but I would do something like this:

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

            let subreq = from ts2 in entddb.TB_PR_DTL_RECORD
                         group ts2 by ts2.PR_NUMBER into g
                         select new { PR_NUMBER = g.Key, PR_ID_CTGY_DATE = g.Max(d => d.PR_ID_CTGY_DATE) }
                        
            where subreq.Contains(ts.PR_NUMBER) && subreq.Contains(ts.PR_ID_CTGY_DATE)
            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 
                       };

Writing the where statement gives me an error like: IQueryable<> does not contain a definition for 'Contains' — BlackSD, Dec 06 '22 at 19:47

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

2 Answers2