Multiplicity constraint violated VS 2010 / SQL Server / EF code-first

Question

I am new to MVC/C# and trying to work on a small project using EF code-first which includes the following 4 classes: Office, Role, User and OfficeUser.

Problem is I am trying to use same Role class between User (define master RoleId FK Role.RoleId ) and OfficeUser (define OfficeRoleId for particular office FK to Role.RoleId), Also User and OfficeUser have their own relationship.

So I am trying write my authorization using AuthorizeAttribute for which I need to get the roles of the particular user when he/she logs into the site. And when I perform a get

public User GetUserRoleByUserName(string userName, string passWord) { using (var context = DataContext) { return context.Users.Include("Role") .Include("OfficeUsers") .SingleOrDefault(u => u.UserName == userName && u.Password == passWord); } }

I get following error

Multiplicity constraint violated. The role 'User_OfficeUsers_Target' of the relationship 'Inventory.Repository.User_OfficeUsers' has multiplicity 1 or 0..1.

Can you please tell me how can I fix this?

public class Office { public Office() { OfficeUsers = new HashSet<OfficeUser>(); } public int OfficeId { get; set; } public string OfficeName { get; set; } public virtual ICollection<OfficeUser> OfficeUsers { get; set; } } public class Role { public Role() { Users = new HashSet<User>(); OfficeUsers = new HashSet<OfficeUser>(); } public int RoleId { get; set; } public string RoleName { get; set; } public string Description { get; set; } public virtual ICollection<User> Users { get; set; } public virtual ICollection<OfficeUser> OfficeUsers { get; set; } } public class User { public User() { OfficeUsers = new HashSet<OfficeUser>(); } public int UserId { get; set; } public string UserName { get; set; } public string Password { get; set; } public int RoleId { get; set; } public virtual ICollection<OfficeUser> OfficeUsers { get; set; } public virtual Role Role { get; set; } } public class OfficeUser { public OfficeUser() { } public int OfficeUserId { get; set; } public int OfficeId { get; set; } public int UserId { get; set; } [ForeignKey("Role")] public int OfficeRoleId { get; set; } public bool Active { get; set; } public User User { get; set; } public Office Office { get; set; } public Role Role { get; set; } }

Apart from a multiple delete path problem I couldn't reproduce the error. — Eranga
– Eranga, Commented Mar 19, 2012 at 0:45

Jayanga · Accepted Answer · 2012-03-22 05:14:05Z

This may happen if you have not put any configuration to map the relation between User and Office. here is a sample configuration class

public class UserConfiguration : EntityTypeConfiguration<User> { public LocationConfiguration() { HasKey(a => a.Id); HasMany(user => user.OfficeUsers).WithOptional(officeuser => officeuser.User). HasForeignKey(officeuser => officeuser.UserId); } }

and add this configuration to your context as follows

public class YourContext : DbContext { // your DBSets and contructors, etc protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new UserConfiguration()); base.OnModelCreating(modelBuilder); } }

EDIT

try removing the data annotation "[ForeignKey("Role")]" and then add a configuration to Role class as follows. And also add virtual keyword to Role property in OfficeUser class

public class RoleConfiguration : EntityTypeConfiguration<Role> { public LocationConfiguration() { HasKey(a => a.RoleId); HasMany(role =>role.OfficeUsers).WithOptional(officeuser => officeuser.Role). HasForeignKey(officeuser => officeuser.OfficeRoleId); } }

EDIT

declare the OfficeRoleId as

public int? OfficeRoleId { get; set; }

When you query for an OfficeUser you can use below method to use Lazy loading

YourQuery().Include(officeuser => officeuser.Role);

hope you understand the syntax Regards

Thank you Jayanga,It worked using your code "modelBuilder.Entity<User>().HasMany(user =>user.OfficeUsers).WithOptional(officeuser =>officeuser.User).HasForeignKey(officeuser => officeuser.UserId);" but now i get this error "Multiplicity conflicts with the referential constraint in Role 'OfficeUser_Role_Target' in relationship 'OfficeUser_Role'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'." using modelb.Entity<OfficeUser>().HasOptional(c =>c.Role).WithMany(p => p.OfficeUsers).HasForeignKey(c => c.OfficeRoleId);Can u help?
Thanks for the help I had tried and it still gives me this error "Multiplicity conflicts with the referential constraint in Role 'Role_OfficeUsers_Source' in relationship 'Role_OfficeUsers'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'." modelBuilder.Entity<Role>().HasMany(role => role.OfficeUsers).WithOptional(officeuser => officeuser.Role). HasForeignKey(officeuser => officeuser.OfficeRoleId);
didn't work, if i try to comment previous config. that we tried betwn OfficeUsers and Users and OfficeUsers and Roles. I get the result back for admin users with data in User, User.OfficeUsers(6 count), and Users.OfficeUsers(6).Role all having data, but when I use a regular user with access to only one office only I get User, User.OfficeUsers(1 count) has data, and Users.OfficeUsers(1).Role is null thought i see value for Users.OfficeUsers(1).OfficeRoleId
Yes, that can happen because EntityFramework is using Eager loading at that time. Therefore the Role object is not filled but OfficeRoleId is filled because it is just a value (not an object). You can handle this by using Lazy Loading instead of eager loading, will update the answer with a sample

Collectives™ on Stack Overflow

Multiplicity constraint violated VS 2010 / SQL Server / EF code-first

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related