Entity Framework 6 tries to insert FK

Question

I have a small application in C#, WPF and EF6.

I’m using Code First, but I’m having a hard time creating a relationship. Look at my code, of the classes I want to relate: I’m editing for the final version, in case anyone has the same doubt.

public class Caixa
{
    [Key]
    public int CaixaId { get; set; }
    public DateTime DataAbertura { get; set; }
    public DateTime DataFechamento { get; set; }
    public Boolean Aberto { get; set; }
    public Decimal Troco { get; set; }
    public Decimal TotalRetiradas { get; set; }
    public Decimal TotalEntradas { get; set; }
    public Decimal Total { get; set; }
    public Decimal TotalEmCaixa { get; set; }
    public String Observacoes { get; set; }
    public Usuario Responsavel { get; set; }        

    public Caixa()
    {
        DataAbertura = DateTime.Now;
    }

}

public class Usuario
{
    [Key]    
    public int UsuarioId { get; set; }        
    [Required, Index(IsUnique=true)]
    public String Login { get; set; }
    [Required]
    public String Password { get; set; }
    public Boolean Administrador { get; set; }

}

 public class Contexts: DbContext
{
    //entidades
    public DbSet<Usuario> Usuarios { get; set; }
    public DbSet<Produto> Produtos { get; set; }
    public DbSet<Caixa> Caixas { get; set; }

    public Contexts()
    {
        Database.SetInitializer(new DropCreateDatabaseIfModelChanges<Contexts>());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {    

        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        base.OnModelCreating(modelBuilder);            
    }

}

The code you save is as follows::

database.Caixas.Add(NovoCaixa);
NovoCaixa.Responsavel = database.Usuarios.Single(u => u.UsuarioId == UsuarioAtivo.UsuarioId);
database.SaveChanges();

Whenever I try to save the entity Box, I see this mistake:

System.Data.SqlServerCe.SqlCeException: A duplicate value cannot be inserted into a unique index. [ Table name = Usuario, Constraint name = IX_Login ]

I suspect he is trying to insert the user, but it is not what I want, because it already exists.

Can anyone help me? I am doing this project to learn EF.

Answer 1

Possibly you created the Model Usuario, inserted two users with the same Login and tried to execute a Migration to get the error.

Revert all the Migrations using one of the two commands:

PM> Update-Database -TargetMigration:0

Or

PM> Update-Database -TargetMigration:$InitialDatabase 

After that, erase all Migrations and manages another Migration. Perform one more Update-Database.

---

EDIT

You’re using it wrong AddOrUpdate. This way, the settings always try to insert a Usuario, no matter if it exists or not.

The correct way is:

database.Caixas.AddOrUpdate(c => c.Login, caixa); 
database.SaveChanges();

---

EDIT 2

Basically, is all wrong in your solution.

You don’t need to implement a DAL (not DAO, because DAL is the name of the layer, Database Abstraction Layer, and DAO is the object itself, Database Abstraction Object). Entity Framework already fully implements a DAL, so you don’t need to reinvent the wheel.

You may want to isolate logic on a service layer, but WPF is a MVVM standard, where it’s basically the screen that performs the persistence of objects, so it’s best to give up isolating anything until it’s well understood how the Entity Framework works.

As I said, this is not how an object persists in the base:

database.Caixas.AddOrUpdate(caixa);
database.Entry(caixa).Property("ResponsavelId").EntityEntry.State = EntityState.Unchanged;
database.SaveChanges();

AddOrUpdate is not a command for routine operations. It is a command for you to make initial inserts for your system to work (a practice also known as "seeding the base"). It is most common in web projects, especially using ASP.NET MVC.

To insert a new object, the correct command is:

database.Caixas.Add(caixa);

To update:

database.Entry(caixa).State = EntityState.Modified;

Do not try to do a method to perform both operations. The Entity Framework does not work well like this. Still, if you really want to use only one code to perform both operations (which is not necessary), you can check if the object key is null:

if (caixa.CaixaId == null) 
{
    database.Caixas.Add(caixa);
} else 
{
    database.Entry(caixa).State = EntityState.Modified;
}

database.SaveChanges();

Note the nonsense of the code below:

database.Entry(caixa).Property("ResponsavelId").EntityEntry.State = EntityState.Unchanged;

You’re taking a navigation property and trying to tell the context that it hasn’t been modified, but you’re inserting a new one Caixa with a Responsavel hanging on the entity. The Entity Framework interprets this Responsavel as a new object. As much as you say this Responsavel is not a new object, the Entity Framework understands that it is because Caixa is a new object. Hence its error.

I don’t know how you’re doing to create this Caixa, but the two correct and most common ways are:

• Creating the object and defining a Responsavel, selecting the Responsavel if need be;
• Receiving a Caixa filled by a screen and sent to the code that contains the DbContext.

For your case, which is in WPF, the first way is possibly the most likely for this scenario. Ie, creating a Caixa it must be so:

var responsavel = database.Usuarios.Single(/* Coloque aqui a condição para selecionar um Responsavel */); 
var caixa = new Caixa 
{
    Responsavel = responsavel
    /* Não defina ResponsavelId. O Entity Framework deduz ResponsavelId porque você já preencheu o Responsavel */
    /* Preencha aqui as demais properties */
};

databases.Caixas.Add(caixa);
databases.SaveChanges();