Entity Framework 6 database first many to many relationship
EDIT:
Your are missing the AssignedRoles
table. I added the .edmx to my project and i have this entity AssignedRole
. Try recreate your edmx.
Old Answer (Code First):
I just tried using your database structure and everything works fine.
EmployeeDbdb = new EmployeeDb();
var empl = new Employee
{
FirstName = "Test",
LastName = "demo",
Email = "[email protected]"
};
var role = new Role
{
Name = "Role1"
};
db.Roles.AddOrUpdate(role);
db.Employees.AddOrUpdate(empl);
db.SaveChanges();
db.AssignedRoles.AddOrUpdate(new AssignedRole
{
EmployeeId = empl.Id,
RoleId = role.Id
});
db.SaveChanges();
OR:
EmployeeDbdb = new EmployeeDb();
var empl = new Employee
{
FirstName = "Test",
LastName = "demo",
Email = "[email protected]"
};
var role = new Role
{
Name = "Role1"
};
db.Roles.AddOrUpdate(role);
db.Employees.AddOrUpdate(empl);
db.AssignedRoles.AddOrUpdate(new AssignedRole
{
Role = role,
Employee = empl
});
db.SaveChanges();
EvilToaster101
Updated on June 04, 2022Comments
-
EvilToaster101 almost 2 years
I am trying EF6 and trying to utilize a many to many relationship.
Using Database first here is my scripted out database.
CREATE TABLE [States] ( Id int identity (1, 1) not null primary key, Name varchar(50) not null, Abbreviation varchar(2) not null ) GO CREATE TABLE Departments ( Id int identity (1, 1) not null primary key, Name varchar(50), ) GO CREATE TABLE [Role] ( Id int identity (1, 1) not null primary key, Name varchar(50) ) GO CREATE TABLE Employees ( Id int identity (1, 1) not null primary key, FirstName varchar(50), LastName varchar(50), Email varchar(255), DepartmentId int constraint fk_Department_Id foreign key references Departments(Id) ) GO CREATE TABLE AssignedRoles ( Id int identity (1, 1) not null primary key, EmployeeId int not null constraint fk_Employee_Id foreign key references Employees(Id), RoleId int not null constraint fk_Role_Id foreign key references [Role](Id), ) GO CREATE TABLE [Addresses] ( Id int identity (1, 1) not null primary key, EmployeeId int not null, StreetAddress varchar(255), City varchar(55), StateId int not null, ZipCode varchar(10), CONSTRAINT fk_Employee_Id_Address foreign key (EmployeeId) REFERENCES [Employees](Id), CONSTRAINT fk_State_Id foreign key (StateId) REFERENCES [States](Id) ) GO
My Code:
public MicroOrmComparison.UI.Models.Employee Add(MicroOrmComparison.UI.Models.Employee employee) { var employeeToInsert = AutoMapper.Mapper.Map<MicroOrmComparison.UI.Models.Employee, Employee>(employee); using (var db = new EmployeeDb()) { db.Employees.AddOrUpdate(employeeToInsert); if (employeeToInsert.Addresses != null) { foreach (var address in employeeToInsert.Addresses) { db.Addresses.AddOrUpdate(address); } } if (employeeToInsert.Roles != null) { foreach (var role in employeeToInsert.Roles) { role.Employees.Add(employeeToInsert); db.Roles.AddOrUpdate(role); db.Employees.AddOrUpdate(employeeToInsert); } } db.SaveChanges(); employee.Id = employeeToInsert.Id; } return employee; }
Generated Employee from EF6 database first
//------------------------------------------------------------------------------ // <auto-generated> // This code was generated from a template. // // Manual changes to this file may cause unexpected behavior in your application. // Manual changes to this file will be overwritten if the code is regenerated. // </auto-generated> //------------------------------------------------------------------------------ namespace EntityFramework.DataLayer { using System; using System.Collections.Generic; public partial class Employee { public Employee() { this.Addresses = new HashSet<Address>(); this.Roles = new HashSet<Role>(); } public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Email { get; set; } public Nullable<int> DepartmentId { get; set; } public virtual ICollection<Address> Addresses { get; set; } public virtual Department Department { get; set; } public virtual ICollection<Role> Roles { get; set; } } }
Generated Code for Role
//------------------------------------------------------------------------------ // <auto-generated> // This code was generated from a template. // // Manual changes to this file may cause unexpected behavior in your application. // Manual changes to this file will be overwritten if the code is regenerated. // </auto-generated> //------------------------------------------------------------------------------ namespace EntityFramework.DataLayer { using System; using System.Collections.Generic; public partial class Role { public Role() { this.Employees = new HashSet<Employee>(); } public int Id { get; set; } public string Name { get; set; } public virtual ICollection<Employee> Employees { get; set; } } }
The Guilty Test that is failing
[Test] public void ShouldAddRolesToUser() { //Arrange var testUserId = InsertUserToBeModified(); //Act var employee = _employeeRepository.GetFullEmployeeInfo(testUserId); employee.Roles.Add(new MicroOrmComparison.UI.Models.Role { Id = 3, Name = "Supervisor" }); _employeeRepository.Save(employee); //Assert var result = _employeeRepository.GetFullEmployeeInfo(testUserId); result.Roles.Count().Should().Be(1); result.Roles.First().Id.Should().Be(3); //Cleanup _employeeRepository.Remove(testUserId); }
The test says result.Roles.Count() is 0.
My issue is trying to add to the join table AssignedRoles. I have tried multiple inserts within the foreach within the role block but still no luck. I have searched within this site but still no luck. I have been working with Micro ORMs which is why the magic of the join table is blowing my mind. Any help would be greatly appreciated. I have more code if needed, just let me know what code is unclear.
When I debug within the foreach loop its not adding to the join table. HELP
-
EvilToaster101 about 10 yearsimgur.com/vEZZdB1 This is what I get when I try to do db.AssignedRoles, is there something I am missing? I thought entity was supposed to know the relationship between employee and role and that a specific call to AssignedRoles was not needed.
-
Mihai Hantea about 10 yearsThat's great.If the answer help fixing the problem can you please check the mark.
-
Kelly S. French about 7 yearsYes, but how do you do it for database first?