无法插入NULL值插入列'角色ID“（mvc4简单会员） [英] Cannot insert the value NULL into column 'RoleId' (mvc4 simple membership)
问题描述
我注意到，其他人面临同样的问题，如<一个href=\"http://stackoverflow.com/questions/15346608/cannot-insert-the-value-null-into-column-userid\">Cannot插入NULL值插入列'用户ID'但应该由不同的原因造成的。
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.
问题可以简化如下：
UsersContext _usersContext = new UsersContext();
...
var usersInRole = new UsersInRole() { RoleId = 3, UserId = 1 };
_usersContext.UsersInRoles.Add(usersInRole);
_usersContext.SaveChanges();
code的最后一行抛出一个异常
The last line of code threw an exception
时发生错误。请参阅内部异常
了解详细信息。
"An error occurred while updating the entries. See the inner exception for details."
，并在的InnerException说同样的事情
, and the InnerException was saying the same thing
时发生错误。请参阅内部异常
有关详细信息！
"An error occurred while updating the entries. See the inner exception for details."!
幸运的是，的InnerException的的InnerException说：
Fortunately, the InnerException of the InnerException says
无法插入NULL值插入列'角色ID'，表
MFC.dbo.webpages_UsersInRoles';列不允许空值。插
失败。\\ r \\ n此语句已终止。
"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."
。这意味着角色ID = 3被修改或忽略了，怎么可能发生呢？
. which means "RoleId = 3" was modified or ignored, how could that happen?
其他的一些code可能有助于列举如下：
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; }
}
和表创建脚本：
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])
);
另一件事野趣是，我可以从上下文中删除usersInRole，即以下code是确定（如果我手动添加的记录）：
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();
似乎很少有人使用和简单交谈成员，所以我havevn't从谷歌发现了很多有用的资源。因此，任何帮助将AP preciated。谢谢你。
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.
由于汤米说，这个问题是由[关键]造成的属性。
而不是删除[键]（这将导致类似实体类型没有定义键的错误），我改变了code到官方的解决方案：
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!
}
}
}
似乎Roles.AddUserToRole和Roles.RemoveUserFromRole能正确地做到这一点。
但它尚未完成.....奇怪的是，_userContext.UsersInRoles不能返回正确的结果。例如，如果在表中的数据是：
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
它返回3条记录（5,1）（5,1）（5,1）而不是（5,1）（5,2）（5,3）。这是汤米答复中提到但Roles.Add绕过的东西/删除（）。解决的办法是：
1. [ID]列添加到表：
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。新列添加到实体正下方[KEY]：
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; }
}
现在我得到（5,1）（5,2）（5,3）！
Now I get (5, 1)(5, 2)(5, 3)!
我知之甚少的数据库，但汤米提到的，这个应该根据申报角色ID引起的[关键] [数据库...]在脚本PRIMARY KEY CLUSTERED（[用户ID] ASC，[角色ID] ASC）一起
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.
推荐答案
我觉得你的问题是与以下部分
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; }
}
具有键和 DatabaseGeneratedAttribute 告诉数据库将要赋值EF（IDENTITY列） 。然而，你的数据库是不是自动分配此值。所以，当EF试图做插入，它忽略了分配，因为它认为该数据库会自动分配给它的角色ID。在角色ID属性中删除这两个属性，看看是否继续有同样的问题。
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.
这篇关于无法插入NULL值插入列'角色ID“（mvc4简单会员）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
