c# - Query to many-to-many joiner table in EDMX -

i facing performance problem whenever i'm accessing navigation property of entity.

i have 3 tables: usercategory,user , usercategoryuser. there many-to-many relation between user , usercategory tables. usercategoryuser table joiner table , has 2 columns userid , usercategoryid i.e primary keys of user , usercategory tables. joiner table usercategoryuser used maintaining many-to-many relationship between user , usercategory tables.

i'm using database first approach. hence in edmx, user entity have 1 navigation property usercategories. usercategory entity there navigation property users.

i want add user usercategory. before adding, i'm doing check whether user added usercategory or not. in database i've around 100k user records , 1 usercategory. users associated usercategory.

i'm accessing users navigation property follows:

var usercategory = context.usercategories.firstordefault(uc => uc.usercategoryid == usercategoryid);  if (usercategory != null) {     if (usercategory.users.any(u => u.username == username))     {         //other operations     } } 

this code working before hanging have lots of user data. in particular hanging usercategory.users.any(u => u.username == username)) line. tried getting users count still hanging!

usercategory.users.count(); 

i'm not able join query in linq entity joiner table(usercategoryuser) not added entity in edmx.

how solve problem? can check using pure sql or stored procedure want stay away case.

your problem lazy loading enabled, call usercategory.users loads users memory. think, given there few categories, other way round. i.e. user check whether user's category collection contains category being added.

however, if had large number of categories explicitly filter collection. first turn off lazy loading on collection (e.g. removing virtual keyword), try this:

   context.entry(usercategory)               .collection(uc => uc.users)               .query()               .where(u => u.username == username)//i'd put index on username               .load();     if(usercategory.users.any())    {       //other operations    } 

reference: https://msdn.microsoft.com/en-us/data/jj574232.aspx#explicitfilter