CODEWITHSUNDEEP
c#sqlentity-framework
AlexanderBrevig
AlexanderBrevig

Reputation: 1987

EntityFramework 4.1, context.Entities.Add sometimes set FK to NULL

I have this weird problem that has burned way more hours than it should.

My main question is this: What may cause EF 4.1 Code First to set a foreign key to NULL when an entity is added?

The problem is this: I have a list of users on file, and these users must be inserted to my database if they're not already there.

I have something like this:

foreach (var u in usersFromFile) {
    var userProfile = context.Users
        .FirstOrDefault(user=>
            user.EmployeeId == u.EmployeeId && user.CompanyId == 1);
    if (userProfile == null) {
        User newUser = new User();
        newUser.EmployeeId = u.EmployeeId;
        newUser.CompanyId = 1;
        context.Users.Add(newUser); //This will sometimes set CompanyId = NULL
    }
}
context.SaveChanges();

Some users won't be added to the Users table correctly. They get CompanyId == NULL, and as such they do not belong to the company.

I also tried injecting SQL directly like so:

var query = @"INSERT INTO [dbo].[Users] 
              ([CompanyId],[EmployeeId]) VALUES (3,@emplid)";

context.Database.ExecuteSqlCommand(query, new SqlParameter[] {
    new SqlParameter("emplid", u.EmployeeId)});

I know for a fact that the state of the entity about to be added is correct, also in the cases where CompanyId is set to NULL. Could there be something with my underlying database?

Thank you so much for your time and help!

Upvotes: 1

Views: 246

Answers (2)

AlexanderBrevig
AlexanderBrevig

Reputation: 1987

I ended up transforming the list of users to a series of SQL statemens, and running them through context.Database.ExecuteSqlCommand(sql).

It's dirty but it works.

If anyone has any good ideas as to why the FK CompanyId on the user entity is sometimes set to NULL I'd we very happy if you share your ideas.

Upvotes: 0

Jente Rosseel
Jente Rosseel

Reputation: 1205

Try the following:

 foreach (var u in usersFromFile) {
    if (context.Users.Any(
          user=>
            user.EmployeeId == u.EmployeeId && user.CompanyId == 1)
       ) 
    {
        User newUser = new User();
        newUser.EmployeeId = u.EmployeeId;
        newUser.CompanyId = 1;
        context.Users.Add(newUser); //This will sometimes set CompanyId = NULL
    }
}
context.SaveChanges();

The Any() function checks wether a user based on the given query exists.

Also, don't forget to add the context.savechanges to make sure every added record gets put in the database.

Lastly, you checked if user.CompanyId = 1, should be == 1

Upvotes: 5

Related Questions