Insert into bridge table entity framework

Question

Hi guys,
I'm learning to climb with EF ,I do have basic understanding of CRUD with EF ,but now I have a table which have a navigation property (Which I suspect is the bridge table) ,so I need to add value into the bridge table ,I think I can do it with navigational property.

Problem Explained:
Original partial DB Diagram enter image description here

Partial EF Model Diagram
enter image description here

Code I Wrote:

   protected void BtnAddUser_Click(object sender, EventArgs e)
    {
        DBEntities entities = new DBEntities();
        var usr = new User();
        //I thought I would add an Roles object into usr.UserRoles.Add(usrRoles);
        //but UserRoles have only two fields ,RoleTypeId and UserId 
        //var usrRoles = new Roles() 
        //{Id=0,RoleDescription="dfdfdf",RoleType="WebSite Admin"}; 

        usr.UserName = TxtbxUserName.Text;
        usr.Password = TxtBxPassword.Text;
        usr.Email = TxtbxEmail.Text;
        usr.CreateDate = DateTime.Now;
        usr.LastActivityDate = DateTime.Now;
        usr.IsEnabled = true;

        //What to Add in the .Add method
        usr.UserRoles.Add(

        entities.User.AddObject(usr);
        int result = entities.SaveChanges();
        LblMsg.Text = result == 1 ? "User created successfully." : "An error occured    ,please try later.";

        entities.Dispose();
    }

Update (What I have tried so far):
I fetch "Website Admin" role from roles table and put it into ObjectContext.UserRoles.Add(UserRoleWebsiteAdmin); So that what I did in the code,

        //Fetch WebsiteAdmin from Roles
        var userRole = from usrRole in entities.Roles
                       where usrRole.Id == 1
                       select usrRole;

        usr.UserName = TxtbxUserName.Text;
        //same old code of usr.Property = someTextBox
        //I have tried to type cast it LinqtoEntities result into Roles
        usr.UserRoles.Add((Roles)userRole);

Exception generated
enter image description here

P.S: Let me know if you need more clarification.

What is `UserRoles`? You don't have such navigation property in your model described by diagram. You just need to add `Role` to `Roles` property. — Ladislav Mrnka, May 31 '12 at 17:00
@LadislavMrnka 'UserRoles' is the bridge(or junction) table ,Upon user creation user must be given a role (or roles) ,which will be referenced from the 'Roles' table. — DayTimeCoder, May 31 '12 at 17:47
But that table is not in your model. You don't need it you just add `Role` to `user.Roles` and EF will handle it. — Ladislav Mrnka, May 31 '12 at 17:49
@LadislavMrnka A little code example would helpful for noobs like me :) — DayTimeCoder, May 31 '12 at 17:50
How about that, If I fetch "Website Admin" role from roles table and put it into like usr.UserRoles.Add(UserRoleWebsiteAdmin); — DayTimeCoder, May 31 '12 at 17:54
Look again at the entity picture you posted in your question - the one where you marked `Roles` property. Where is `UserRoles` in that picture? — Ladislav Mrnka, May 31 '12 at 17:58
@LadislavMrnka You are right its not there,then how do I insert into bridge table ,check my previous comment ,I think that will work. — DayTimeCoder, May 31 '12 at 18:00
What he's saying is that Entity Framework will do this work for you because of the navigation properties which are showing up on your image. Because you have two FK in the mapping table, entity framework is smart enough to know what you're doing and just does it behind the scenes for you. — Chris, May 31 '12 at 18:02
To your Update: `userRoles` is only a *query*, you need to *execute* it to get a role *entity*: `var userRole = (from usrRole ... select usrRole).Single();` for example. — Slauma, May 31 '12 at 18:27
You need to understand Many-to-Many relationship in EF http://vincentlauzon.wordpress.com/2011/04/15/entity-framework-4-1-many-to-many-relationships-5/ and http://buildstarted.com/2010/08/27/entity-framework-with-many-to-many-one-to-many-and-one-to-one-relationships/ — Matija Grcic, May 31 '12 at 20:16

score 2 · Accepted Answer · answered May 31 '12 at 22:03

Maybe you can use using http://msdn.microsoft.com/en-us/library/yh598w02.aspx and object initializer http://msdn.microsoft.com/en-us/library/bb384062.aspx for better readability so:

using(DBEntities entities = new DBEntities())
{
       //Make user object
       var user = new User{
            UserName = TxtbxUserName.Text,
            Password = TxtBxPassword.Text,
            Email = TxtbxEmail.Text,
            CreateDate = DateTime.Now,
            LastActivityDate = DateTime.Now,
            IsEnabled = true
        };

       //Fetch type of Role from Roles table
       var userRole = entities.Roles.Where(x=>x.usrRole.Id ==1).Single();
       user.UserRoles.Add(userRole);

       entities.User.AddObject(user);
       int result = entities.SaveChanges();
       LblMsg.Text = result == 2 ? "User created succesfully." : "An error occured ,please try later.";
}

Regards

Thanks for the suggestion , I like it , +1 for that – DayTimeCoder May 31 '12 at 23:18 — DayTimeCoder, May 31 '12 at 23:18

DayTimeCoder · Answer 2 · 2012-05-31T21:11:40.473

Well thanks guys... Here what I have done and it works,

       DBEntities entities = new DBEntities();
       //Make user object
       var usr = new User();
       //Fetch type of Role from Roles table
       var userRole = (from usrRole in entities.Roles
                       where usrRole.Id == 1
                       select usrRole).Single();
        //copy user related info from textboxes
        usr.UserName = TxtbxUserName.Text;
        usr.Password = TxtBxPassword.Text;
        usr.Email = TxtbxEmail.Text;
        usr.CreateDate = DateTime.Now;
        usr.LastActivityDate = DateTime.Now;
        usr.IsEnabled = true;


        usr.UserRoles.Add(userRole as Roles);

        entities.User.AddObject(usr);
        int result = entities.SaveChanges();
        LblMsg.Text = result == 2 ? "User created succesfully." : "An error occured ,please try later.";

        entities.Dispose();

Insert into bridge table entity framework

2 Answers2