EF代码首先对现有的数据库，映射一对多 [英] EF Code First on existing database, mapping one to many

问题描述

我有应用程序，我不能修改生成的数据库（我可以添加表，视图等，但我不能修改现有的表，列添加到它们）。我上它采用BreezeJS允许通过的OData协议数据的Web应用程序查询的客户端部分的Web应用程序。

I have a database generated by application which I can't modify (I can add tables, views and such but I can't modify existing tables, add columns to them). I work on a web application which uses BreezeJS to allow the client-side part of the web app query for the data via OData protocol.

测量表具有以下结构：

MeasurementId INT
DeviceId INT FOREIGN KEY REFERENCES Devices (DeviceId)
Name VARCHAR,
PRIMARY KEY (MeasurementId)

我需要的是添加可空的ParentId 自参考外键，因为我不能修改现有的表，我创建了新的， Measurement_Parent ：

What I need is to add nullable ParentId self referencing foreign key and because I can't modify existing tables, I've created new one, Measurement_Parent:

MeasurementId INT FOREIGN KEY REFERENCES Measurements (MeasurementId),
ParentId INT FOREIGN KEY REFERENCES Measurements (MeasurementId),
PRIMARY KEY (MeasurementId)

我有以下实体：

public partial class Measurement
{
    public Measurement()
    {
        this.Children = new List<Measurement>();
    }

    public Int32 MeasurementId { get; set; }

    public virtual Measurement Parent { get; set; }

    public Int32 DeviceId { get; set; }

    public virtual Device Device { get; set; }

    public String Name { get; set; }

    public virtual ICollection<Measurement> Children { get; set; }
}

现在棘手的部分。我尝试过许多不同的方法来得到这个工作，但没有成功。电流 EntityTypeConfiguration 我的实体是这样的：

Now the tricky part. I've tried many different approaches to get this working but without success. Current EntityTypeConfiguration for my entity looks like this:

// Primary Key
this.HasKey(m => m.MeasurementId);

// Table & Column Mappings

this.Property(t => t.MeasurementId)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

// Table & Column Mappings
this.ToTable("Measurement");
this.Property(m => m.MeasurementId);
this.Property(m => m.DeviceId);
this.Property(m => m.Name);

// Relationships

// Each measurement references device performing the measurement.
this.HasRequired(d => d.Device)
    .WithMany(m => m.Measurements)
    .HasForeignKey(d => d.DeviceId);

// Each measurement can have optional parent.
this.HasOptional(measurement => measurement.Parent)
    .WithMany() // .WithMany(measurement => measurement.Children) ??
    .Map(m =>
    {
        m.MapKey("ParentId");
        m.ToTable("Measurement_Parent");
    });

不幸的是这给了我怪异的错误而载入我的应用程序：

Unfortunately this gives me weird error while loading my app:

Metadata query failed for: api/EDW/Metadata; The specified table 'Measurement_Parent' was not found in the model. Ensure that the table name has been correctly specified.

我不知道为什么会这样，因为表的是的存在。我想这两个表映射到一个实体（表分裂），但由于的ParentId可 NULL 和EF生成的 INNER JOIN 而不是 LEFT OUTER JOIN 这个映射，也没有工作，因为测量某些行表中ommited因为他们没有在 Measurement_Parent 任何相应的行。

I have no idea why is this happening because the table is there. I tried mapping these two tables onto one entity (table splitting), but because the ParentId can be NULL and EF generated INNER JOIN instead of LEFT OUTER JOIN for this mapping, it didn't work because some rows in Measurement table were ommited as they didn't have any corresponding rows in Measurement_Parent.

基本上我需要的是有可选的父参照父测量和列表孩子测量的属性。

Basically what I need is to have optional Parent property with reference to parent measurement and list of Children measurements.

推荐答案

您需要的是实体分裂 - 分裂两个或多个表之间的一个实体。这隐含包括共享主键 - 在此情况下，在关系表的共享密钥将是儿童实体的标识。您可以通过调用多个地图方法做到这一点，每一个调用 EntityMappingConfiguration.Properties 来定义哪些属性应包括在映射片段和 ToTable通话来设置表名。

What you need is entity splitting - splitting a single entity among two or more tables. This implicitly involves shared primary key - in this case, the shared key in the relationship table will be the child entities' ID. You do this by calling multiple Map methods, each with a call to EntityMappingConfiguration.Properties to define which properties should be included in that mapping fragment and a call to ToTable to set the table name.

modelBuilder.Entity<Measurement>()
    .HasKey( ke => ke.MeasurementId )
    .Map( emc =>
    {
        emc.Properties( pe => new { pe.MeasurementId, pe.Name, pe.DeviceId } );
        emc.ToTable( "Measurement" );
    } )
    .Map( emc =>
    {
        emc.Properties( pe => new { pe.MeasurementId, pe.ParentId } );
        // maybe name this MeasurementExtension?  This table could
        //  be used for any properties you wish to add to the Measurement entity
        emc.ToTable( "Measurement_Parent" );

        // for this example's clarity I've named the PK Child_MeasurementId
        //  but in real-world use I would name it MeasurementId
        emc.Property( pe => pe.MeasurementId ).HasColumnName( "Child_MeasurementId" );
        emc.Property( pe => pe.ParentId ).HasColumnName( "Parent_MeasurementId" );
    } );

modelBuilder.Entity<Measurement>()
    .HasOptional( npe => npe.Parent )
    .WithMany( npe => npe.Children )
    .HasForeignKey( fke => fke.ParentId );

下面是结果在DB（注意我并没有建立一个FK /导航道具为设备，但你知道如何做到这一点）：

Here's the result in the DB (note I did not set up a FK/nav prop for Device but you know how to do that):

理想情况下， Parent_MeasurementId 字段会不为空和记录会被删除，而不是设置该栏为空，如果他们没有父母，但是这似乎并不可能与实体的分裂。在任何情况下，这样做你正在寻找什么 - 无需修改最初的基础表扩展实体

Ideally, the Parent_MeasurementId field would be not null and the record would be deleted instead of setting that column to null if their is no parent, but that doesn't seem possible with entity splitting. In any case, this does exactly what you're looking for - extending an entity without modifying the initial underlying table.

这篇关于EF代码首先对现有的数据库，映射一对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！