Click here to Skip to main content
16,018,418 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
SQL
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
SQL
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

C#
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);
Posted
Updated 8-Mar-18 0:48am
v2

1 solution

There's few ways to implement IN clause in Entity Framework.

Most of developers recommend to use Contains()[^] method. See:
sql - Entity Framework - attribute IN Clause usage - Stack Overflow[^]
MVC Where IN clause for Entity Framework | The ASP.NET Forums[^]
Entity Framework - Dealing with large 'WHERE IN' statements[^]

Another way to resolve it is to use Where[^] + Any[^]. In that case, an entity have to be a collection:
C#
//get all invoices where specific product is on it
var result = DbContext.Invoices.Where(x=>x.Items.Any(y=>y.ProductID==valueToFind));


Good luck!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900