I am trying to convert the sql query to Entity framework.but i am not able to convert the nested query can some one help on this.
AND TMM100_ITEM.itm_int_id IN (SELECT A.itm_int_id FROM TMM500_ITEM_VENDOR A WITH (NOLOCK) WHERE A.mfg_int_id = 666)
My sql query
SELECT TMM100_ITEM.itm_ext_id
FROM TMM100_ITEM WITH (NOLOCK)
JOIN TMM500_ITEM_VENDOR WITH (NOLOCK) ON TMM100_ITEM.itm_int_id = TMM500_ITEM_VENDOR.itm_int_id
AND TMM500_ITEM_VENDOR.vnd_rnk_no = 1
JOIN TMM140_MANUF WITH (NOLOCK) ON TMM500_ITEM_VENDOR.mfg_int_id = TMM140_MANUF.mfg_int_id
JOIN TAP300_VENDOR_MASTER WITH (NOLOCK) ON TMM500_ITEM_VENDOR.vnd_int_id = TAP300_VENDOR_MASTER.vnd_int_id
WHERE TMM100_ITEM.crp_int_id = 18
AND ISNULL(stk_ty_fg, 'S') <> 'C'
AND TMM100_ITEM.row_sta_cd = 'A'
AND TMM100_ITEM.itm_int_id IN (SELECT A.itm_int_id FROM TMM500_ITEM_VENDOR A WITH (NOLOCK) WHERE A.mfg_int_id = 666)
What I have tried:
Entitty framework
var Query = (from t100 in context.TMM100_ITEM
join t500 in context.TMM500_ITEM_VENDOR on t100.itm_int_id equals t500.itm_int_id
join t140 in context.TMM140_MANUF on t500.mfg_int_id equals t140.mfg_int_id
join t300 in context.TAP300_VENDOR_MASTER on t500.vnd_int_id equals t300.vnd_int_id
where t100.crp_int_id == 18 && t100.stk_ty_fg != "C" && t100.row_sta_cd == "A" && t140.mfg_int_id == value && t500.vnd_rnk_no == 1 && t500.row_sta_cd == "A"
select t100.itm_int_id);