CODEWITHSUNDEEP
c#entity-framework
SeanW
SeanW

Reputation: 2355

Trying to fix bad db design with Entity Framework, Foreign Key Data Annotations specifically

I'm dealing with a bit of a nightmare at the moment. I'm having to do some database first development where I can't change the database (too many other apps rely on the database). The basic idea is that I have two tables that are related, but do not have an actual FK relationship in SQL.

I'll jump right in and show you some tables:

tblConnectorTypes:
  ConnectID (primary key, Int16)
  ConnectorType (String)

tblEquipmentPorts:
  EquipPortID (primary key, Int32)
  ConnectorType (Byte)
  ...other columns left out for brevity...

That is a rough outline of what the tables look like (with their SQL types listed in parens). tblEquipmentPorts.ConnectorType maps to tblConnectorTypes.ConnectID -- ugly right? Ugh.

As you can see, not only do the column names not match up, but the column types are different. I've been trying to find some form of DataAnnotation I can use to make this work, but I think the different column types are going to be a deal killer. Here's a sample of the two POCO's I've created to represent the tables above:

[Table("tblEquipmentPorts")]
public class EquipmentPort {

    [Key]
    public int EquipPortID { get; set; }

    [Column("ConnectorType")]
    public byte ConnectorTypeID { get; set; }

    [ForeignKey("ConnectorTypeID")]
    public virtual ConnectorType ConnectorType { get; set; }

    // ...other columns left out for brevity...
}

[Table("tblConnectorTypes")]
public class ConnectorType {

    [Key]
    [Column("ConnectID")]
    public Int16 ConnectorTypeID { get; set; }

    [Column("ConnectorType")]
    public string Name { get; set; }
}

As you can see, I've tried to clean up the column names with DataAnnotation, but I'm not sure if I can then create the Foreign Key annotations using the annotated names. However, building and running produces the following error about the contraints not being the same type:

One or more validation errors were detected during model generation:

System.Data.Edm.EdmAssociationConstraint: : The types of all properties in 
the Dependent Role of a referential constraint must be the same as the 
corresponding property types in the Principal Role. The type of property
'ConnectorTypeID' on entity 'EquipmentPort' does not match the type of 
property 'ConnectorTypeID' on entity 'ConnectorType' in the referential 
constraint 'EquipmentPort_ConnectorType'.

Is it possible to coerce some sort of conversion between the column types so that I can create this relationship?

I wish I could change the database itself, but like I mentioned above there are too many other applications in the company that rely on this database as is (that I cannot update). Joys of working in an enterprise, right?

Thanks for reading!

Upvotes: 4

Views: 4220

Answers (2)

Matt Smucker
Matt Smucker

Reputation: 5234

You should be able to alter tblEquipmentPorts and add a integer type ConnectID (with or without a foreign key constraint) without affecting much in the database. It's much better to clean up a mess then to cover it up, if you're able to.

Actually, wrap tblEquipmentPorts in a view and have a ConnectID calculated field converting ConnectorType to an INT. You can then use the view in your application instead of the table. This should not break anything (though it might limit your application), where altering the table could break code that has bad practices like inserting without a column lis

Upvotes: 1

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364259

Trying to fix bad db design with Entity Framework, Foreign Key Data Annotations specifically

You will not fix bad database design with EF because EF is only "entry level" ORM and it is heavily dependent on correct database design - especially with fluent API or data annotations. It also moves a lot of relational concepts into object world (that is the reason why I use "entry level"). Foreign keys are just one example of relational concepts pushed to object world.

Your options are

  • fixing database or introducing view as mentioned by @msmucker0527 in comment (but make sure that view is updatable if you also want to change data).
  • (untested) Try to change type of the property in either entity to have matching types.

Upvotes: 2

Related Questions