Relationship N for N and One for N with Codefirst Data Annotations

Question

How can I Relationship N to N and One to N using Data Annotations?

For example:

A sales drive has a user, and a user can have multiple drives.

This same sales movement has several products, and each product can be in various drives.

How can I perform this mapping using Data Annotation?

Answer 1

Another proposal

_{I’m sorry, but, if you had put your entities at least we could simulate, then I made an example that has virtually the same logic and you can implement in yours that is giving some errors.}

1) N to M (many to many).

Explanation: where a Book may have several Authors and an Author may be in several Books.

[Table("Autor")]
public class Autor
{
    public Autor()
    {
        this.Livros = new HashSet<Livro>();
    }

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

    [Required()]
    [MaxLength(100)]
    public String Nome { get; set; }

    public virtual ICollection<Livro> Livros { get; set; }

}

[Table("Livro")]
public class Livro
{

    public Livro()
    {
        this.Autores = new HashSet<Autor>();
    }

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

    [Required()]
    [MaxLength(100)]
    public String Titulo { get; set; }

    public int TipoId { get; set; }
    [ForeignKey("TipoId")]
    public virtual Tipo Tipo { get;set;}

    public virtual ICollection<Autor> Autores { get; set; }
}

Notice that this guy is only put one ICollection Authors within Book and ICollection Books within Author, no need to create an additional Entity (in this case it will be the relation). In the collections themselves the operations of Add (Add) and Remove (Remove), in the intermediate table Authorbook, where it holds the keys of Author and Book.

Look how the proposal would look with these models generated in the bank.

2) 1 to N (one to many)

Explanation: where a Book has a Type and a Type may have several books

[Table("Tipo")]
public class Tipo
{
    [Key]
    [DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)]
    public int TipoId { get; set; }

    [Required()]
    [MaxLength(100)]
    public string Descricao { get; set; }

    [ForeignKey("TipoId")]
    public ICollection<Livro> Livros { get; set; }
}

Inside Book has a Tipoid field ForeignKey for the entity Type and within Type a Collection of Books, this forces the relationship between these entities.

Proposed Final Model:

Context:

In this item that is the class you inherit from DbContext, 3 entities were placed (Autor, Livro and Tipo), and in the OnModelCreating, I forced a configuration in the relationship N for M (non-compulsory), but, for me essential for me to talk to the model as is the relationship and the name of the Table, if you leave in charge of the Framework it also generates but, with its own nomenclature.

public class Db: DbContext
{
    public Db()
        :base("Data Source=.\\sqlexpress;Initial Catalog=CBOLayout;Persist Security Info=True;User ID=sa;Password=senha") { }

    public DbSet<Livro> Livro { get; set; }
    public DbSet<Autor> Autor { get; set; }
    public DbSet<Tipo> Tipo { get; set; }


    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Autor>()
            .HasMany<Livro>(x=>x.Livros)
            .WithMany(x => x.Autores)
            .Map(x => 
            {
                x.ToTable("AutorLivro");
                x.MapLeftKey("AutorId");
                x.MapRightKey("LivroId");
            });
        base.OnModelCreating(modelBuilder);
    }
}

Settings:

• [Table("nome_da_tabela")]: indicates the name of the table referencing that entity.

• [Key]: indicates the Primary Key of your table

• [Required]: cannot contain null values, must be filled in.

• [MaxLength]: field size defined in the database, example: nvarchar(100).

• [ForeignKey]: indicates foreign key.

• [DatabaseGenerated]: with this setting you can set the auto increment of this field.

Running on the Migrations

using System;
using System.Data.Entity.Migrations;

public partial class newBanco : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Autor",
            c => new
                {
                    AutorId = c.Int(nullable: false, identity: true),
                    Nome = c.String(nullable: false, maxLength: 100),
                })
            .PrimaryKey(t => t.AutorId);

        CreateTable(
            "dbo.Livro",
            c => new
                {
                    LivroId = c.Int(nullable: false, identity: true),
                    Titulo = c.String(nullable: false, maxLength: 100),
                    TipoId = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.LivroId)
            .ForeignKey("dbo.Tipo", t => t.TipoId, cascadeDelete: true)
            .Index(t => t.TipoId);

        CreateTable(
            "dbo.Tipo",
            c => new
                {
                    TipoId = c.Int(nullable: false, identity: true),
                    Descricao = c.String(nullable: false, maxLength: 100),
                })
            .PrimaryKey(t => t.TipoId);

        CreateTable(
            "dbo.AutorLivro",
            c => new
                {
                    AutorId = c.Int(nullable: false),
                    LivroId = c.Int(nullable: false),
                })
            .PrimaryKey(t => new { t.AutorId, t.LivroId })
            .ForeignKey("dbo.Autor", t => t.AutorId, cascadeDelete: true)
            .ForeignKey("dbo.Livro", t => t.LivroId, cascadeDelete: true)
            .Index(t => t.AutorId)
            .Index(t => t.LivroId);

    }

    public override void Down()
    {
        DropForeignKey("dbo.AutorLivro", "LivroId", "dbo.Livro");
        DropForeignKey("dbo.AutorLivro", "AutorId", "dbo.Autor");
        DropForeignKey("dbo.Livro", "TipoId", "dbo.Tipo");
        DropIndex("dbo.AutorLivro", new[] { "LivroId" });
        DropIndex("dbo.AutorLivro", new[] { "AutorId" });
        DropIndex("dbo.Livro", new[] { "TipoId" });
        DropTable("dbo.AutorLivro");
        DropTable("dbo.Tipo");
        DropTable("dbo.Livro");
        DropTable("dbo.Autor");
    }
}

References:

• Dataannotation in Code-First

• Namespace System.ComponentModel.Dataannotations

• Foreignkeyattribute Class

• Part 6: Using Data Annotations for Model Validation

Answer 2

Thus:

public class Movimentacao
{
    [Key]
    public int MovimentacaoId { get; set; }
    public int ProdutoId { get; set; }
    public int UsuarioId { get; set; }

    public virtual Produto Produto { get; set; }
    public virtual Usuario Usuario { get; set; }
}

public class Usuario
{
    [Key]
    public int UsuarioId { get; set; }

    ...

    public virtual ICollection<Movimentacao> Movimentacoes { get; set; }
}

public class Produto
{
    [Key]
    public int ProdutoId { get; set; }

    ...

    public virtual ICollection<Movimentacao> Movimentacoes { get; set; }
}