linq - How to connect a users table correctly to a Membership table, or should you even do that? -

i'm going wrong way, please feel free correct me.

currently have user model called u_user, contains fields such address, receivenotifications, hascompanycar etc.

these users have roles in .net membership table. when user logs in via membership, user record in database follows (the username on membership table , own u_user table have match):

        //gets current user         public u_user currentuser()         {             return getuser(httpcontext.current.user.identity.name);         }          //gets user details username         public u_user getuser(string username)         {             return (from u in db.u_user                    u.username == username                    select u).firstordefault();         } 

if wanted list of users in, lets say, "create" role this:

allusers.where(x => roles.isuserinrole(x.username, "create")) 

this big performance hit it's doing lookup each iteration of user. makes me think i'm not going user management in correct way. answer question:

how should connect membership users table in turn connected rest of data? i'd accept how go more efficiently!

many :)

edit :

i've increased performance via below code. users in role in 1 swoop , filter them.

string[] usersinrole = roles.getusersinrole("canapprove"); users = users.where(x => usersinrole.any(y => y == x.username)); 

but i'm still sure i'm going wrong!

not expert in this, apps typically use foreign key (with index) own users table membership users table using guid field membership. allows me queries using linq like:

 var query = myuser in myusers             join aspuser in aspnet_users on myuser.userid equals aspuser.userid             join usersinrole in aspnet_usersinroles on aspuser.userid equals usersinrole.userid             join role in aspnet_roles on usersinrole.roleid equals role.roleid             role ...             select new { ... }; 

(or can use dot-form myuser.aspuser.roles.role let orm generate joins if prefer)

for performance, it's watch sql trace - make sure you're not making many sql round-trips each logical step in code.

hope helps bit.

---

update - in answer questions "should that", think "yes" there other options available - e.g. can use profile fields - see step 6 in great walkthrough - http://www.4guysfromrolla.com/articles/120705-1.aspx