Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity Framework 4.1 Code First approach to create many-to-many relation

I'm using the Silverlight 5 Beta SDK and the EntityFramework 4.1 in an Silverlight Application.

I'll try to create the two tables 'Author' and 'Book'. In SQL, there should be a third (join) table, which makes the many-to-many relation between Author and Book (one author could have written many books and a book could be written from many authors).

This is what I've got so far:

namespace CodeFirst.Models.Web
{    
    public class Author
    {
        public Author()
        {
            this.Books = new HashSet<Book>();
        }

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public int ID { get; set; }

        [Required]
        public string Name { get; set; }

        public ICollection<Book> Books { get; set; }
    }


    public class Book
    {
        public Book()
        {
            this.Authors = new HashSet<Author>();
        }


        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public int ID { get; set; }

        [Required]
        public string Name { get; set; }

        public ICollection<Author> Authors { get; set; }
    }


    // Should I do something like that:
    public class AuthorMapping : EntityTypeConfiguration<Author>
    {
        public AuthorMapping() : base()
        {   
            //this.HasMany (g => g.Books)
            //    .WithMany(m => m.Authors)
            //    .Map     (gm => gm.ToTable    ("Author_Book")
            //                      .MapLeftKey ("AuthorID")
            //                      .MapRightKey("BookID"));
        }
    }


    public class CodeFirstModelContext : DbContext
    {
        public CodeFirstModelContext() : base()
        {
            this.Database.Connection.ConnectionString = @".\MSSQLSERVER2008;Database=CodeFirst;Trusted_Connection=true;";
        }


        public DbSet<Author> Authors  { get; set; }
        public DbSet<Book>   Books { get; set; }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new AuthorMapping());

            // tell Code First to ignore PluralizingTableName convention
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }
    }


    [EnableClientAccess()]
    public class CodeFirstDomainService : DomainService
    {
        public CodeFirstDomainService()
        {
            this.m_modelContext = new CodeFirstModelContext();
        }


        public IQueryable<Author> GetAuthors()
        {
            return this.m_modelContext.Authors;//.Include("Books");
        }

        public void InsertAuthor(Author Author)
        {
            this.m_modelContext.Insert(Author);
        }

        public void UpdateAuthor(Author Author)
        {
            this.m_modelContext.Update(Author, this.ChangeSet.GetOriginal(Author));
        }

        public void DeleteAuthor(Author Author)
        {
            this.m_modelContext.Delete(Author);
        }


        public IQueryable<Book> GetBooks()
        {
            return this.m_modelContext.Books;//.Include("Authors");
        }

        public void InsertBook(Book Author)
        {
            this.m_modelContext.Insert(Author);
        }

        public void UpdateBook(Book Author)
        {
            this.m_modelContext.Update(Author, this.ChangeSet.GetOriginal(Author));
        }

        public void DeleteBook(Book Author)
        {
            this.m_modelContext.Delete(Author);
        }


        protected override void Dispose(bool disposing)
        {
            if (disposing)
                this.m_modelContext.Dispose();
            base.Dispose(disposing);
        }

        protected override bool PersistChangeSet()
        {
            this.m_modelContext.SaveChanges();
            return base.PersistChangeSet();
        }


        private CodeFirstModelContext m_modelContext;
    }
}

The most obvious problem is, that the navigation properties (Books in Author and Authors in Book) aren't created from the code designer in my client project.

What do I need to do?

EDIT: Okay, now I'm able to use only one of the NavigationProperties simultaneously. If I try to 'Include' both I'm getting the following error:

Association 'Author_Book' defined on entity type 'CodeFirst.Models.Web.Author' is invalid. It is a foreign key association but the property type is not a singleton.

This is my updated code:

public class Author
{
    public Author()
    {
        this.Books = new Collection<Book>();
    }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int ID { get; set; }

    [MaxLength(32)]
    [Required]
    public string Name { get; set; }

    [Association("Author_Book", "ID", "ID")]
    [Include]
    public Collection<Book> Books { get; set; }
}


public class Book
{
    public Book()
    {
        this.Authors = new Collection<Author>();
    }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int ID { get; set; }

    [MaxLength(32)]
    [Required]
    public string Name { get; set; }

    [Association("Author_Book", "ID", "ID")]
    [Include]
    public Collection<Author> Authors { get; set; }
}


public class AuthorMapping : EntityTypeConfiguration<Author>
{
    public AuthorMapping() : base()
    {
        this.HasMany (g => g.Books)
            .WithMany(m => m.Authors)
            .Map     (gm => gm.ToTable("Author_Book"));
    }
}

public class BookMapping : EntityTypeConfiguration<Book>
{
    public BookMapping() : base()
    {
        this.HasMany (m => m.Authors)
            .WithMany(g => g.Books)
            .Map     (gm => gm.ToTable("Author_Book"));
    }
}

It seems to me, that Entity Framework still isn't able to deal with many-to-many relations. At least, that's what the error message implies.

EDIT2: I've changed my code after I've read this post on social.msdn:

public class Author
{
    public Author()
    {
        this.Books = new Collection<Book>();
    }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int ID { get; set; }

    [MaxLength(32)]
    [Required]
    public string Name { get; set; }

    [Association("Author_Book", "Book_ID", "Author_ID")]
    [Include]
    [ForeignKey("Book_ID")]
    public Collection<Book> Books { get; set; }
    public int Book_ID { get; set; }
}

public class Book
{
    public Book()
    {
        this.Authors = new Collection<Author>();
    }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int ID { get; set; }

    [MaxLength(32)]
    [Required]
    public string Name { get; set; }

    [Association("Author_Book", "Author_ID", "Book_ID")]
    [Include]
    [ForeignKey("Author_ID")]
    public Collection<Author> Authors { get; set; }
    public int Author_ID { get; set; }
}

It doesn't fixed my problem. The same error is still present. I've tested to remove the AssociationAttribute without success. Am I doing somethinf wrong here?

like image 885
0xbadf00d Avatar asked May 09 '11 12:05

0xbadf00d


People also ask

How to configure one-to-many relationships in Entity Framework?

Configure One-To-Many Relationships In Entity Framework Using Code First Approach. The relationship between tables is defined by using foreign keys in relational databases. The foreign key is a column or combination of columns which enforces a relation between the data of two tables.

What are the conventions for one to many relationships in Entity Framework?

Conventions for One-to-Many Relationships. There are certain conventions in Entity Framework which if followed in entity classes (domain classes) will automatically result in a one-to-many relationship between two tables in the database. You don't need to configure anything else.

How to configure one-to-one relations between two entities?

The two ends of relation can be referred to as the principal role and dependent role. There are two ways to configure one-to-one relations between two entities in Entity Framework Using DataAnnotation Entity Framework Code First provides a set of data annotation attributes that can be applied to domain classes or the properties of domain classes.

How to create a many-to-many relationship in EF 6?

Many-to-Many Relationship by Following Conventions EF 6 includes default conventions for many-to-many relationships. You need to include a collection navigation property at both ends.


1 Answers

I think problem here lies in the WCF RIA service, not anything EF related. That is, that WCF doesn't like interfaces. Solution would be use Collection instead of ICollection. I'm sure EF won't mind it and it will fix your WCF problem.

Edit: This may solve your problem http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/d894c8af-5985-4995-88e2-c8733e4a51ea

like image 99
Euphoric Avatar answered Sep 19 '22 14:09

Euphoric