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
};
CodePudding user response:
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);
CodePudding user response:
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
};