Click here to Skip to main content
14,086,389 members
Rate this:
 
Please Sign up or sign in to vote.
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);
Posted
Updated 8-Mar-18 0:48am
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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:
//get all invoices where specific product is on it
var result = DbContext.Invoices.Where(x=>x.Items.Any(y=>y.ProductID==valueToFind));


Good luck!
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web02 | 2.8.190518.1 | Last Updated 8 Mar 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100