Literal or permanent as part of a composite key in EF code

Question

I am relatively new to the first Code for Entity Framework approach. I have long used the Database First approach, but Code First seems to be better suited for the application I'm currently developing. I work with an existing MS SQL database and I am not allowed to make any changes to the database. The reason I use Code First is because the Fluent API allows me to dynamically assign a table name to a class.

However, I have a predicament when I need to assign a relationship between two tables. One table, ArCodes, has a composite key consisting of CodeType and Code (both are rows). The CodeType column identifies the code type, and the Code column is an 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 Invoice table should refer to the ArCodes table for ship through code and terms.

 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 establish relationships for both the ShipVia property and the Conditions property. However, I'm not sure how to do this in relation to the CodeType part of the composite key. For “ship” codes, the code type must be “S” and the code “terms” with codes, the code type must be “T”.

I tried using DB Context, but this 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

Well, I reduced my code to its simplest form, and I executed the solution 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: a problem with displaying fragments starting from line 28: a condition member "ArCode.cod_typ" with a condition other than "IsNull = False" is displayed. 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 the "CodeType" links to the database column name "cod_typ" in the OnModelCreating event, then the above expression is executed without errors. However, invoice.ShipVia and invoice.Terms will be null, although there is a corresponding entry 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'.

Answer 1

What you want is not possible for EF. ArCode has a composite key, so any association with it will have to use two properties. This means that in Invoice you will need four properties (two pairs) to refer to two ArCode . But two of these properties (for CodeType ) are not supported by columns in the database, so EF cannot display them.

But ... there is a way that can help you. You can create two derived classes from ArCode and Invoice designate them by associations with one property. But then you need to distract from the model as such and trick EF a bit by specifying one 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 do not need to embed ARCODS records through EF. This will not allow you to insert records with identical Code s, although this will allow the database. But I expect that the contents of ARCODS will be fairly stable, and perhaps enough to fill its script.