Entity Framework 4.1 Code-First Ansatz zur Realisierung einer Many-to-Many-Beziehung über Domain-Services

Question

Ich hatte einige Probleme beim Erstellen eines Datenbankmodells mit dem neuesten Entity Framework und Code-First (siehe Entity Framework 4.1 Code First-Ansatz zur Erstellung einer Many-to-Many-Beziehung für Details).

Unterdessen habe ich herausgefunden, dass das Problem nicht mehr das Entity Framework selbst ist, sondern die Verwendung zusammen mit WCF RIA DomainServices.

Zur Vollständigkeit - das ist mein relevantes Code-First-Code:

//
// Modelle
//

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

    [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 Books { get; set; }
}

public class Book
{
    public Book()
    {
        // this.Authors = new Collection();
    }

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

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

    // Ich würde wirklich gerne diese Navigations-Eigenschaft erstellen, aber es scheint keinen Weg zu geben, meinem DomainService mitzuteilen, sie einzuschließen.
    // public Collection Authors { get; set; }
}

//
// Zuordnungen
//

public class AuthorMapping : EntityTypeConfiguration
{
    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 Authors  { get; set; }
    public DbSet   Books { get; set; }

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

//
// DomainService
//

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

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

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

Die SQL-Tabellen werden wie erwartet erstellt. In meiner Clientanwendung verwende ich ein RadGridView mit einem DomainDataSource:

Jetzt wird es interessant. Wenn ich zwei Datensätze zur leeren Datenbank hinzufüge - einen zum Autoren und einen anderen zum Buch - dann ist das 'ID'-Feld beider Datensätze '1'. Das Interessante ist, dass die GetAuthorsQuery() mit eingeschlossenen Büchern das Buch zur 'Books'-Eigenschaft des Autors hinzufügt. Es gibt keinen Eintrag in der erstellten Author_Book (Join-)Tabelle. Also habe ich meinen SQL-Profiler gestartet, um zu sehen, was hier genau passiert. Das habe ich herausgefunden:

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

Warum macht er das? Ich würde wirklich gerne meine Many-to-Many-Beziehung verwenden, aber ich wäre auch glücklich, eine unidirektionale Beziehung zu verwenden (zumindest würde dann etwas funktionieren).

Vielen Dank im Voraus für jede Hilfe.

Answer 1

Ich verwende keine Attribute, sondern Maps. Trotzdem hoffe ich, dass Sie es nützlich finden werden.

So würde ich eine Many-to-Many-Beziehung zwischen Autoren und Büchern schreiben und in der Lage sein, von einem Autor auf ein Buch zuzugreifen und umgekehrt.

Im Folgenden finden Sie ein vollständiges Beispiel, das Sie kopieren & einfügen & kompilieren können.

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 Books { get; set; }
        public Author()
        {
            Books = new List();
        }
    }

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

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

        public DbSet Authors { get; set; }
        public DbSet Books { get; set; }

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

    public class BookMap : EntityTypeConfiguration
    {
        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
    {
        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
        }
    }
}

Wenn es darum geht, ein Buch aus der Datenbank wiederherzustellen, führt es diese Abfrage aus

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

Wenn es (mit Lazy Load) seine Autoren wiederherstellt, führt es aus

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