EF代码首先在现有的数据库上映射一对多 [英] EF Code First on existing database, mapping one to many
问题描述
测量表具有以下结构：
MeasurementId INT
DeviceId INT FOREIGN KEY REFERENCES设备（DeviceId ）
名称VARCHAR，
PRIMARY KEY（MeasurementId）
我需要的是添加可空的 ParentId 自引用外键，因为我不能修改现有的表，我创建了新的表， Measurement_Parent ：
MeasurementId INT FOREIGN KEY REFERENCES Measurements（MeasurementId），
ParentId INT FOREIGN KEY参考测量（MeasurementId） ，
PRIMARY KEY（MeasurementId）
我有以下实体：
public partial class Measurement
{
public Measurement（）
{
this.Children = new List< ; Measurem耳鼻喉科>（）;
}
public Int32 MeasurementId {get;组; }
public virtual Measurement Parent {get;组; }
public Int32 DeviceId {get;组; }
public virtual Device Device {get;组; }
public String Name {get;组; }
public virtual ICollection< Measurement>孩子{get;组; }
}
现在是棘手的部分。我已经尝试了许多不同的方法来获得这个工作，但没有成功。
//主键
this.HasKey（m => m.MeasurementId）;
// Table&列映射
this.Property（t => t.MeasurementId）
.HasDatabaseGeneratedOption（DatabaseGeneratedOption.None）;
// Table&列映射
this.ToTable（Measurement）;
this.Property（m => m.MeasurementId）;
this.Property（m => m.DeviceId）;
this.Property（m => m.Name）;
//关系
//每个测量参考设备执行测量。
this.HasRequired（d => d.Device）
.WithMany（m => m.Measurements）
.HasForeignKey（d => d.DeviceId）;
//每个测量都可以有可选的父项。
this.HasOptional（measurement => measurement.Parent）
.WithMany（）// .WithMany（measurement => measurement.Children）??
.Map（m =>
{
m.MapKey（ParentId）;
m.ToTable（Measurement_Parent）;
}）;
不幸的是，这可以让我在加载我的应用程序时出现奇怪的错误：
元数据查询失败：api / EDW / Metadata;在模型中找不到指定的Measurement_Parent表。确保表名已被正确指定。
我不知道为什么会发生这种情况，因为表是。我尝试将这两个表映射到一个实体（表拆分），但是因为ParentId可以是 NULL ，并且EF生成 INNER JOIN 而不是 LEFT OUTER JOIN 用于此映射，它没有工作，因为 Measurement 表中的某些行被忽略因为他们在 Measurement_Parent 中没有任何相应的行。
基本上我需要的是具有可选的 Parent 属性，参考父测量和儿童测量的列表。
您需要的是实体拆分 - 在两个或多个表中分割单个实体。这隐含涉及共享主键 - 在这种情况下，关系表中的共享密钥将是子实体的ID。您可以通过调用多个 Map 方法来执行此操作，每个方法都调用 EntityMappingConfiguration.Properties 来定义应包含哪些属性在该映射片段中，调用 ToTable 设置表名。
modelBuilder.Entity< Measurement>（）
.HasKey（ke => ke.MeasurementId）
.Map（emc =>
{
emc.Properties => new {pe.MeasurementId，pe.Name，pe.DeviceId}）;
emc.ToTable（Measurement）;
}）
.Map（emc =>
{
emc.Properties（pe => new {pe.MeasurementId，pe.ParentId}）;
//也许命名这个MeasurementExtension？这个表可以
//被使用对于您要添加到Measurement实体的任何属性，
emc.ToTable（Measurement_Parent）;
//为此示例的清晰度我已命名为PK Child_MeasurementId
/ /但在现实世界中使用我会命名为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）;
这是数据库中的结果（注意我没有设置一个FK / nav支持设备，但你知道如何做到这一点）：
理想情况下， Parent_MeasurementId 并且记录将被删除，而不是将该列设置为null（如果它们不是父级），但实体拆分看起来似乎不可行。无论如何，这完全符合您的要求 - 扩展一个实体而不修改初始底层表。
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.
Measurement table has following structure:
MeasurementId INT
DeviceId INT FOREIGN KEY REFERENCES Devices (DeviceId)
Name VARCHAR,
PRIMARY KEY (MeasurementId)
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)
I have following entity:
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; }
}
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.
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.
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 );
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):
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屋！
