Entity Foreign Key Framework

Question

My domain:

public class SBE_ST_CorpoDocente
{
    public int Id { get; set; }
    public string Nome { get; set; }
    public virtual ICollection<SBE_ST_Curso> Cursos { get; set; }
}

public class SBE_ST_Curso
{
    public int Id { get; set; }
    public string Titulo { get; set; }
    public virtual ICollection<SBE_ST_CorpoDocente> Coordenacao { get; set; }
}

Layer of interaction with the bank:

public void Salvar(SBE_ST_Curso entidade)
    {
        var idsCoordenacao = entidade.Coordenacao.Select(c => c.Id).ToList();
        var coordenacao = contexto.CorpoDocente.Where(cd => idsCoordenacao.Contains(cd.Id)).ToList();
        if (entidade.Id > 0)
        {
            var cursoAlterar = contexto.Curso.First(x => x.Id == entidade.Id);
            cursoAlterar.Titulo = entidade.Titulo;
            cursoAlterar.Coordenacao = coordenacao;
            contexto.Entry(cursoAlterar).State = EntityState.Modified;
            contexto.SaveChanges();
        }
        else
        {
            entidade.Coordenacao = coordenacao;
            contexto.Curso.Add(entidade);
            contexto.SaveChanges();
        }

When I enter a new record it works perfectly. When I update a record it gives error. If I clean the table that stores the relationships, then I can edit normal the first time, then gives the same error.

Error:

Additional information: An error occurred while saving entities that do not Expose Foreign key properties for their relationships. The Entityentries Property will Return null because a single Entity cannot be identified as the source of the Exception. Handling of exceptions while saving can be made easier by exposing Foreign key properties in your Entity types. See the Innerexception for Details.

Analyzing the Intellitrace:

Exception:Caught: "Violation of PRIMARY KEY Constraint 'Pk_dbo.Sbe_st_cursosbe_st_corpodocente'. Cannot Insert Duplicate key in Object 'dbo.Sbe_st_cursosbe_st_corpodocente'. The Duplicate key value is (8, 1). The statement has been terminated." (System.Data.Sqlclient.Sqlexception) A System.Data.Sqlclient.Sqlexception was Caught: "Violation of PRIMARY KEY Constraint 'Pk_dbo.Sbe_st_cursosbe_st_corpodocente'. Cannot Insert Dcate uplikey in Object 'dbo.Sbe_st_cursosbe_st_corpodocente'. The Duplicate key value is (8, 1). The statement has been terminated." Time: 11/08/2014 15:28:19 Thread:Worker Thread[3508]

I made a GAMBIARRA to solve this problem. (GAMBIARRA REALLY)

public void LimparRelacionamentos(int id)
{
    SqlConnection MinhaConexao = new SqlConnection(ConfigurationManager.ConnectionStrings["BancoDados"].ConnectionString);
    MinhaConexao.Open();
    string query = "DELETE FROM SBE_ST_CursoSBE_ST_CorpoDocente WHERE SBE_ST_Curso_Id = " + id;
    SqlCommand comando = new SqlCommand(query, MinhaConexao);
    comando.ExecuteNonQuery();
    MinhaConexao.Close();
}



 public void Salvar(SBE_ST_Curso entidade)
        {
            var idsCoordenacao = entidade.Coordenacao.Select(c => c.Id).ToList();
            var coordenacao = contexto.CorpoDocente.Where(cd => idsCoordenacao.Contains(cd.Id)).ToList();
            if (entidade.Id > 0)
            {
                var cursoAlterar = contexto.Curso.First(x => x.Id == entidade.Id);
                cursoAlterar.Titulo = entidade.Titulo;
                LimparRelacionamentos(entidade.Id);
                cursoAlterar.Coordenacao = coordenacao;
                contexto.SaveChanges();
            }
            else
            {
                entidade.Coordenacao = coordenacao;
                contexto.Curso.Add(entidade);
                contexto.SaveChanges();
            }
}

Answer 1

You are not making the context correctly observe your changes. The context always thinks that the entity coming from the screen is new because it has not loaded the existing record before. It would be right to load a new record if it does not have Id (Id > 0). Otherwise, you need to indicate to the context that the entity has been changed by you, and that the current information of the object should be treated as true.

Another thing is that:

cursoAlterar.Coordenacao = entidade.Coordenacao.Select(
    coordenacao => contexto.CorpoDocente.FirstOrDefault(x => x.Id == coordenacao.Id)).ToList();

This statement makes the context always fill in Coordenacao again. While there seems to be no change, the Entity Framework has a logic that looks at variables. How there was change, and you filled in Coordenacao from the screen, it tries to insert the records as if they were new. It would be right to fill the data with the variable coming from the context, and not the screen.

The code can also be changed to something simpler and removed directly from the context, for example:

cursoAlterar.Coordenacao = contexto.CorpoDocente.Where(
    x => entidade.Coordenacao.Select(e => e.Id).Contains(x.Id))).ToList();

The end result looks something like this.

public void Salvar(SBE_ST_Curso entidade)
{
    if (entidade.Id > 0)
    {
        // Esta linha tem que pelo menos levantar exceção, evitando inserir 
        // alguma bobagem no banco, por isso troquei para SingleOrDefault.
        var cursoAlterar = contexto.Curso.SingleOrDefault(x => x.Id == entidade.Id);

        cursoAlterar.Titulo = entidade.Titulo;
        cursoAlterar.Coordenacao = contexto.CorpoDocente.Where(
            x => entidade.Coordenacao.Select(e => e.Id).ToList().Contains(x.Id)).ToList();
        contexto.Entry(cursoAlterar).State = EntityState.Modified;

        contexto.SaveChanges();
    }
    else
    {
        cursoAlterar.Coordenacao = contexto.CorpoDocente.Where(
            x => entidade.Coordenacao.Select(e => e.Id).ToList().Contains(x.Id)).ToList();
        contexto.Curso.Add(entidade);

        contexto.SaveChanges();
    }
}

---

EDIT

I made several corrections in your code. I put everything in a Github repository. For what matters to the question, it lacked a robust logic that makes exclusion and inclusion of Faculty Bodies, as explained below:

            // Entradas Excluídas
            var coordenacoesOriginais = contexto.Curso.AsNoTracking().SingleOrDefault(c => c.Id == cursoAlterar.Id).Coordenacao;

            foreach (var coordenacaoPossivelmenteExcluida in coordenacoesOriginais)
            {
                if (cursoAlterar.Coordenacao.All(c => c.Id != coordenacaoPossivelmenteExcluida.Id))
                {
                    contexto.CorpoDocente.Remove(coordenacaoPossivelmenteExcluida);
                }
            }

            // Novas Entradas
            foreach (var novaCoordenacao in coordenacao)
            {
                if (cursoAlterar.Coordenacao.All(c => c.Id != novaCoordenacao.Id))
                {
                    cursoAlterar.Coordenacao.Add(novaCoordenacao);
                }
            }

            contexto.SaveChanges();

However, note that by deleting a Faculty in this way, you will also be deleting the original record, since the bank’s modeling is wrong. It should be created an extra associative entity that links Courses to Faculty, something like CursoAssocCorpoDocente. There goes your choice.

Answer 2

You can’t have a Manytomany relationship like this. Use EntityTypeConfiguration of lib System.Data.Entity.ModelConfiguration. And create the Map:

public class SBE_ST_CorpoDocenteMap : EntityTypeConfiguration<SBE_ST_CorpoDocente>
{
    public SBE_ST_CorpoDocenteMap()
    {
        HasMany(t => t.Cursos)
            .WithMany(t => t.Coordenacao)
            .Map(m =>
            {
                m.ToTable("TB_CURSO_COORDENADO", "schema");
                m.MapLeftKey("id_curso");
                m.MapRightKey("id_coordenado");
            });
    }
}

And plural your properties that are lists. use ICollection<Curso> Cursos

Good luck

Answer 3

Because they are 2 relationships of 1 for many, it is best to associate to the list of elements of the context, using the ids of the past object.

public void Salvar(SBE_ST_Curso curso)
{
    var idsCoordenacao = curso.Coordenacao.Select(c => c.Id).ToList();
    var coordenacao = contexto.CorpoDocente.Where(cd => idsCoordenacao.Contains(cd.Id)).ToList();

    if (curso.Id > 0)
    {
        var cursoAlterar = contexto.Curso.FirstOrDefault(c => c.Id == curso.Id);
        cursoAlterar.Titulo = curso.Titulo;
        cursoAlterar.Coordenacao = coordenacao;
    }
    else
    {
         curso.Coordenacao = coordenacao;
         contexto.Curso.Add(curso);
    }

    contexto.SaveChanges();
}

Just to reinforce, in case you do not know, but it is not recommended to work with objects that come directly from Request, as parameters of actions of the controller due to security issues. For this, always use a specific model for data transfer. These models are called Viewmodel by some and are only classes without any vículo, that serve only to transfer the data.

In your case, you could have a class for this data in the form of:

public class CursoViewModel
{
    public int Id { get; set; }
    public string Nome { get; set; }
    public List<int> Coordenacao { get; set; }

    public static CursoViewModel MapearDoCurso(SBE_ST_Curso curso)
    {
        return new CursoViewModel
        {
            Id = curso.Id,
            Nome = curso.Nome,
            Coordenacao = curso.Coordenacao.Select(c => c.Id).ToList()
        };
    }

    public void MapearParaCurso(SBE_ST_Curso curso)
    {
        curso.Id = this.Id;
        curso.Nome = this.Nome;
        curso.Coordenacao = context.CorpoDocente.Where(c => this.Coordenacao.Contains(c.Id));
    }
}

With this auxiliary mapping structure and methods, you can work safely, sending and receiving data with this class and retrieve your model in the controller action:

public ActionResult Edit(int id)
{
    var curso = contexto.Cursos.FirstOrDefault(c => c.Id == id);

    if (curso != null)
    {
        var cursoParaEditar = CursoViewModel.MapearDoCurso(curso);

        return View(cursoParaEditar);
    }
    else
    {
        ViewBag["erro"] = "Curso inválido!";
        return View();
    }
}

On the return, just do the opposite, using the method in the instance MapearParaCurso(), passing a reference to the course with the method came ID:

public ActionResult Edit(CursoViewModel cursoAlterado)
{
    if (ModelState.IsValid)
    {
        var curso = contexto.Cursos.FirstOrDefault(c => c.Id == cursoAlterado.Id);

        if (curso != null)
        {
            cursoAlterado.MapearParaCurso(curso);
            // caso não salve, utilize: contexto.Entry(curso).EntityState = EntityState.Modifyed;
            contexto.SaveChanges();
            return RedirectToAction("Index");
        }
        else
        {
            ViewBag["erro"] = "Curso inválido!";
            return View();
        }
    }
    else
    {
        ViewBag["erro"] = "Erros de validação!";
        return View();
    }
}

Answer 4

I managed to come up with a solution.

I’m not sure why it works, or if it makes sense to make it work.

Solution

If before I upgrade I give one Clear() in the list, it no longer generates the error.

That is to say:

var idsCoordenacao = entidade.Coordenacao.Select(c => c.Id).ToList();
var coordenacao = contexto.CorpoDocente.Where(cd => idsCoordenacao.Contains(cd.Id)).ToList();
if (entidade.Id > 0)
{
    var cursoAlterar = contexto.Curso.First(x => x.Id == entidade.Id);
    cursoAlterar.Titulo = entidade.Titulo;
    cursoAlterar.Coordenacao.Clear(); //ADICIONAR ESSA LINHA.
    cursoAlterar.Coordenacao = coordenacao;
    contexto.Entry(cursoAlterar).State = EntityState.Modified;
    contexto.SaveChanges();
}