Entity Framework 4.1 Code-First approach to realize many-to-many relation over Domain-Services

I had some problems while creating a database model using the newest Entity Framework and Code-First (see Entity Framework 4.1 Code First approach to create many-to-many relation for details).

Meanwhile I've figured out that the problem isn't the Entity Framework itself any more, but using it along with WCF RIA DomainServices.

For the sake of completeness - that's my relevant Code-First code:

//
// Models
//

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; }

    [Include]
    [Association("Author_Book", "ID", "ID")]
    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; }

    // I really would like to create this navigation property, but there seems to be no way
    // to tell my DomainService to include it.
    // public Collection<Author> Authors { get; set; }
}


//
// Mappings
//

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


//
// DbContext
//

public class BookAuthorModelContext : DbContext
{
    public BookAuthorModelContext()
        : base(@"data source=localhost\MSSQLSERVER2008R2;database=BookAuthor;integrated security=True;")
    {
    }


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


    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new AuthorMapping());
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}


//
// DomainService
//

[EnableClientAccess()]
public class BookAuthorDomainService : DomainService
{
    static BookAuthorDomainService()
    {
        Database.SetInitializer<BookAuthorModelContext>(new BookAuthorModelInitializer());
    }

    public BookAuthorDomainService()
    {
        this.m_modelContext = new BookAuthorModelContext();
    }


    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 BookAuthorModelContext m_modelContext;
}

The SQL-Tables are created as expected. In my client application I'm using a RadGridView with a DomainDataSource:

<UserControl>
    <UserControl.Resources>
        <webServices:BookAuthorDomainContext x:Name="BookAuthorDomainContext"/>
    </UserControl.Resources>

    <riaControls:DomainDataSource x:Name="AuthorDomainDataSource"
                                  DomainContext="{StaticResource BookAuthorDomainContext}" QueryName="GetAuthorsQuery"                                
                                  d:DesignData="{d:DesignInstance webModels:Author, CreateList=true}">

    <telerik:RadGridView x:Name="AuthorGridView" DataContext="{Binding ElementName=AuthorDomainDataSource}" 
                         ItemsSource="{Binding Data}" IsBusy="{Binding IsBusy}"/>
</UserControl>

Now things get interesting. If I add two records to the empty database - one to the Author table and an other to the Book table - then both records 'ID' field is '1'. The interesting thing is, that the GetAuthorsQuery() with included Books adds the Book to the Authors 'Books' Property. There is no entry in the created Author_Book (join-)table. So, I've started my SQL-Profiler to see what exactly is going here. That's what I found out:

SELECT 
[Project1].[ID] AS [ID], 
[Project1].[Name] AS [Name], 
[Project1].[C1] AS [C1], 
[Project1].[ID1] AS [ID1], 
[Project1].[Name1] AS [Name1]
FROM ( SELECT 
    [Limit1].[ID] AS [ID], 
    [Limit1].[Name] AS [Name], 
    [Join1].[ID] AS [ID1], 
    [Join1].[Name] AS [Name1], 
    CASE WHEN ([Join1].[Author_ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM   (SELECT TOP (20) [c].[ID] AS [ID], [c].[Name] AS [Name]
        FROM [dbo].[Author] AS [c] ) AS [Limit1]
    LEFT OUTER JOIN  (SELECT [Extent2].[Author_ID] AS [Author_ID], [Extent3].[ID] AS [ID], [Extent3].[Name] AS [Name]
        FROM  [dbo].[Author_Book] AS [Extent2]
        INNER JOIN [dbo].[Book] AS [Extent3] ON [Extent3].[ID] = [Extent2].[Book_ID] ) AS [Join1] ON [Limit1].[ID] = [Join1].[Author_ID]
)  AS [Project1]
ORDER BY [Project1].[ID] ASC, [Project1].[C1] ASC

Why is he doing this? I really would like to use my many-to-many relation, but I would also be happy to use a uni-directional relation (at least something would be working).

Thanks in advance for any help.

---

I'm not using attributes, but maps. Never the less, I hope you'll find it useful.

This is how I would write a many-to-many relationship between Authors and Books, and be able to access a book from it's author and viceversa, as well.

The following is a complete example, which you can copy&paste&compile.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WordAndImages.Entities;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;
using System.ComponentModel.DataAnnotations;

namespace Bookstore
{
    public class Author
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Book> Books { get; set; }
        public Author()
        {
            Books = new List<Book>();
        }
    }

    public class Book
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public virtual ICollection<Author> Authors { get; set; }
        public Book()
        {
            Authors = new List<Author>();
        }
    }

    public class Context : DbContext
    {
        static Context()
        {
            Database.SetInitializer<Context>(null);
        }

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new AuthorMap());
            modelBuilder.Configurations.Add(new BookMap());
        }
    }

    public class BookMap : EntityTypeConfiguration<Book>
    {
        public BookMap()
        {
            this.HasMany(t => t.Authors)
            .WithMany(a => a.Books)
            .Map(t => t.ToTable("authorsbooks").MapLeftKey("book_id").MapRightKey("author_id"));
        }
    }

    public class AuthorMap : EntityTypeConfiguration<Author>
    {
        public AuthorMap()
        {
            this.HasMany(a => a.Books)
            .WithMany(b => b.Authors)
            .Map(t => t.ToTable("authorsbooks").MapLeftKey("author_id").MapRightKey("book_id"));
        }
    }

    class Program
    {
        static void Main(string[] args)
        {

            #region Saving

            var context = new Context();
            context.Database.Delete();
            context.Database.CreateIfNotExists();

            var book1 = new Book { Title = "Joy" };
            var book2 = new Book { Title = "Happy" };

            var author1 = new Author { Name = "Lisa" };
            var author2 = new Author { Name = "John" };
            var author3 = new Author { Name = "Luca" };

            book1.Authors.Add(author1);
            book1.Authors.Add(author2);

            book2.Authors.Add(author1);
            book2.Authors.Add(author3);

            context.Books.Add(book1);
            context.Books.Add(book2);
            context.SaveChanges();

            #endregion

            #region Accessing a book from it's author and viceversa

            var context2 = new Context();


            var recovered_book1 = context2.Books.Where(b => b.Title == "Joy").FirstOrDefault();
            Console.WriteLine(string.Format("Book1 has title {0} and has {1} authors", recovered_book1.Title, recovered_book1.Authors.Count));
            foreach (var author in recovered_book1.Authors)
                Console.WriteLine(author.Name);

            var recovered_book2 = context2.Books.Where(b => b.Title == "Joy").FirstOrDefault();
            Console.WriteLine(string.Format("Book2 has title {0} and has {1} authors", recovered_book2.Title, recovered_book2.Authors.Count));
            foreach (var author in recovered_book1.Authors)
                Console.WriteLine(author.Name);


            var recovered_author1 = context2.Authors.Where(a => a.Name == "Lisa").FirstOrDefault();
            Console.WriteLine(string.Format("{0} wrote {1} books", recovered_author1.Name, recovered_author1.Books.Count));
            foreach (var book in recovered_author1.Books)
                Console.WriteLine(book.Title);

            Console.ReadLine();
            #endregion
        }
    }
}

When it comes to recover a book from the database, it runs this query

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Title] AS [Title]
FROM [dbo].[Books] AS [Extent1]
WHERE N'Joy' = [Extent1].[Title]

When it recovers (with lazy load) its authors, it runs

exec sp_executesql N'SELECT 
[Extent2].[Id] AS [Id], 
[Extent2].[Name] AS [Name]
FROM  [dbo].[authorsbooks] AS [Extent1]
INNER JOIN [dbo].[Authors] AS [Extent2] ON [Extent1].[author_id] = [Extent2].[Id]
WHERE [Extent1].[book_id] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1