asp.net mvc - Accessing columns in cross reference table via LINQ with Entity Framework -

i have users table, roles table, , cross reference table users_roles has following columns:

user_id role_id beamline_id facility_id laboratory_id

the beamline_id, facility_id, laboratory_id filled in depending on role_id. if has role_id of 2 ("lab admin") have entry in laboratory_id.

i trying figure out how laboratory_id specific row in table. example, know have user_id = 1. want laboratory_id user_id = 1 , role_id = 2 ("lab admin").

this simple when dealing sql new entity framework , trying entities , i'm having trouble. using mvc in controller have done this:

user user = new user(); user.getuser(user.identity.name); var labid = user.users_roles.where(r => r.role_id == 2); 

that should me "row" of user when role = lab admin don't know how grab labortory_id column now. thought maybe be:

var labid = user.users_roles.where(r => r.role_id == 2).select(l => l.laboratory_id); 

but not correct. appreciated.

edit: using database first approach , using dbcontext. typically access context this:

var context = new passentities(); 

as why code you've tried doesn't work, @ first glance think need tack on .single() end:

var labid = user.users_roles.where(r => r.role_id == 2)                             .select(l => l.laboratory_id)                             .single(); 

select() returns sequence, , looks want single value, think might cause of problem having. single() throw exception if there's not 1 result, sounds appropriate table structure, there's first() if don't care enforcing that.

but kind of thing, might think querying manually using dbcontext, instead of trying load entities individually , traversing down through navigation properties -- results in more local searching need, and, depending on circumstances, might performing more/larger queries against context , database necessary too.

here's example linq query use (you might have adjust names of tables , on):

 var labid = (from ur in context.users_roles               ur.user_id == 1 && ur.role_id == 2               select ur.laboratory_id).single(); 

when execute statement, translated sql equivalent this:

select top 1 laboratory_id users_roles user_id = 1 , role_id = 2 

...so it's pretty efficient. (actually, if user_id , role_id form primary key users_roles table, , record has been loaded previously, think it'll return cached copy, , won't need query database @ all.)

that's bare-bones query, though; more likely, you're going want query based on properties of user or role, instead of searching hard-coded ids know in advance. in case, can adjust query this:

var labid = (from u in context.users              join ur in context.users_roles on u.user_id equals ur.user_id              join r in context.roles on ur.role_id equals r.role_id              u.username == "john smith" && r.rolename == "lab admin"              select ur.laboratory_id).single(); 

that would, can tell, return lab id user john smith under role lab admin.

does make sense? linq syntax can take little getting used to.