0

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; }
}
9
  • Why are you trying to set the ID for something that's supposed to be auto generated? Commented Aug 14, 2017 at 16:18
  • You're using an identity for UserID. Have you set it up as an identity field in the DB? Commented Aug 14, 2017 at 16:19
  • Because leaving it blank still gives me the error. Commented Aug 14, 2017 at 16:19
  • 1
    If I had to make a wild guess my problem is caused by creating the CompanyUsers table. Commented Aug 14, 2017 at 16:22
  • @BaileyMiller Your guess is correct :) So again, why do you need that new many-to-many table in addition to one-to-many that you already have? You shouldn't be preparing to switch - decide now, and migrate later if needed. Keeping both will just cause problems. Commented Aug 14, 2017 at 16:42

1 Answer 1

1

If your id is automatically generated by database, try add the script below in user class

[DatabaseGenerated(DatabaseGeneratedOption.None)]

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.