EF代码首先具有多对多的自引用关系 [英] EF Code First with many to many self referencing relationship

问题描述

我开始使用EF Code First与MVC，有点困惑的东西。我有以下数据库结构（对不起，我不允许发布图像不幸）：

表 - 产品

表 - 相关产品

1-Many on Products.ProductID - > RelatedProducts.ProductID

1-Many on Products.ProductID - > RelatedProducts.RelatedProductID

基本上我有一个产品可以有一系列与之相关的产品。这些都保存在相关产品表中，其中包含由ProductID和相关产品的ProductID定义的关系，该名称相关产品名称为RelatedProductID。在我的代码中，我已经生成了以下类：

  public class MyDBEntities：DbContext 
 {
 public DbSet< Product>产品{get;组; } 
 public DbSet< RelatedProduct>相关产品{get;组; } 
} 
 
 public class Product 
 {
 public Guid ProductID {get;组; } 
 public string Name {get;组; } 
 public string标题{get;组; } 
 public string描述{get;组; } 
 public decimal价格{get;组; } 
 public Guid CategoryID {get;组; } 
 public string ImageURL {get;组; } 
 public string LargeImageURL {get;组; } 
 public string提供{get;组; } 
 public virtual List< RelatedProduct>相关产品{get;组; } 
} 
 public class RelatedProduct 
 {
 public Guid ProductID {get;组; } 
 public Guid RelatedProductID {get;组; } 
 public virtual Product Product {get;组; } 
 public virtual Product SimilarProduct {get;组; } 
} 
  

然后我尝试使用以下代码访问这些代码：

  myDB.Products.Include（RelatedProducts）。其中（x => x.ProductID == productID） .FirstOrDefault（）; 
  

但是我不断收到以下错误：

  {无效的列名称ProductProductID2'.\r\\\
无效的列名称ProductProductID2。\\\
Invalid列名称ProductProductID.\r\\\
Invalid列名称ProductProductID1。\\\\无效的列名称ProductProductID2。} 
  

什么我做错了吗我基本上想要得到一个产品，然后迭代相关产品并显示该产品信息。

解决方案

答案的第一部分是那么EF4 CTP5没有正确地将您的POCO映射到数据库，因为它不够聪明。如果你查看数据库，你会得到：

  CREATE TABLE RelatedProducts（
 RelatedProductID uniqueidentifier NOT NULL，
 ProductID uniqueidentifier NOT NULL，
 ProductProductID uniqueidentifier NULL，
 ProductProductID1 uniqueidentifier NULL，
 ProductProductID2 uniqueidentifier NULL，
 PRIMARY KEY CLUSTERED 
（
 RelatedProductID ASC 
）
）ON [PRIMARY] 
  

Yuck！这需要通过一些手动工作来修复。在您的DbContext中，您可以添加如下规则：

  protected override void OnModelCreating（ModelBuilder modelBuilder）
 {
 modelBuilder.Entity< Product>（）
 .Property（p => p.ProductID）
 .HasDatabaseGenerationOption（DatabaseGenerationOption.Identity）; 
 
 modelBuilder.Entity< RelatedProduct>（）
 .HasKey（rp => new {rp.ProductID，rp.RelatedProductID}）; 
 
 modelBuilder.Entity< Product>（）
 .HasMany（p => p.RelatedProducts）
 .WithRequired（rp => rp.Product）
 .HasForeignKey（rp => rp.ProductID）
 .WillCascadeOnDelete（）; 
 
 modelBuilder.Entity< RelatedProduct>（）
 .HasRequired（rp => rp.SimilarProduct）
 .WithMany（）
 .HasForeignKey（rp => rp.RelatedProductID）
 .WillCascadeOnDelete（false）; 
 
 base.OnModelCreating（modelBuilder）; 
} 
  

I am starting out with using the EF Code First with MVC and am a bit stumped with something. I have the following db structure (Sorry but I was not allowed to post an image unfortunately):

Table - Products
Table - RelatedProducts

1-Many on Products.ProductID -> RelatedProducts.ProductID
1-Many on Products.ProductID -> RelatedProducts.RelatedProductID

Basically I have a product that can have a series of products that are related to it. These are kept in the RelatedProducts table with the relationship defined by the ProductID and the ProductID of the related product which I have named RelatedProductID. In my code I has produced the following classes:

public class MyDBEntities : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<RelatedProduct> RelatedProducts { get; set; }
}

public class Product
{
    public Guid ProductID { get; set; }
    public string Name { get; set; }
    public string Heading { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
    public Guid CategoryID { get; set; }
    public string ImageURL { get; set; }
    public string LargeImageURL { get; set; }
    public string Serves { get; set; }
    public virtual List<RelatedProduct> RelatedProducts { get; set; }
}
public class RelatedProduct
{
    public Guid ProductID { get; set; }
    public Guid RelatedProductID { get; set; }
    public virtual Product Product { get; set; }
    public virtual Product SimilarProduct { get; set; }
}

I then try to access these in code using:

myDB.Products.Include("RelatedProducts").Where(x => x.ProductID == productID).FirstOrDefault();

But I keep getting the following error:

{"Invalid column name 'ProductProductID2'.\r\nInvalid column name 'ProductProductID2'.\r\nInvalid column name 'ProductProductID'.\r\nInvalid column name 'ProductProductID1'.\r\nInvalid column name 'ProductProductID2'."}

What am I doing wrong? I basically want to get a product then iterate through the RelatedProducts and display that product info.

解决方案

The first part of the answer is that EF4 CTP5 is not correctly mapping your POCOs to the database because it's not smart enough. If you check out the database, you get:

    CREATE TABLE RelatedProducts(
        RelatedProductID uniqueidentifier NOT NULL,
        ProductID uniqueidentifier NOT NULL,
        ProductProductID uniqueidentifier NULL,
        ProductProductID1 uniqueidentifier NULL,
        ProductProductID2 uniqueidentifier NULL,
        PRIMARY KEY CLUSTERED 
        (
            RelatedProductID ASC
        )
    ) ON [PRIMARY]  

Yuck! This needs to be fixed with some manual work. In your DbContext, you add rules like so:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .Property(p => p.ProductID)
            .HasDatabaseGenerationOption(DatabaseGenerationOption.Identity);

        modelBuilder.Entity<RelatedProduct>()
            .HasKey(rp => new { rp.ProductID, rp.RelatedProductID });

        modelBuilder.Entity<Product>()
            .HasMany(p => p.RelatedProducts)
            .WithRequired(rp => rp.Product)
            .HasForeignKey(rp => rp.ProductID)
            .WillCascadeOnDelete();

        modelBuilder.Entity<RelatedProduct>()
            .HasRequired(rp => rp.SimilarProduct)
            .WithMany()
            .HasForeignKey(rp=> rp.RelatedProductID)
            .WillCascadeOnDelete(false);

        base.OnModelCreating(modelBuilder);
    }

这篇关于EF代码首先具有多对多的自引用关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！