实体框架4.1代码 - 实现域间服务多对多关系的第一种方法 [英] Entity Framework 4.1 Code-First approach to realize many-to-many relation over Domain-Services

问题描述

在使用最新的实体框架和代码优先创建数据库模型时，我遇到了一些问题（请参阅实体框架4.1代码创建多对多关系的第一种方法）。

同时，我已经弄清楚，问题不在于实体框架本身，而是与WCF RIA DomainServices一起使用。

为了这个缘故的完整性 - 这是我相关的代码代码：

  // 
 //模型
 // 
 
 public class作者
 {
 public Author（）
 {
 this.Books = new Collection< Book>（）; 
} 
 
 [DatabaseGenerated（DatabaseGeneratedOption.Identity）] 
 [Key] 
 public int ID {get;组; 
 
 [MaxLength（32）] 
 [必需] 
 public string Name {get;组; } 
 
 [Include] 
 [Association（Author_Book，ID，ID）] 
 public Collection< Book>书籍{get;组; 
} 
 
 public class Book 
 {
 public Book（）
 {
 // this.Authors = new Collection< Author> （）; 
} 
 
 [DatabaseGenerated（DatabaseGeneratedOption.Identity）] 
 [Key] 
 public int ID {get;组; 
 
 [MaxLength（32）] 
 [必需] 
 public string Name {get;组; } 
 
 //我真的想创建这个导航属性，但似乎没有办法
 //来告诉我的DomainService来包含它。 
 // public Collection< Author>作者{get;组; } 
} 
 
 
 // 
 // 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>作者{get;组; } 
 public DbSet< Book>书籍{get;组; 
 
 
 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（作者）
 {
 this.m_modelContext.Insert（作者）; 
} 
 
 public void UpdateAuthor（作者）
 {
 this.m_modelContext.Update（作者，this.ChangeSet.GetOriginal（作者））; 
} 
 
 public void DeleteAuthor（作者）
 {
 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（作者，this.ChangeSet.GetOriginal（作者））; 
} 
 
 public void DeleteBook（Book Author）
 {
 this.m_modelContext.Delete（Author）; 
} 
 
 
 protected override void Dispose（bool disposal）
 {
 if（disposal）
 this.m_modelContext.Dispose（）; 
 base.Dispose（disposal）; 
} 
 
 protected override bool PersistChangeSet（）
 {
 this.m_modelContext.SaveChanges（）; 
 return base.PersistChangeSet（）; 
} 
 
 
 private BookAuthorModelContext m_modelContext; 
} 
  

按预期创建SQL表。在我的客户端应用程序中，我使用了一个带有DomainDataSource的RadGridView：

 < 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 ：作者，CreateList = true}> 
 
< telerik：RadGridView x：Name =AuthorGridViewDataContext ={Binding ElementName = AuthorDomainDataSource}
 ItemsSource ={Binding Data}IsBusy ={Binding IsBusy} /> 
< / UserControl> 
  

现在有趣了。如果我将两个记录添加到空数据库 - 一个到作者表，另一个记录到图表，则记录的ID字段为1。有趣的是，GetAuthorsQuery（）与包含的书籍将书添加到作者的图书属性。
创建的Author_Book（join-）表中没有条目。所以，我已经启动了我的SQL-Profiler来查看到底在做什么。这就是我发现的：

  SELECT 
 [Project1]。[ID] AS [ID]，
 [Project1] [名称] AS [名称]，
 [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） （NULL AS int）ELSE 1 END AS [C1] 
 FROM（SELECT TOP（20）[c]。[ID] AS [ID]，[c] [名称] AS [名称] 
 FROM [dbo]。[作者] AS [c]）AS [Limit1] 
 LEFT OUTER JOIN（SELECT [Extent2]。[Author_ID] AS [Author_ID]，[Extent3]。[ID] AS [ID] [Extent3] [名称] AS [名称] 
 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 
  

他为什么这样做？我真的想使用我的多对多关系，但我也很乐意使用单向关系（至少会有一些工作）。

谢谢你提前获得任何帮助。

解决方案

我没有使用属性，而是使用地图。从来没有，我希望你会发现它很有用。

这是我将如何编写作者和书籍之间的多对多关系，并且能够

以下是一个完整的示例，您可以复制&粘贴和编译。

 使用系统; 
使用System.Collections.Generic; 
使用System.Linq; 
使用System.Text; 
使用WordAndImages.Entities; 
使用System.Data.Entity; 
使用System.Data.Entity.ModelConfiguration; 
使用系统; 
使用System.Collections.Generic; 
使用System.Linq; 
使用System.Text; 
使用System.Data.Entity; 
使用System.Data.Entity.ModelConfiguration; 
使用System.ComponentModel.DataAnnotations; 
 
命名空间Bookstore 
 {
 public class Author 
 {
 public int Id {get;组; } 
 public string Name {get;组; } 
 public virtual ICollection< Book>书籍{get;组; } 
 public Author（）
 {
 Books = new List< Book>（）; 
} 
} 
 
 public class Book 
 {
 public int Id {get;组; } 
 public string标题{get;组; } 
 public virtual ICollection< Author>作者{get;组; } 
 public Book（）
 {
 Authors = new List< Author>（）; 
} 
} 
 
 public class上下文：DbContext 
 {
 static Context（）
 {
 Database.SetInitializer< Context> ;（空值）; 
} 
 
 public DbSet< Author>作者{get;组; } 
 public DbSet< Book>书籍{get;组; 
 
 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））; 
} 
} 
 
类程序
 {
 static void Main（string [] args）
 {
 
 #region保存
 
 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作者{Name =Lisa}; 
 var author2 = new作者{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从它的作者和副手访问一本书
 
 var context2 = new Context（）; 
 
 
 var restored_book1 = context2.Books.Where（b => b.T​​itle ==Joy）。FirstOrDefault（）; 
 Console.WriteLine（string.Format（Book1 has title {0} and have {1} authors，restored_book1.Title，restored_book1.Authors.Count））; 
 foreach（var author in restored_book1.Authors）
 Console.WriteLine（author.Name）; 
 
 var restored_book2 = context2.Books.Where（b => b.T​​itle ==Joy）。FirstOrDefault（）; 
 Console.WriteLine（string.Format（Book2 has title {0} and have {1} authors，restored_book2.Title，restored_book2.Authors.Count））; 
 foreach（var author in restored_book1.Authors）
 Console.WriteLine（author.Name）; 
 
 
 var restored_author1 = context2.Authors.Where（a => a.Name ==Lisa）。FirstOrDefault（）; 
 Console.WriteLine（string.Format（{0}写了{1}书，restored_author1.Name，restored_author1.Books.Count））; 
 foreach（var book in restored_author1.Books）
 Console.WriteLine（book.Title）; 
 
 Console.ReadLine（）; 
 #endregion 
} 
} 
} 
  

当从数据库恢复一本书时，它运行这个查询

  SELECT TOP（1）
 [Extent1 ] [Id] AS [Id]，
 [Extent1]。[标题] AS [标题] 
 FROM [dbo]。[图书] AS [Extent1] 
 WHERE N'Joy' = [Extent1]。[标题] 
  

当它的作者恢复（懒加载）时，它运行

  exec sp_executesql N'SELECT 
 [Extent2]。[Id] AS [Id]，
 [ Extent2] [名称] AS [名称] 
 FROM [dbo]。[authorbooks] AS [Extent1] 
 INNER JOIN [dbo]。[作者] AS [Extent2] ON [Extent1] ] = [Extent2]。[Id] 
 WHERE [Extent1]。[book_id] = @ EntityKeyValue1'，N'@ EntityKeyValue1 int'，@ EntityKeyValue1 = 1 
  pre> 
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


                        
这篇关于实体框架4.1代码 - 实现域间服务多对多关系的第一种方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！