Error updating records with recursive associations using EF and Code First

Question

I want to update a record type graph, which modify the parent object and child objects also having internally. The problem usually occurs when EF UPDATE registry for children puts them to NULL in where children helps identify the records you are updating.

My domain classes are:

This class helps me to change the status, Add - Customized - delete, which is the graph from the parent as all children. The function that makes it work.

public interface IObjectWthState
{
    State State { get; set; }
}
public enum State 
{
    Added,
    Unchanged,
    Modified,
    Deleted
}

This is a User Class:

 public abstract class User : IObjectWthState 
 {
    public int Id { get; set; }
    public String Name { get; set; }
    public String LastName { get; set; }        
    [Timestamp]
    public byte[] RowVersion { get; set; }
    [NotMapped]
    public State State { get; set; }
}

And here are the two classes that inherit from user:

public class AdminUser:User
{
    public ICollection<BasicUser> UsersList { get; set; }
    public String Email { get; set; }
}

public class BasicUser: User
{
    public String Position { get; set; }
    public String Department { get; set; }
}

As seen AdminUser BasicUser has a list.

The model was generated as wanted, needed to detect a foreign key and add. Here a picture of the DB:

This is the function that adds or updates the infromación:

public virtual void AddUpdateGraph(T entity)
{

    if (((IObjectWthState)entity).State == State.Added)
    {
        DbEntityEntry dbEntityEntry = dbContext.Entry(entity);
        dbEntityEntry.State = EntityState.Added;
    }
    else
    {
        dbSet.Add(entity);
        dbContext.ApplyStateChanges();
    }
} 

The function that handles go and adjust the status of internal nodes:

public static void ApplyStateChanges(this DbContext context)
{
    foreach (var entry in context.ChangeTracker.Entries<IObjectWthState>())
    {
        IObjectWthState stateInfo = entry.Entity;
        entry.State = StateHelpers.ConvertState(stateInfo.State);
    }
}  

Function that returns the state to EF:

public static EntityState ConvertState(State state)
{
    switch (state)
    {
        case State.Added:
            return EntityState.Added;
        case State.Modified:
            return EntityState.Modified;
        case State.Deleted:
            return EntityState.Deleted;
        default:
            return EntityState.Unchanged;
    }
}

When you want to add a new AdminUser with his list BasicUser everything works fine with no problem, the problem comes when you want to modify the EF BasicUser AdminUser and generates updates for the BasicUser but added a condition that the foreign key is null.

Here you can see the two updates that are generated

AdminUser:

 exec sp_executesql N'update [dbo].[User]
set [Name] = @0, [LastName] = @1, [Email] = @2
where (([Id] = @3) and ([RowVersion] = @4))
select [RowVersion]
from [dbo].[User]
where @@ROWCOUNT > 0 and [Id] = @3',N'@0 nvarchar(max) ,@1 nvarchar(max) ,@2 nvarchar(max) ,@3 int,@4 binary(8)',@0=N'Beto',@1=N'Guerere',@2=N'beto@gmail.com',@3=3,@4=0x0000000000000801

BasicUser:

exec sp_executesql N'update [dbo].[User]
set [Name] = @0, [LastName] = @1, [Position] = @2, [Department] = @3, [AdminUser_Id] = @4
where ((([Id] = @5) and ([RowVersion] = @6)) and [AdminUser_Id] is null)
select [RowVersion]
from [dbo].[User]
where @@ROWCOUNT > 0 and [Id] = @5',N'@0 nvarchar(max) ,@1 nvarchar(max) ,@2 nvarchar(max) ,@3 nvarchar(max) ,@4 int,@5 int,@6 binary(8)',@0=N'Viomar',@1=N'Guerere',@2=N'Supervisora',@3=N'Ventas',@4=3,@5=4,@6=0x0000000000000802

As you see in the EF generated SQL command to do the update of BasicUser is added the condition of the record has the Null value to a AdminUser_Id. I do not understand the reason for this. The field can not be null because that user is assigned to a supervisor.

I hope I explained.

Thank you very much for any help you can give me.

Answer 1

Are you retrieving a basicuser that already has an adminuser assigned? It looks to me like the adminuserid is not being retained from the time you retrieve it to the time you are doing the update. I see this frequently, for example in MVC applications where you don't want to display that foreign key and therefore you don't include it in the view. But when you update and send the object to the controller, it creates a new user instance that has the null FK. And as you are using that FK as a concurrency checking field, it is doubly important that you retain the value as you move from controller to view to controller. IF this is the case, luckily I already have a blog post on this problem so I don't ahve to reexplain in full here: http://thedatafarm.com/blog/data-access/round-tripping-a-timestamp-field-with-ef4-1-code-first-and-mvc-3/

Let me know if that is, indeed, the case. If so, hopefully it solves your problem. If not, I'll probably have more questions! :)

(p.s. the rest of your code looks pretty familiar! :) does that come from my & Rowan's book on DbContext? :) )

Answer 2

Answering my question to me: EF believes the FK in the database anyhow you have to work with her inside the model as variable of this form if you work disconnected you be to be sure that this your information being stored.

The first thing that has to be done is to add in the class BasicUser a variable of type int the one that we will call AdminUser_Id:

public class BasicUser: User
    {       
        public String Position { get; set; }
        public String Department { get; set; }
        public int AdminUser_Id { get; set; }
    }

Then we have to tell EF that the variable added is a FK that references a AdminUser AdminUser and can have many BasicUser.

To do it, we go where we are declaring the contex, DataLayer (In My case), and in the cuncion where this being created the model we add the following annotation:

modelBuilder.Entity<AdminUser>()HasMany(A => A.UsersList ).WithRequired().HasForeignKey(B => B.AdminUser_Id );

We update the model and with these adjustments the mistake is eliminated completely at the moment of realizing the update of the record.

For more information about FK and EF recommend reading this article:

1. Making Do with Absent Foreign Keys
2. Code First Relationships Fluent API

Thank you very much to all especially to Julie Lerman for your help, without it I would have cost a little more to get to that solution.

I hope I have explained.