Can't create 1: 1 relationship using EF due to difference in primary key types
We are using EF in a project and need to create a 1: 1 relationship between two tables in the database ( FundMaterList and FundMeta strong>).
The first one ( FundMasterList ) was created using the first db approach and it has the following column as its primary key:
[PerformanceID] [char](10) NOT NULL
and this is the main limitation:
CONSTRAINT [PK_FundMasterList] PRIMARY KEY CLUSTERED
([PerformanceID] ASC)
WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
The second one ( FundMeta strong>) was created using a codes-based approach and has the following declaration:
public class FundMeta
{
[Key]
[StringLength(10)]
[Column("PerformanceID")]
public string InstId { get; set; }
...
}
and the next mapping
modelBuilder.Entity<FundMetadataEntity>()
.Property(e => e.InstId)
.IsFixedLength()
.IsUnicode(false);
modelBuilder.Entity<FundEntity>()
.HasOptional(e => e.FundMeta)
.WithRequired(e => e.Fund)
.WillCascadeOnDelete();
FundMeta and the Fund are declared virtual. When I run a database update, I get the following error message:
The column "dbo.FundMasterList.PerformanceID" is not the same data type as the reference column "FundMetadats.PerformanceID" in the foreign key "FK_dbo.FundMetas_dbo.FundMasterList_PerformanceID". Failed to create constraint. See previous errors.
When I delete create relationship everithing works fine and the column in FundMeta table is the same type as in FundMasterList. It seems that creating a relationship is trying to "overwrite" this constraint:
modelBuilder.Entity<FundMetadataEntity>()
.Property(e => e.InstId)
.IsFixedLength()
.IsUnicode(false);
EDIT:
When I generated the migration script I noticed the following changes:
Without a relationship, this looks fine:
AlterColumn("dbo.FundMeta", "PerformanceID",
c => c.String(nullable: false, maxLength: 10, fixedLength: true, unicode: false));
After adding relationships:
DropPrimaryKey("dbo.FundMeta");
AlterColumn("dbo.FundMeta", "PerformanceID", c => c.String(nullable: false, maxLength: 10));
AddPrimaryKey("dbo.FundMeta", "PerformanceID");
This means that the limit has fixedLength: true, unicode: false
not been lost.
When adding this limitation manually during the migration script, everything works fine. Are there other solutions possible?
source to share
Steve is right.
Without any other information, the string results in nvarchar.
So you are trying to create a constraint between char [10] and nvarchar [10].
You must enter your PC type using HasColumnType("char")
as directed by Steve.
NotUnicode might not be enough as you get varchar(10)
source to share