Cannot insert the value NULL into column 'RoleId' (mvc4 simple membership)

Question

I noticed that someone else has faced the same problem such as Cannot insert the value NULL into column 'UserId' but it should be caused by different reasons.

The problem can be simplified like this:

            UsersContext _usersContext = new UsersContext();
            ...
            var usersInRole = new UsersInRole() { RoleId = 3, UserId = 1 };
            _usersContext.UsersInRoles.Add(usersInRole);
            _usersContext.SaveChanges();

The last line of code threw an exception

"An error occurred while updating the entries. See the inner exception for details."

, and the InnerException was saying the same thing

"An error occurred while updating the entries. See the inner exception for details."!

Fortunately, the InnerException of the InnerException says

"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."

. which means "RoleId = 3" was modified or ignored, how could that happen?

Some other code might help are listed below:

[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<UsersInRole> UsersInRoles { get; set; }
}

and table creation scripts:

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])
);

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):

            UsersContext _usersContext = new UsersContext();
            ...
            var usersInRole = _usersContext.UsersInRoles.SingleOrDefault(i => i.RoleId == 3 && UserId == 1);
            _usersContext.UsersInRoles.Remove(usersInRole);
            _usersContext.SaveChanges();

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.

Here is the solution:

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:

            foreach (var role in roles)
            {
                foreach (var user in UserProfile.GetAllUserProfiles(_usersContext))
                {
                    var usersInRole = _usersContext.UsersInRoles.SingleOrDefault(uio => uio.RoleId == role.RoleId && uio.UserId == user.UserId);
                    var key = user.UserId + "_" + role.RoleId;
                    if (collection.AllKeys.Any(i => i == key) && collection[key].Contains("true") && usersInRole == null)
                    {
                        Roles.AddUserToRole(user.UserName, role.RoleName); //Key codes!
                    }
                    if (collection.AllKeys.Any(i => i == key) && !collection[key].Contains("true") && usersInRole != null)
                    {
                        Roles.RemoveUserFromRole(user.UserName, role.RoleName); //Key codes!
                    }
                }
            }

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:

RoleId UserId
5      1
5      2
5      3

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:

    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])
    );

2. Add the new column to the entity RIGHT UNDER THE [KEY]:

[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; }
}

Now I get (5, 1)(5, 2)(5, 3)!

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.

Fruit

Awesome!! Glad you have got it worked out - sounds like you just learned a whole lot about EF, primary keys and the data annotations surrounding them!! – Oleviaolfaction

Ha, in fact it's the first time I "used" EF (all EF codes were generated automatically by MVC4 membership provider), that's why I was lost the last time. Thanks again. – Fruit

Answer 1

I think your problem is with the following section

public partial class UsersInRole
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int RoleId { get; set; }
    public int UserId { get; set; }
}

Having the Key and DatabaseGeneratedAttribute is telling EF that the database is going to assign the value (IDENTITY column). However, your database is not auto assigning this value. So, when EF is trying to do the insert, it is ignoring your RoleId that you assigned because it thinks the database is going to auto assign it. Remove those two attributes on the RoleId property and see if you continue to have the same issues.

Oleviaolfaction

You have the point! Thanks a lot. Yet the solution is tricky. Pls see my comment in my post. – Fruit

Answer 2

1) Why are you using DbContext to add Roles to user? There are some native Membership methods to do it:

Roles.AddUsersToRole(string[] usernames, string rolename)
Roles.AddUserToRole(string username, string rolename) //**This one fits your purpose
Roles.AddUserToRoles(string username, string[] rolenames)
...

2) Value in key column can't be null

3) Int can't be null in C#. If you want to set null value to int, you should define it as nullable: int?

Tutt