How to use a composite primary key with Entity Framework from a foreign key in C#-CodePudding

I’ve got a large problem with an existing database and the primary key / foreign keys using Entity Framework.

It's an existing database and it is not allowed to change the database, at least not the existing keys because they are used by other programs. To explain what is going on with the keys I added a partial diagram.

The parameterCode and Type are string index keys, so the are nog as normal Id's

The database consists of 4 tables:

Parameter

This has the following primary key columns (which form a composite key):

ParameterCode
Type => this key is part of the parameterType

ParameterType

This has the following primary key:

ParameterTypeCode => connected to type in Parameter.

ParameterDocumentType

PrimaryKey: ParameterDocumentId

This has the following foreign keys

ParamaterCode => connected to Parameter
Type => connected to parameter
ParameterDocument => this one works fine, so I keep it out

I created configurations and models to connect all the tables, however the Parameter table connecting to the parameterType does not accept the configuration. When I declare both keys as primary keys.

When connecting the parametertype foreign key which is also part of the primary key I get the following error:

System.InvalidOperationException: 'The property or navigation 'ParameterType' cannot be added to the entity type 'ParameterModel' because a property or navigation with the same name already exists on entity type 'ParameterModel'.'

I stripped the code from all other values to make it smaller.

[![// configurations
public class ParameterConfiguration : IEntityTypeConfiguration<ParameterModel>
{
        public void Configure(EntityTypeBuilder<ParameterModel> builder)
        {
            builder.ToTable("Parameter", table => table.ExcludeFromMigrations());

            builder.HasKey(p => new { p.ParameterCode, p.ParameterType });

            builder.Property(p => p.ParameterCode)
                .HasColumnName("ParameterCode")
                .HasColumnType("varchar(25)")
                .IsRequired();

            builder.Property(p => p.Description)
                .HasColumnName("Description")
                .HasColumnType("varchar(255)");
                .IsRequired();

            builder.HasOne(x => x.ParameterType) // error on creating this key
                .WithMany(x => x.Parameters)
                .HasForeignKey(x => x.Type)
                .IsRequired();
        }
}

// configurations ParameterTypeConfiguration
public class ParameterTypeConfiguration : IEntityTypeConfiguration<ParameterTypeModel>
{
        public void Configure(EntityTypeBuilder<ParameterTypeModel> builder)
        {
            builder.ToTable("ParameterType", table => table.ExcludeFromMigrations());

            builder.HasKey(p => p.ParameterTypeCode);

            builder.Property(p => p.ParameterTypeCode)
                .HasColumnName("ParameterTypeCode")
                .HasColumnType("varchar(50)")
                .IsRequired();

        }
}

// configurations
public void Configure(EntityTypeBuilder<ParameterDocumentTypeModel> builder)
{
            builder.ToTable("ParameterDocumentType", table => table.ExcludeFromMigrations());

            builder.HasKey(p => p.ParameterDocumentTypeId);

            builder.Property(p => p.ParameterDocumentTypeId)
                .HasColumnName("ParameterDocumentTypeID")
                .HasColumnType("bigint")
                .UseIdentityColumn()
                .ValueGeneratedOnAdd()
                .IsRequired();

            builder.Property(p => p.ParameterCode)
                .HasColumnName("Parameter_Code")
                .HasColumnType("varchar(50)")
                .IsRequired();

            builder.Property(p => p.Type)
                .HasColumnName("Type")
                .HasColumnType("varchar(25)")
                .IsRequired();

            builder.HasOne(p => p.Parameter)
                .WithMany(p => p.ParameterDocumentTypes)
                .HasForeignKey(p => p.ParameterCode)
                .IsRequired(false);
        }
}

// models
public class ParameterDocumentTypeModel 
{
        public long ParameterDocumentTypeId { get; set; }
        public long? DocumentTypeId { get; set; }
        
        // used for the connection with parameter
        public virtual ParameterModel Parameter { get; set; }

        public string ParameterTypeCode
        {
            get => Parameter?.Type;
        }
}

public class ParameterModel 
{
        public string ParameterCode { get; set; }
        public string Type { get; set; }

        public virtual ParameterTypeModel ParameterType { get; set; }
        // used to connect to parameterType
        public virtual ICollection<ParameterDocumentTypeModel> ParameterDocumentTypes { get; set; }
}

public class ParameterTypeModel 
{
        public string ParameterTypeCode { get; set; }
        public string Description { get; set; }

        // used to connect to parameter
        public ICollection<ParameterModel> Parameters { get; set; }
}

CodePudding user response：

property or navigation with the same name already exists on entity type 'ParameterModel'

Because you attempted to configure ParameterType as a key property, which would fail later anyway as only scalars can be key properties.

This

  builder.HasKey(p => new { p.ParameterCode, p.ParameterType });

should be

  builder.HasKey(p => new { p.ParameterCode, p.Type });

CodePudding user response：

After correcting: builder.HasKey(p => new { p.ParameterCode, p.ParameterType });

Many thanks for David Browne

I also needed to do the same for the foreignkey.