none
Many to Many insert exception upon SubmitChanges() RRS feed

  • Question

  • I have two tables, Users and Roles and a m2m table between them, UserRoles.  The Delete rules are ser to Cascade.  When I run AddUserWithRole()I get a new User and a new UserRole - this is good.  But if I comment out the u.UserRoles.Add(ur) line, I get an error message:

     

    System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UserRoles_User". The conflict occurred in database "LINQ", table "dbo.User", column 'ID'.

     

    What is the reason behind this behavior?

     

    public void AddUserWithRole()

    {

    LINQDataContext dc = new LINQDataContext(...);

    User u = new User();

    u.Name = "User " + DateTime.Today.TimeOfDay.Milliseconds.ToString();

    Role r = dc.Roles.First();

    UserRole ur = new UserRole();

    ur.Role = r;

    u.UserRoles.Add(ur);  // if I comment this out, I get an exception

    dc.Users.InsertOnSubmit(u);

    dc.SubmitChanges();

    }

     

     

     

    Wednesday, August 6, 2008 6:49 PM

Answers

  • You get this exception because your UserRole needs both a User and a Role in order to be complete. You only assign the Role directly, but not the user. Instead you have the statement that adds the UserRole to the user. When you do this, the UserRole.User property is automatically assigned. When you comment out the statement, your UserRole has no User and therefore no user id.
    Thursday, August 7, 2008 1:23 AM
    Moderator

All replies

  • You get this exception because your UserRole needs both a User and a Role in order to be complete. You only assign the Role directly, but not the user. Instead you have the statement that adds the UserRole to the user. When you do this, the UserRole.User property is automatically assigned. When you comment out the statement, your UserRole has no User and therefore no user id.
    Thursday, August 7, 2008 1:23 AM
    Moderator
  • Thanks for the explanation Matt

     

    Thursday, August 7, 2008 4:53 PM