Yet again the amazing Entity Framework has failed to make something simple, simple... I have created a really basic set of models, Company, County, User's have a M:1 relationship with Company. So I have an extra table I created called CompanyUsers that I believe will generate the table design I like.
However now I cannot enter a basic user into the database claiming that I cannot have a null UserId even though I am setting the Id.
I understand that my table design is weird, but I may eventually want to switch to a M:M relationship and having this table setup is nice for that. Secondly, having the data in 3 tables makes writing SQL queries easier because of joins.
Exact Error:
System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'UserId', table 'SSI.Database.Context.Software.dbo.Users'; column does not allow nulls. INSERT fails.
Seed:
var _company = new Company()
{
CompanyId=1,
Name="Company Inc."
};
var _counties = new List<County>() {
new County(){ Name="Place1" },
new County(){ Name="Place2"}
};
context.Companies.AddOrUpdate(_company);
var _user = new User() {UserId=1, CompanyId=_company.CompanyId, Company=_company, Name = "John Smith" };
if (context.Counties.Count() == 0)
{
context.Counties.AddRange(_counties);
}
context.Users.AddOrUpdate(_user);
All my models:
Company:
public class Company
{
public Company()
{
Users = new List<User>();
}
[Key]
public int CompanyId { get; set; }
public string Name { get; set; }
public ICollection<User> Users { get; set; }
}
User:
public class User
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int UserId { get; set; }
public string Name { get; set; }
public Nullable<int> CompanyId { get; set; }
[ForeignKey("CompanyId")]
public Company Company { get; set; }
}
CompanyUser Table:
public class CompanyUsers
{
[Key]
[Column(Order = 1)]
public int CompanyId { get; set; }
[ForeignKey("CompanyId")]
public virtual Company Company { get; set; }
[Key]
[Column(Order = 2)]
public int UserId { get; set; }
[ForeignKey("UserId")]
public virtual User User{ get; set; }
}