文字或常数作为EF代码中的复合键的一部分 [英] literal or constant as part of composite key in EF code first
问题描述
那就是说，我需要一个困境分配两个表之间的关系。一个表格ArCodes具有由CodeType和Code组成的复合键（均为字符串）。 CodeType列确定代码的类型，代码列是代码类型唯一的标识符。
public class ArCode {
[Column（cod_typ，Order = 0），Key]
public string CodeType {get;组; }
[Column（ar_cod，Order = 1），Key]
public string Code {get;组; }
[Column（desc）]
public string说明{get;组; }
}
另一张表发票需要有一个关系到ArCodes表中查找ship via代码和条款代码。
public class Invoice {
[Column（pi_hist_hdr_invc_no），Key]
public int InvoiceNumber {get;组; }
[Column（shp_via_cod）]
public string ShipViaCode {get;组; }
public ArCode ShipVia {get;组; }
[Column（terms_cod）]
public string TermsCode {get;组; }
public ArCode条款{get;组; }
}
我想为 ShipVia财产和条款财产。但是，我不确定如何关于复合键的CodeType部分。对于通过代码，代码类型应为S，代码条款代码，代码类型应为T。
我尝试过跟随DB上下文，但它没有工作：
protected override void OnModelCreating（DbModelBuilder modelBuilder）{
/ /设置表名称
modelBuilder.Entity< ArCode>（）。ToTable（ARCODS+ CompanyCode）;
modelBuilder.Entity< Invoice>（）。ToTable（IHSHDR+ CompanyCode）;
//
//设置关系
//
// 1发票< - > 0-1通过AR代码运送
modelBuilder.Entity< Invoice>（）
.HasOptional（invoice => invoice.ShipVia）
.WithMany（）
.HasForeignKey（发票=> new {TheType =S，invoice.ShipViaCode}）
;
base.OnModelCreating（modelBuilder）;
}
任何帮助将不胜感激。
更新＃1
好的，我把代码缩小到最简单的形式，我按照@GertArnold提供的解决方案。
public abstract class ArCode {
[Column（cod_typ）]
public string CodeType {get;组; }
[Column（ar_cod）]
public string Code {get;组; }
[Column（terms_desc）]
public string TermsDescription {get;组; }
[Column（terms_typ）]
public string TermsType {get;组; }
[Column（shp_via_desc）]
public string ShipViaDescription {get;组; }
[Column（tax_desc）]
public string TaxDescription {get;组;
public class TermsCode：ArCode {}
public class ShipViaCode：ArCode {}
public class Invoice {
[Column（pi_hist_hdr_invc_no），Key]
public int InvoiceNumber {get;组; }
[Column（hdr_invc_dat）]
public DateTime InvoiceDate {get;组; }
[Column（shp_via_cod）]
public string ShipViaCode {get;组; }
public ShipViaCode ShipVia {get;组; }
[Column（terms_cod）]
public string TermsCode {get;组; }
public TermsCode条款{get;组; }
public Invoice（）{
}
}
public class PbsContext：DbContext {
public DbSet< Invoice>发票{get;组; }
protected override void OnModelCreating（DbModelBuilder modelBuilder）{
modelBuilder.Entity< Invoice>（）。ToTable（IHSHDR）;
modelBuilder.Entity< ArCode>（）。HasKey（r => r.Code）.ToTable（ARCODS）;
modelBuilder.Entity< TermsCode>（）。Map（m => m.Requires（CodeType）
.HasValue（T）。HasColumnType（varchar）。 HasMaxLength（1）.IsRequired（））
.ToTable（ARCODS）;
modelBuilder.Entity< ShipViaCode>（）。Map（m => m.Requires（CodeType）
.HasValue（S）。HasColumnType（varchar）。HasMaxLength（1） .IsRequired（））
.ToTable（ARCODS）;
base.OnModelCreating（modelBuilder）;
}
public PbsContext（）
：base（name = PbsDatabase）{
}
}
但是，以下代码返回错误：
PbsContext context = new PbsContext（）;
var invoice = context.Invoices.OrderByDescending（r => r.InvoiceDate）.FirstOrDefault（）;
错误3032：从第28行开始映射片段的问题：条件成员映射了'IsNull = False'以外的条件的'ArCode.cod_typ'。或者删除ArCode.cod_typ上的条件或从映射中删除它。
如果我从ArCode类中删除CodeType列并在OnModelCreating事件中将所有CodeType引用更改为数据库列名cod_typ，则上述语句执行时不会出错。但是，发票-ShipVia和invoice.Terms都将为空事件，尽管数据库中存在匹配的记录。
更新＃2
public abstract class ArCode {
[Column（ar_cod）]
public string Code {get;组; }
[Column（terms_desc）]
public string TermsDescription {get;组; }
[Column（terms_typ）]
public string TermsType {get;组; }
[Column（shp_via_desc）]
public string ShipViaDescription {get;组; }
[Column（tax_desc）]
public string TaxDescription {get;组;
public class TermsCode：ArCode {}
public class ShipViaCode：ArCode {}
public class Invoice {
[Column（pi_hist_hdr_invc_no），Key]
public int InvoiceNumber {get;组; }
[Column（hdr_invc_dat）]
public DateTime InvoiceDate {get;组; }
[Column（shp_via_cod）]
public ShipViaCode ShipVia {get;组; }
[Column（terms_cod）]
public TermsCode条款{get;组; }
public Invoice（）{
}
}
public class PbsContext：DbContext {
public DbSet< Invoice>发票{get;组; }
protected override void OnModelCreating（DbModelBuilder modelBuilder）{
modelBuilder.Entity< Invoice>（）。ToTable（IHSHDR）;
modelBuilder.Entity< ArCode>（）。HasKey（r => r.Code）.ToTable（ARCODS）;
modelBuilder.Entity< TermsCode>（）。Map（m => m.Requires（CodeType）
.HasValue（T）。HasColumnType（varchar）。 HasMaxLength（1）.IsRequired（））
.ToTable（ARCODS）;
modelBuilder.Entity< ShipViaCode>（）。Map（m => m.Requires（CodeType）
.HasValue（S）。HasColumnType（varchar）。HasMaxLength（1） .IsRequired（））
.ToTable（ARCODS）;
base.OnModelCreating（modelBuilder）;
}
public PbsContext（）
：base（name = PbsDatabase）{
}
}
现在，以下代码返回错误：
PbsContext context = new PbsContext（）;
var invoice = context.Invoices.OrderByDescending（r => r.InvoiceDate）.FirstOrDefault（）;
EntityCommandExecutionException - 无效的列名称ShipVia_Code。无效的列名称Terms_Code。
您想要的是不可能的EF。 ArCode 具有复合键，因此与它的任何关联将不得不使用两个属性。这意味着在发票中，您需要四个属性（两对）来引用两个 ArCode 对象。但是其中两个属性（ CodeType ）属性不由数据库中的列进行备份，因此EF无法映射。
但是...有一种方法可以帮助你。您可以从 ArCode 创建两个派生类，让发票指单属性关联。但是你必须从模型中转移，并通过定义一个键来愚弄EF：
public abstract class ArCode {...} //抽象！
public class TermsCode：ArCode {}
public class ShipViaCode：ArCode {}
protected override void OnModelCreating（DbModelBuilder modelBuilder）
{
modelBuilder.Entity< Invoice>（）。ToTable（IHSHDR）;
modelBuilder.Entity< Invoice>（）。HasOptional（i => i.Terms）.WithOptionalDependent（）。Map（m => m.MapKey（terms_cod））;
modelBuilder.Entity< Invoice>（）。HasOptional（i =&i; i.ShipVia）.WithOptionalDependent（）。Map（m => m.MapKey（shp_via_cod））;
modelBuilder.Entity< ArCode>（）。HasKey（a => a.Code）.ToTable（ARCODS）;
modelBuilder.Entity< TermsCode>（）。Map（m => m.Requires（CodeType）
.HasValue（T）。HasColumnType（varchar）。HasMaxLength（1） .IsRequired（））
.ToTable（ARCODS）;
modelBuilder.Entity< ShipViaCode>（）。Map（m => m.Requires（CodeType）
.HasValue（S）。HasColumnType（varchar）。HasMaxLength（1） .IsRequired（））
.ToTable（ARCODS）;
base.OnModelCreating（modelBuilder）;
}
public class Invoice
{
[Column（pi_hist_hdr_invc_no），Key]
public int InvoiceNumber {get;组; }
public ShipViaCode ShipVia {get;组; }
public TermsCode条款{get;组; }
}
如果您不必插入 ARCODS 通过EF记录。尽管数据库可以允许，它不允许您插入具有相同的代码的记录。但我希望 ARCODS 的内容非常稳定，也可以填写一个脚本。
I am relatively new to the Code First approach to Entity Framework. I have used the Database First approach for a while now, but the Code First seems to be a better fit for the application I am currently developing. I am working with an existing MS SQL database, and I am not allowed to make any changes whatsoever to the database. The reason why I am using Code First is because the Fluent API allows me to dynamically assign a table name to a class.
That said, I have a predicament where I need to assign a relationship between 2 tables. One table, ArCodes, has a composite key made up of the CodeType and the Code (both are strings). The CodeType column determins the type of code and the Code column is the identifier unique to the code type.
public class ArCode {
[Column("cod_typ", Order = 0), Key]
public string CodeType { get; set; }
[Column("ar_cod", Order = 1), Key]
public string Code { get; set; }
[Column("desc")]
public string Description { get; set; }
}
The other table, Invoices, needs to have a relationship to the ArCodes table for both a "ship via" code and a "terms" code.
public class Invoice {
[Column("pi_hist_hdr_invc_no"), Key]
public int InvoiceNumber { get; set; }
[Column("shp_via_cod")]
public string ShipViaCode { get; set; }
public ArCode ShipVia { get; set; }
[Column("terms_cod")]
public string TermsCode { get; set; }
public ArCode Terms { get; set; }
}
I would like to setup the relationship for both the "ShipVia" property and the "Terms" property. However, I am not sure how to do so in regards to the CodeType portion of the composite key. For "ship via" codes the Code Type should be "S", and code "terms" codes, the code type should be "T".
I have tried the following in by DB Context, but it did not work:
protected override void OnModelCreating(DbModelBuilder modelBuilder) {
// setup the table names
modelBuilder.Entity<ArCode>().ToTable("ARCODS" + CompanyCode);
modelBuilder.Entity<Invoice>().ToTable("IHSHDR" + CompanyCode);
//
// setup the relationships
//
// 1 Invoice <--> 0-1 Ship Via AR Codes
modelBuilder.Entity<Invoice>()
.HasOptional(invoice => invoice.ShipVia)
.WithMany()
.HasForeignKey(invoice => new { TheType = "S", invoice.ShipViaCode })
;
base.OnModelCreating(modelBuilder);
}
Any help would be appreciated.
Update #1
Ok, I reduced my code to its simplest form, and I followed the solution as provided by @GertArnold.
public abstract class ArCode {
[Column("cod_typ")]
public string CodeType { get; set; }
[Column("ar_cod")]
public string Code { get; set; }
[Column("terms_desc")]
public string TermsDescription { get; set; }
[Column("terms_typ")]
public string TermsType { get; set; }
[Column("shp_via_desc")]
public string ShipViaDescription { get; set; }
[Column("tax_desc")]
public string TaxDescription { get; set; }
}
public class TermsCode : ArCode { }
public class ShipViaCode : ArCode { }
public class Invoice {
[Column("pi_hist_hdr_invc_no"), Key]
public int InvoiceNumber { get; set; }
[Column("hdr_invc_dat")]
public DateTime InvoiceDate { get; set; }
[Column("shp_via_cod")]
public string ShipViaCode { get; set; }
public ShipViaCode ShipVia { get; set; }
[Column("terms_cod")]
public string TermsCode { get; set; }
public TermsCode Terms { get; set; }
public Invoice() {
}
}
public class PbsContext : DbContext {
public DbSet<Invoice> Invoices { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder) {
modelBuilder.Entity<Invoice>().ToTable("IHSHDR");
modelBuilder.Entity<ArCode>().HasKey(r => r.Code).ToTable("ARCODS");
modelBuilder.Entity<TermsCode>().Map(m => m.Requires("CodeType")
.HasValue("T").HasColumnType("varchar").HasMaxLength(1).IsRequired())
.ToTable("ARCODS");
modelBuilder.Entity<ShipViaCode>().Map(m => m.Requires("CodeType")
.HasValue("S").HasColumnType("varchar").HasMaxLength(1).IsRequired())
.ToTable("ARCODS");
base.OnModelCreating(modelBuilder);
}
public PbsContext()
: base("name=PbsDatabase") {
}
}
However, the following code returns an error:
PbsContext context = new PbsContext();
var invoice = context.Invoices.OrderByDescending(r => r.InvoiceDate).FirstOrDefault();
error 3032: Problem in mapping fragments starting at line 28:Condition member 'ArCode.cod_typ' with a condition other than 'IsNull=False' is mapped. Either remove the condition on ArCode.cod_typ or remove it from the mapping.
If I remove the "CodeType" column from the ArCode class and change all "CodeType" references to the database column name of "cod_typ" within the OnModelCreating event, then the statement above executes without error. However, invoice.ShipVia and invoice.Terms will both be null event though there is a matching record in the database.
Update #2
public abstract class ArCode {
[Column("ar_cod")]
public string Code { get; set; }
[Column("terms_desc")]
public string TermsDescription { get; set; }
[Column("terms_typ")]
public string TermsType { get; set; }
[Column("shp_via_desc")]
public string ShipViaDescription { get; set; }
[Column("tax_desc")]
public string TaxDescription { get; set; }
}
public class TermsCode : ArCode { }
public class ShipViaCode : ArCode { }
public class Invoice {
[Column("pi_hist_hdr_invc_no"), Key]
public int InvoiceNumber { get; set; }
[Column("hdr_invc_dat")]
public DateTime InvoiceDate { get; set; }
[Column("shp_via_cod")]
public ShipViaCode ShipVia { get; set; }
[Column("terms_cod")]
public TermsCode Terms { get; set; }
public Invoice() {
}
}
public class PbsContext : DbContext {
public DbSet<Invoice> Invoices { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder) {
modelBuilder.Entity<Invoice>().ToTable("IHSHDR");
modelBuilder.Entity<ArCode>().HasKey(r => r.Code).ToTable("ARCODS");
modelBuilder.Entity<TermsCode>().Map(m => m.Requires("CodeType")
.HasValue("T").HasColumnType("varchar").HasMaxLength(1).IsRequired())
.ToTable("ARCODS");
modelBuilder.Entity<ShipViaCode>().Map(m => m.Requires("CodeType")
.HasValue("S").HasColumnType("varchar").HasMaxLength(1).IsRequired())
.ToTable("ARCODS");
base.OnModelCreating(modelBuilder);
}
public PbsContext()
: base("name=PbsDatabase") {
}
}
Now, the following code returns an error:
PbsContext context = new PbsContext();
var invoice = context.Invoices.OrderByDescending(r => r.InvoiceDate).FirstOrDefault();
EntityCommandExecutionException - Invalid column name 'ShipVia_Code'. Invalid column name 'Terms_Code'.
What you want is impossible for EF. ArCode has a composite key, so any association to it will have to use two Properties. That means that in Invoice you'd need four properties (two pairs) to refer to the two ArCode objects. But two of these properties (those for CodeType) are not backed up by columns in the database, so EF can not map them.
But... there is a way that may help you out. You could create two derived classes from ArCode and let Invoice refer to those by single-property associations. But then you have to divert from the model as such and fool EF a bit by defining a single key:
public abstract class ArCode { ... } // abstract!
public class TermsCode : ArCode { }
public class ShipViaCode : ArCode { }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Invoice>().ToTable("IHSHDR");
modelBuilder.Entity<Invoice>().HasOptional(i => i.Terms).WithOptionalDependent().Map(m => m.MapKey("terms_cod"));
modelBuilder.Entity<Invoice>().HasOptional(i => i.ShipVia).WithOptionalDependent().Map(m => m.MapKey("shp_via_cod"));
modelBuilder.Entity<ArCode>().HasKey(a => a.Code).ToTable("ARCODS");
modelBuilder.Entity<TermsCode>().Map(m => m.Requires("CodeType")
.HasValue("T").HasColumnType("varchar").HasMaxLength(1).IsRequired())
.ToTable("ARCODS");
modelBuilder.Entity<ShipViaCode>().Map(m => m.Requires("CodeType")
.HasValue("S").HasColumnType("varchar").HasMaxLength(1).IsRequired())
.ToTable("ARCODS");
base.OnModelCreating(modelBuilder);
}
public class Invoice
{
[Column("pi_hist_hdr_invc_no"), Key]
public int InvoiceNumber { get; set; }
public ShipViaCode ShipVia { get; set; }
public TermsCode Terms { get; set; }
}
This may work for you if you don't have to insert ARCODS records through EF. It won't allow you to insert records with identical Codes, although the database would allow it. But I expect the content of ARCODS to be pretty stable and maybe it is enough to fill it with a script.
这篇关于文字或常数作为EF代码中的复合键的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
