c# - Linq many to many relation Join -

c# - Linq many to many relation Join -

i have 4 tables, usercredential, userprofile, userroles , role

var user = (from uc in db.usercredentials bring together in db.userprofiles on uc.userid equals up.userid bring together ur in db.userroles on uc.userid equals ur.userid select new { credetial = uc, profile = up, roles = db.roles.where(r => r.roleid == ur.roleid)}) .firstordefault();

userrole has userid , roleid, user can have multiple roles.

query generated above code doesn't seem efficient. can suggest improve code

firstly, since we're worried performance, create sure database has indexes on of userid , roleid columns.

since have multiple userroles, including in bring together wastefully increases cardinality of query, , subsequently calling firstordefault pulls downwards one. one? haven't shown how select particular user, i'll leave fix, unless it's filtered in 1 of info sources.

also, roles property on anonymous object: every time touch hitting database. source of performance problems. if it's acceptable cache information, finishing subquery tolist() phone call prudent.

that subquery source of trouble, if using tolist - it'll trip database, create sure maintain cardinality of main query low command number of trips.

var user = (from uc in db.usercredentials bring together in db.userprofiles on uc.userid equals up.userid //where uc.userid == somepassedinuserid /* add together line if datasources aren't filtered */ select new { credetial = uc, profile = up, roles = (from ur in db.userroles bring together r in db.roles on ur.roleid equals r.roleid ur.userid == uc.userid select r).tolist() .firstordefault();

c# linq entity-framework linq-to-sql