0

I get the following error when I try to update a record:

The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_dbo.User_dbo.House_IdHouse\

My code:

public class User
{
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int IdUser { get; set; }
        public int? IdHouse { get; set; }

        [ForeignKey("IdHouse")]
        public virtual House House { get; set; }
        //--- other fields
}

public class House
{
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int IdHouse { get; set; }
        public string Code { get; set; }

        public virtual ICollection<User> Users { get; set; }
        //--- other fields
}

I select users from database and update a flag then commit changes

var users = uow.Users.GetAll().Where(u => u.Name == null).ToList();

foreach (var user in users)
{
    user.Checked = true;
}

uow.Users.UpdateMany(users);
uow.Commit();

I'm not updating the house property at all, however I have the feeling it tries to insert a new house.

This query returns 3 records. The first 2 records have "House" linked to it and for the last record the "House" property is set to null.

I also tried to update each record apart.

UpdateMany function:

public void UpdateMany(IList<T> entities)
{
    foreach (var entity in entities)
    {
        Update(entity);
    }
}

public virtual void Update(T entity)
{
    DbEntityEntry dbEntityEntry = DbContext.Entry(entity);

    if (dbEntityEntry.State == EntityState.Detached)
    {
        DbSet.Attach(entity);
    }  

    dbEntityEntry.State = EntityState.Modified;
}

I also tried this one:

public virtual void Update(T entity)
{
    DbEntityEntry dbEntityEntry = DbContext.Entry(entity);

    dbEntityEntry.State = EntityState.Modified;
}

I'm using .Net 4.0 and Entity Framework 6

UPDATE

Running SQL profiler i found something strange, the application tries to update then insert. Although i'm not doing any insert

Update:

exec sp_executesql N'UPDATE [dbo].[User] SET [DSL] = @0, [TPL] = @1, [DFD] = @2, [IdProff] = @3, [IdHouse] = @4 WHERE ([IdUser] = @5) ',N'@0 float,@1 float,@2 float,@3 int,@4 int,@5 int', @0=0,041837288150243422,@1=462,76900000000001, @2=19,360999999999997,@3=3354,@4=17@5=3354

Insert:

exec sp_executesql N'INSERT [dbo].[ IdUser]([ DSL], [TPL], [DFD], [IdProff], [IdHouse] VALUES (@0, @1, @2, @3, @4) SELECT [IdUser] FROM [dbo].[ IdUser] WHERE @@ROWCOUNT > 0 AND [IdUser] = scope_identity()',N'@0 float,@1 float,@2 float,@3 int,@4 int, @0=0,91810000000000003,@1=460,11399999999998,@2=19,792000000000002,@3=3197,@4=0 @5=N'User'

Why the application trying to insert?

UPDATE: My context settings

DbContext.Configuration.ProxyCreationEnabled = true;
DbContext.Configuration.LazyLoadingEnabled = true;
DbContext.Configuration.ValidateOnSaveEnabled = false;
DbContext.Configuration.AutoDetectChangesEnabled = false;
Maro
  • 2,579
  • 9
  • 42
  • 79
  • Is HouseID nullable in your database definition? – GantTheWanderer Jan 23 '17 at 18:11
  • You may want to take a look [at this question's answers](http://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint). They explain why the error is happening fairly well. Although it is terms of pure SQL, you should be able to determine the issue in your code armed with this knowledge. – Timothy G. Jan 23 '17 at 18:14
  • Yes, it is nullable – Maro Jan 23 '17 at 18:15
  • You have 2 possibilities: When you populate the IdHouse, your ViewModel or other DTO you are using is setting a invalid value. Or, the DTO has the property value as non-nullable, then It's setting to default value of int, 0. – Fals Jan 23 '17 at 18:18
  • I suspect there a type Conversion somewhere between your interface and your codethat is converting "IdHouse" = null or possibly another foreign key) to a "0" and then tries to insert a 0 ? – DaniDev Jan 23 '17 at 19:11
  • You could try tesing updating one record at a time. First the ones that have a value for house ID and then the one that doesn't. if the ones that do have a value update correctly, and the one that doesn't does not update then it is probably the case, as I mentioned in comment above. – DaniDev Jan 23 '17 at 19:31
  • I updated my question with SQL query – Maro Jan 23 '17 at 19:32
  • @DaniDev The problem also happen for the one that has House ID, the application is trying to insert new record, i really don't know why – Maro Jan 23 '17 at 19:34
  • Tell me what is your "User" entity called in your Entities model and I will post an Update Command for you to try – DaniDev Jan 23 '17 at 19:54
  • You are switching conexts. And so this line: if (dbEntityEntry.State == EntityState.Detached) { DbSet.Attach(entity); } Tries to insert your records inset of updating – DaniDev Jan 23 '17 at 20:30
  • just checking did you manage to resolve this issue? – DaniDev Jan 24 '17 at 21:28
  • @DaniDev not yet . thanks – Maro Jan 24 '17 at 22:36
  • Now that I studied your code more carefully I think your problem is that you are 'leaving' or 'losing' the context to do your updates. hence your list is 'detatached' so it treats the items as new items and tries to insert them thus causing a foreign key violation. Let me know if this makes sense? if you like I can rewrite your update methods so that you are still in context. – DaniDev Jan 24 '17 at 23:04
  • It make sense, yes please write your update method – Maro Jan 24 '17 at 23:44
  • OK, It will take a little time, as I am in the middle of something else. – DaniDev Jan 24 '17 at 23:57

0 Answers0