CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
Natasha
Natasha

Reputation: 973

Can not create 1:1 relation using EF because of difference in primary key data types

We are using EF on the project and need to create 1:1 relation between 2 tables in the database (FundMaterList and FundMeta).

The first (FundMasterList) was created using db first approach and it has the following column as primary key :

[PerformanceID] [char](10) NOT NULL

and this primary constraint: 

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]

Second (FundMeta) was created using code-first approach and has the following declaration: 

public class FundMeta
{
    [Key]
    [StringLength(10)]
    [Column("PerformanceID")]
    public string InstId { get; set; }
    ...
}

and the following 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 Fund are declared as virtuals. When I am running database update I get the following error message:

Column 'dbo.FundMasterList.PerformanceID' is not the same data type as referencing column 'FundMetadats.PerformanceID' in foreign key 'FK_dbo.FundMetas_dbo.FundMasterList_PerformanceID'. Could not create constraint. See previous errors.

When I remove creating relations everithing works fine and column in the table FundMeta has the same type as in FundMasterList. It seems that creating relation try to "overwrite" this constraint: 

modelBuilder.Entity<FundMetadataEntity>()
                    .Property(e => e.InstId)
                    .IsFixedLength()
                    .IsUnicode(false);

EDIT:

When I generated migration script I've noticed the following changes:

Without relation it looks OK: 

AlterColumn("dbo.FundMeta", "PerformanceID",
 c => c.String(nullable: false, maxLength: 10, fixedLength: true, unicode: false));

After the relationship added: 

 DropPrimaryKey("dbo.FundMeta");
 AlterColumn("dbo.FundMeta", "PerformanceID", c => c.String(nullable: false, maxLength: 10));
 AddPrimaryKey("dbo.FundMeta", "PerformanceID");

It means that limitation on fixedLength: true, unicode: false was lost.

When add this limitation manually in migration script everything works fine. Any other solutions possible?

Upvotes: 3

Views: 77

Answers (1)

tschmit007
tschmit007

Reputation: 7800

Steve is true.

With no other information String leads to nvarchar.

So you are trying to create a constraint between char[10] and nvarchar[10].

You have to force the type of your PK by using HasColumnType("char") as stated by Steve.

NotUnicode may not be enough as you will get varchar(10)

Upvotes: 1

Related Questions