EF代码优先:同一收集类型一对多 [英] EF code first: one-to-many twice to same collection type

问题描述

简化的:在我的数据库中，我有一种产品在不同的日期以不同的价格出售.换句话说，它具有价格历史记录.我有两个类:具有一对多关系的 Product 和 Price :

Simplified: In my DB I have a product that was sold with different prices on different dates. In other words it has a Price History. I have two classes: Product and Price with a one-to-many relationship:

public class Product
{
    public int ProductId {get; set;}
    public string Name {get; set;}

    public ICollection<Price> Prices {get; set;}
}

public class Price
{
    public int PriceId {get; set;}

    // foreign key to Product:
    public int ProductId {get; set;}
    public Product Product {get; set;}

    public DateTime ActivationDate {get; set;}
    public decimal value {get; set;}
}

public class MyDbContext : DbContext
{
    public DbSet<Price> Prices { get; set; }
    public DbSet<Product> Products { get; set; }
}

到目前为止，Entity Framework知道如何处理.通过使用这两个类，我可以在特定日期获得特定产品的价格.

So far so good, Entity Framework knows how to handle this. With the use of these two classes I am able to get the price of a certain product on a certain date.

但是，如果我的产品有两个价格历史记录:购买价格历史记录和零售价格历史记录?

But what if my product has two price histories: a Purchase Price history and a Retail Price History?

public class Product
{
    public int ProductId {get; set;}
    public string Name {get; set;}

    public ICollection<Price> PurchasePrices {get; set;}
    public ICollection<Price> RetailPrices {get; set;}  
}

因为这两个集合属于同一类型，所以我不想用相同类型的对象填充单独的表(真正的原因:我有很多带有价格集合的类).

Because these two collections are to the same type I don't want separate tables filled with object of the same type (the real reason: I have a lot of classes with price collections).

因此，我必须使用Fluent API进行一些编码.我的直觉说我需要像使用多对多关系那样连接表，可以通过使用ManyToManyNavigationPropertyConfiguration.Map.

So I have to do a bit of coding using Fluent API. My gut feeling says I need joining tables, like in a many-to-many relationship, mayby using the ManyToManyNavigationPropertyConfiguration.Map.

该怎么做?

推荐答案

阅读了有关

After reading a story about one-to-one foreign key associations and using this for a one-to-many association I was able to implement it with the following requirements:

• 我可以有许多具有相同类型T的属性的不同类.
• 所有类型T的实例都可以放在一个表中，即使该类型的所有者"在不同的表中也是如此.
  • 一个类甚至可以具有两个T类型的属性.

  例如:一个客户可能有一个BillingAddress和DeliveryAddress，它们的类型均为Address.这两个地址都可以放在一个表中:地址.

  For instance: A customer may have a BillingAddress and a DeliveryAddress, both of type Address. Both addresses can be put in one table: Address.

  public class Address
  {
      public int Id { get; set; }
      public string Street { get; set; }
      public string City { get; set; }
      public string ZipCode { get; set; }
  }
  
  public class User
  {
      public int UserId { get; set; }
      public string Name { get; set; }
  
      public int BillingAddressId { get; set; }
      public Address BillingAddress { get; set; }
      public int DeliveryAddressId { get; set; }
      public Address DeliveryAddress { get; set; }
  }
  
  public class MyDbContext : DbContext
  {
      public DbSet<Address> Addresses { get; set; }
      public DbSet<User> Users { get; set; }
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          modelBuilder.Entity<User>()
              .HasRequired(p => p.DeliveryAddress)
              .WithMany()
              .HasForeignKey(p => p.DeliveryAddressId)
              .WillCascadeOnDelete(false);
  
          modelBuilder.Entity<User>()
              .HasRequired(p => p.BillingAddress)
              .WithMany()
              .HasForeignKey(p => p.BillingAddressId)
              .WillCascadeOnDelete(false);
      }
  }
  

  此解决方案中的聪明之处在于该地址中没有拥有"用户.因此，如果我定义一个带有地址的新类，则可以将该地址添加到同一地址表中.因此，如果我有十个都具有地址的不同类，则不需要十个地址表.

  The smart thing in this solution is that the Address does not have an "owning" user in it. So if I define a new class with an Address this address can be added to the same table of Address. So If I have ten different classes that all have an address I don't need ten address tables.

  如果您有一组地址怎么办?

  通常，在一对多关系中，多端需要一侧的外键以及对所有者"的引用:

  Normally in a one-to-many relation the many side needs a foreign key to the one side plus a reference to the "owner":

  一个经常看到的例子:博客和帖子:一个博客有很多帖子.一篇帖子恰好属于一个博客:

  An often seen example: blogs and posts: one blog has many posts. One post belongs to exactly one blog:

  public class Blog
  {
      public int Id { get; set; }
      public string Name { get; set; }
      virtual public ICollection<Post> Posts {get; set;}
  }
  
  public class Post
  {
      public int Id { get; set; }
      public string Text { get; set; }
  
      public int BlogId { get; set; }
      public Blog Blog { get; set; }
   }
  

  此命名将自动导致正确的一对多关系，但是如果要在DbContext中指定:

  This naming will automatically lead to the correct one-to-many relationship, but if you want to specify in the DbContext:

  public DbSet<Blog> Blogs { get; set; }
  public DbSet<Post> Posts { get; set; }
  

  以及在OnModelCreating中:

  and in OnModelCreating:

  modelBuilder.Entity<Blog>()
      .HasMany(b => b.Posts)
      .WithRequired(post => post.Blog)
      .HasForeignKey(post => post.BlogId);
  

  即使您不需要Post.Blog，也不能因为创建模型而删除此属性.如果将其删除，最终将使用魔术字符串定义外键.

  Even if you would not need Post.Blog, you can't remove this property, because of the model creating. If you would remove it you would end up with magic strings to define the foreign key.

  为了也可以有一个地址集合(或者在我最初的问题中:很多价格历史记录，其中每个价格历史记录都是价格的集合)，我将这两种方法结合在一起.

  To be able to also have a collection of addresses (or in my original question: a lot of price histories, where each price history is a collection of prices) I combined these two methods.

  public class Price
  {
      public int Id { get; set; }
      public int PriceHistoryId { get; set; }
      public virtual PriceHistory PriceHistory { get; set; }
  
      public DateTime ActivationDate { get; set; }
      public decimal Value { get; set; }
  }
  
  public class PriceHistory
  {
      public int Id { get; set; }
      virtual public ICollection<Price> Prices { get; set; }
  }
  
  public class Product
  {
      public int Id { get; set; }
      public string Name { get; set; }
  
      // Purchase Prices
      public virtual PriceHistory PurchasePriceHistory { get; set; }
      public int PurchasePriceHistoryId { get; set; }
  
      // Retail prices
      public virtual PriceHistory RetailPriceHistory { get; set; }
      public int RetailPriceHistoryId { get; set; }
  }
  
  public class MyDbContext : DbContext
  {
      public DbSet<Product> Products { get; set; }
      public DbSet<PriceHistory> PriceHistories { get; set; }
      public DbSet<Price> Prices { get; set; }
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          // one price history has many prices: one to many:
          modelBuilder.Entity<PriceHistory>()
              .HasMany(p => p.Prices)
              .WithRequired(price => price.PriceHistory)
              .HasForeignKey(price => price.PriceHistoryId);
  
          // one product has 2 price histories, the used method is comparable
          // with the method user with two addresses
          modelBuilder.Entity<Product>()
              .HasRequired(p => p.PurchasePriceHistory)
              .WithMany()
              .HasForeignKey(p => p.PurchasePriceHistoryId)
              .WillCascadeOnDelete(false);
  
          modelBuilder.Entity<Product>()
              .HasRequired(p => p.RetailPriceHistory)
              .WithMany()
              .HasForeignKey(p => p.RetailPriceHistoryId)
              .WillCascadeOnDelete(false);
      }
  }
  

  我已经在具有多个价格历史记录的其他类中对其进行了测试:-所有价格将在一张桌子中-所有价格历史记录都将在一张表中-每个对价格历史记录的引用都需要一个priceHistoryId.

  I've tested it with other classes that have several price histories: - All prices will be in one table - All price histories will be in one table - Each reference to a price history needs a priceHistoryId.

  如果您仔细观察结果，实际上就是价格历史是耦合表的多对多关系的实现.

  If you look closely to the result it is in fact the implementation of a many-to-many relation where the price history is the coupling table.

  我试图删除PriceHistory类，并让一个Product在OnModelCreating中具有多个价格对多个价格的集合，但这将导致带有魔术字符串的"Map"语句，并为每个语句分别创建表价格历史.

  I've tried to remove the PriceHistory class, and let a Product have several collections of Prices with a many-to-many in OnModelCreating, but that would lead to "Map" statements with magic strings, and separate tables for each PriceHistory.

  链接到说明关于如何实现多对多

  这篇关于EF代码优先:同一收集类型一对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！