title: Cannot insert the value NULL into column 'RoleId' (mvc4 simple membership)
body: <p>I noticed that someone else has faced the same problem such as <a href="https://stackoverflow.com/questions/15346608/cannot-insert-the-value-null-into-column-userid">Cannot insert the value NULL into column &#39;UserId&#39;</a> but it should be caused by different reasons.</p>
  
  <p>The problem can be simplified like this:</p>
  
  <pre><code>            UsersContext _usersContext = new UsersContext();
              ...
              var usersInRole = new UsersInRole() { RoleId = 3, UserId = 1 };
              _usersContext.UsersInRoles.Add(usersInRole);
              _usersContext.SaveChanges();
  </code></pre>
  
  <p>The last line of code threw an exception </p>
  
  <blockquote>
    <p>"An error occurred while updating the entries. See the inner exception
    for details."</p>
  </blockquote>
  
  <p>, and the InnerException was saying the same thing </p>
  
  <blockquote>
    <p>"An error occurred while updating the entries. See the inner exception
    for details."!</p>
  </blockquote>
  
  <p>Fortunately, the InnerException of the InnerException says </p>
  
  <blockquote>
    <p>"Cannot insert the value NULL into column 'RoleId', table
    'MFC.dbo.webpages_UsersInRoles'; column does not allow nulls. INSERT
    fails.\r\nThe statement has been terminated."</p>
  </blockquote>
  
  <p>. which means "RoleId = 3" was modified or ignored, how could that happen?</p>
  
  <p>Some other code might help are listed below: </p>
  
  <pre><code>[Table("webpages_UsersInRoles")]
  public partial class UsersInRole
  {
      [Key]
      [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
      public int RoleId { get; set; }
      public int UserId { get; set; }
  }
  
  public class UsersContext : DbContext
  {
      public UsersContext()
          : base("MFCConnectionString")
      {
      }
  
      public DbSet&lt;UsersInRole&gt; UsersInRoles { get; set; }
  }
  </code></pre>
  
  <p>and table creation scripts:</p>
  
  <pre><code>CREATE TABLE [dbo].[webpages_UsersInRoles] (
      [UserId] INT NOT NULL,
      [RoleId] INT NOT NULL,
      PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
      CONSTRAINT [fk_UsersInRoels_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[webpages_Roles] ([RoleId]),
      CONSTRAINT [fk_UsersInRoles_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[UserProfile] ([UserId])
  );
  </code></pre>
  
  <p>Another thing intersting is that I can remove an usersInRole from the context, namely, the following code is ok (if I added the record manually):</p>
  
  <pre><code>            UsersContext _usersContext = new UsersContext();
              ...
              var usersInRole = _usersContext.UsersInRoles.SingleOrDefault(i =&gt; i.RoleId == 3 &amp;&amp; UserId == 1);
              _usersContext.UsersInRoles.Remove(usersInRole);
              _usersContext.SaveChanges();
  </code></pre>
  
  <p>Seems few people are using and talking simple membership, thus I havevn't found many helpful resource from Google. So any help will be appreciated. Thanks.</p>
  
  <h2>Here is the solution:</h2>
  
  <p>As Tommy said, this problem is caused by the [Key] attributes.
  Rather than deleting the [Key](which will cause an error like "entity type has no key defined"), I changed the code to offical solution:</p>
  
  <pre><code>            foreach (var role in roles)
              {
                  foreach (var user in UserProfile.GetAllUserProfiles(_usersContext))
                  {
                      var usersInRole = _usersContext.UsersInRoles.SingleOrDefault(uio =&gt; uio.RoleId == role.RoleId &amp;&amp; uio.UserId == user.UserId);
                      var key = user.UserId + "_" + role.RoleId;
                      if (collection.AllKeys.Any(i =&gt; i == key) &amp;&amp; collection[key].Contains("true") &amp;&amp; usersInRole == null)
                      {
                          Roles.AddUserToRole(user.UserName, role.RoleName); //Key codes!
                      }
                      if (collection.AllKeys.Any(i =&gt; i == key) &amp;&amp; !collection[key].Contains("true") &amp;&amp; usersInRole != null)
                      {
                          Roles.RemoveUserFromRole(user.UserName, role.RoleName); //Key codes!
                      }
                  }
              }
  </code></pre>
  
  <p>Seems that Roles.AddUserToRole and Roles.RemoveUserFromRole can do it correctly.
  But it's not finished yet..... Strangely, _userContext.UsersInRoles cannot return correct results. For example, if the data in table is: </p>
  
  <pre><code>RoleId UserId
  5      1
  5      2
  5      3
  </code></pre>
  
  <p>it returns 3 records (5,1)(5,1)(5,1) rather than (5, 1)(5, 2)(5, 3). This is the thing Tommy mentioned in his reply but bypassed by Roles.Add/Remove(). The solution is:
  1. Add a [ID] column to the table: </p>
  
  <pre><code>    CREATE TABLE [dbo].[webpages_UsersInRoles] (
          [ID] INT NOT NULL IDENTITY, --Key codes!
          [UserId] INT NOT NULL,
          [RoleId] INT NOT NULL,
          PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
          CONSTRAINT [fk_UsersInRoels_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[webpages_Roles] ([RoleId]),
          CONSTRAINT [fk_UsersInRoles_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[UserProfile] ([UserId])
      );
  </code></pre>
  
  <p>2. Add the new column to the entity RIGHT UNDER THE [KEY]:</p>
  
  <pre><code>[Table("webpages_UsersInRoles")]
  public partial class UsersInRole
  {
      [Key]
      [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
      public int ID { get; set; } //Key codes!
      public int RoleId { get; set; }
      public int UserId { get; set; }
  }
  </code></pre>
  
  <p>Now I get (5, 1)(5, 2)(5, 3)!</p>
  
  <p>I know little about database, but as Tommy mentioned, this should be caused by declaring RoleId under [Key][Database....] with PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC) in scripts together.</p>