Entity Framework update and delete associative entity

Question

I have a student table that has many to many relationship with the course table. Thus resulting in the table CursoAluno.

I would like to know the correct way to the update and the delete table CursoAluno.

For example when there is an update related to the relationship AlunoXCurso, I need to delete all data related to that Student and add everything again or have a more correct way?

Answer 1

That question, beforehand, is conceptual and has no relation to the Entity Framework itself.

About your doubt, you must think how Aluno and Curso: courses exist without students and students exist without courses; a student belongs to a course but a course belongs to your company.

In short, the best practice, in the mine point of view, is to create a table of interploration, basically as the CursoAluno that you did, only better cut.

Let’s think:

• A student can been in more than one course?
  • If yes, then the nomenclature is wrong: CursosAluno would be the best case, after all, a student may belong to more than one course simultaneously.
    • Tables that interplorate should have their trivial and simple structure - this makes them flexible. Below, an example that I like and practice a lot, but that go probably require you foreign dependency keys to both the columns, because if one or the other - student or course - ceases to exist, the relationship - the principle - must be undone. Remembering that, with this structure, the other tables are free to be modified because they do not directly affect their cultures, only how they interact with each other.

Cursosaluno.sql

+----+----------+----------+
| id | aluno_id | curso_id |
+----+----------+----------+
| 1  | 1        | 1        |
+----+----------+----------+
| 2  | 1        | 2        |
+----+----------+----------+

• • If not, then you don’t need of that interploration table which is the current one CursoAluno. The only thing needed would be for you to put it on the table Alunos a field called curso_id - then you will be able to work with the desired flexibility.

Giving depth to your case, let’s consider the following:

Sql courses.

+----+----------------+
| id | name           |
+----+----------------+
| 1  | C#             |
+----+----------------+
| 2  | PHP            |
+----+----------------+
| 3  | Banco de dados |
+----+----------------+

Sql students.

+----+----------------+
| id | name           |
+----+----------------+
| 1  | João           |
+----+----------------+

So let’s consider that John is martyred in C# and Database. Your table CursosAluno will look like this:

+----+----------+----------+
| id | aluno_id | curso_id |
+----+----------+----------+
| 1  | 1        | 1        |
+----+----------+----------+
| 2  | 1        | 3        |
+----+----------+----------+

Now, you want to relocate him from C# for PHP, then we we remove the line WHERE aluno_id = 1 AND curso_id = 1 and then add a new record to aluno_id = 1 and curso_id = 2.

This way, we will have the following result:

+----+----------+----------+
| id | aluno_id | curso_id |
+----+----------+----------+
| 1  | 1        | 2        |
+----+----------+----------+
| 2  | 1        | 3        |
+----+----------+----------+

If you think about it, we have a unanimous and independent structure, easy to maintain and with a simple concept.

Answer 2

Diagram of tables and their relationships (N - M)

Entities and Dbcontext

//Representa Tabela Aluno
[Table("Aluno")]
public class Aluno
{
    public Aluno()
    {
        this.Cursos = new HashSet<Curso>();
    }
    [Key()]
    [DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)]
    public int AlunoId { get; set; }
    public String Nome { get; set; }
    public virtual ICollection<Curso> Cursos { get; set; }
}
//Representa Tabela Curso
[Table("Curso")]
public  class Curso {
    public Curso ()
    {
        this.Alunos = new HashSet<Aluno>();
    }

    [Key()]
    [DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)]
    public int CursoId { get; set; }
    public String Descricao { get; set; }

    public virtual ICollection<Aluno> Alunos { get; set; }
}
//Representa o DbContext
public class Context: DbContext
{
    public Context()
        : base("Data Source=.\\SqlExpress;Initial Catalog=BaseDados;Persist Security Info=True;User ID=sa;Password=senha") { }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {       
        modelBuilder.Entity<Curso>()
                .HasMany(a => a.Alunos)
                .WithMany(c => c.Cursos)
                .Map(x =>
                {
                    x.MapLeftKey("CursoId");
                    x.MapRightKey("AlunoId");
                    x.ToTable("CursoAluno");
                });

        base.OnModelCreating(modelBuilder);
    }
    public DbSet<Curso> Curso { get; set; }
    public DbSet<Aluno> Aluno { get; set; }
}

Operations

Insert

In this procedure was inserted Course in the Course table, Student in the Student Table. After that was inserted the Course within the Student Courses (in the relationship table N - M).

Context db = new Context();

//INSERINDO CURSO NA TABELA CURSO
Curso curso = new Curso();
curso.Descricao = "Informática Basica";

//INSERINDO ALUNO NA TABELA ALUNO
Aluno aluno = new Aluno();
aluno.Nome = "Aluno Nome 1";

//ADICIONANDO NO CONTEXTO
db.Curso.Add(curso);
db.Aluno.Add(aluno);

//INSERINDO CURSO PARA O ALUNO
aluno.Cursos.Add(curso);

//SALVANDO REALMENTE OS DADOS
db.SaveChanges();

Rule out

This exclusion has reference to the Course table where the Course within Students will be excluded. This course in the Course table will normally exist, that is, it was excluded from the relationship table and not from the main Course table

Context db = new Context();
//EXCLUIR DO ALUNO 1 O CURSO 1
Aluno aluno = db.Aluno.Find(1);
Curso curso = aluno.Cursos.Where(x => x.CursoId == 1).FirstOrDefault();
if (curso != null)
{
    aluno.Cursos.Remove(curso);
}
//SALVANDO REALMENTE OS DADOS
db.SaveChanges();

When working with N-M relationship we do the two Add and Remove operations, the updates are done directly in Student and Course as they have all the necessary fields for such.

Update

Context db = new Context();
Aluno aluno = db.Aluno.Find(1);
aluno.Nome = "Alterando o nome";

Curso curso = db.Curso.Find(1);
curso.Descricao = "Alterando Curso";

db.SaveChanges();

Note: In this section you can update the main data of Student and Courses and also work with Delete and Add in the relationship table.

Example

Context db = new Context();
Aluno aluno = db.Aluno.Find(1);
aluno.Nome = "Alterando o nome";

//Novo curso que vai ser adicionado na tabela de Curso e relacionamento para o Aluno existente
Curso curso = new Curso();
curso.Descricao = "C#";

aluno.Cursos.Add(curso);

db.SaveChanges();