Exception "A dependent property in a ReferentialConstraint is mapped to a store-generated column" when making insertion in a table with a FK which has a UK constraint

[pedrompc]

I have a table "RecipientListImport" with a FK to "RecipientList" which has a UK constraint on it. This creates a 0..1 relationship between the two.

This is the definition for my "RecipientListImport" table:

CREATE TABLE [core].[RecipientListImport](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[IdRecipientList] [int] NOT NULL,
	[RemoveDuplicates] [bit] NOT NULL,
	[IdValidationCountry] [smallint] NULL,
	[CreateDatetime] [datetime] NOT NULL,
	[LastUpdateDatetime] [datetime] NULL,
 CONSTRAINT [PK_core_RecipientListImport] PRIMARY KEY CLUSTERED ([Id]),
 CONSTRAINT [UQ_core_RecipientListImport_IdRecipientList] UNIQUE NONCLUSTERED ([IdRecipientList] ASC)

ALTER TABLE [core].[RecipientListImport]  WITH CHECK ADD  CONSTRAINT [FK_core_RecipientListImport_core_RecipientList] FOREIGN KEY([IdRecipientList])
REFERENCES [core].[RecipientList] ([Id])
ON DELETE CASCADE

ALTER TABLE [core].[RecipientListImport] CHECK CONSTRAINT [FK_core_RecipientListImport_core_RecipientList]

This is the configuration class created for this table:

public partial class RecipientListImportConfiguration : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<RecipientListImport>
    {
        public RecipientListImportConfiguration()
            : this("core")
        {
        }

        public RecipientListImportConfiguration(string schema)
        {
            ToTable("RecipientListImport", schema);
            HasKey(x => x.Id);

            Property(x => x.Id).HasColumnName(@"Id").HasColumnType("int").IsRequired().HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
            Property(x => x.IdRecipientList).HasColumnName(@"IdRecipientList").HasColumnType("int").IsRequired();
            Property(x => x.RemoveDuplicates).HasColumnName(@"RemoveDuplicates").HasColumnType("bit").IsRequired();
            Property(x => x.IdValidationCountry).HasColumnName(@"IdValidationCountry").HasColumnType("smallint").IsOptional();
            Property(x => x.CreateDatetime).HasColumnName(@"CreateDatetime").HasColumnType("datetime").IsRequired().HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Computed);
            Property(x => x.LastUpdateDatetime).HasColumnName(@"LastUpdateDatetime").HasColumnType("datetime").IsOptional();

            // Foreign keys
            HasRequired(a => a.RecipientList).WithOptional(b => b.RecipientListImport); // FK_core_RecipientListImport_core_RecipientList
            InitializePartial();
        }
        partial void InitializePartial();
    }

I can't find any problem with this configuration, however, whenever i try to make an insertion the RecipientListImport table, i get the exception "{"A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'Id'."}".
I'm not sure if the 'Id' column the exception is warning me about is the one on the "RecipientList" table, which is indeed an identity column, but I don't see why that should be a problem. The "RecipientListImport" table, which has the FK, also has an "Id" column, but that one has no other FK reference to it, it's just an identity column.

If I remove the UK constraint on the table, then the code for the foreign key becomes:

HasRequired(a => a.RecipientList).WithMany(b => b.RecipientListImports).HasForeignKey(c => c.IdRecipientList); // FK_core_RecipientListImport_core_RecipientList

With this configuration the insertion is successful. I would appreciate any insight as to why this insertion fails with the presence of the unique key, as the 0..1 relationship created in the Configuration class looks right to me.

Thank you.

[sjh37]

The mapping is correct, so it sounds like you are not quite using EF correctly.

1. You first need to load in the relevant RecipientList item into a variable, say called rl.
2. Create a new RecipientListImport item, on a variable, called rli, and set rli.RecipientList = rl;
3. Add rli to your database. _db.RecipientListImport.Add(rli);
4. The perform a _db.SaveChanges();

Hopefully, that should be all you need.

[pedrompc]

Thank you for your response. Unfortunately, it still doesn't work. Here's how I'm making the insertion:

var list = Context.RecipientLists.FirstOrDefault(r => r.Id == recipientListId);
var import = new RecipientListImport();
import.RecipientList = list;
Context.RecipientListImports.Add(import);
Context.SaveChanges(); // DbUpdateException -> {"A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'Id'."}

Before your suggestion, I was setting the foreign key property with the Id directly, instead of fetching the entity beforehand, but it doesn't seem to make a difference. I'm getting the same error for other entities in my codebase which also have this FK/UK combination. It's odd that if I remove the UK from the table, the relationship becomes 0..* and the insertion works. Since the generated configuration seems correct, I also suspected that I could be using EF incorrectly, but I'm trying to migrate from an EDMX solution, and this is code that's always been working.

[sjh37]

Hi @pedrompc That EF code you have above looks spot on. I'd just change FirstOrDefault(r => r.Id == recipientListId); to be Find(recipientListId); as it's more efficient and doesn't hit the database if that record is already in the context. See no.3 in this blog post.

I can't really suggest anything further as everything else looks ok. The only thing that looks odd is the CreateDatetime column as this has a DatabaseGeneratedOption.Computed option set on it, and I can't see that in the CREATE TABLE SQL above. Perhaps there is more to that table than you know about.

[pedrompc]

Hello, again thanks for the response. Looking at the code a little bit better I think I understand why it's not working. I think it's the because according to the table definition, the foreign key from RecipientListImport to RecipientList is not the primary key, but rather the "IdRecipientList" column. But EF thinks the foreign key is the primary key "Id" column, because in the relationship definition we're not indicating the foreign key should be on the column "IdRecipientList". So when EF tries to insert the row, it'll consider the column "Id" as being the foreign key rather than IdRecipientList, and that's why i'm getting the "{"A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'Id'."}" exception, because the "Id" column is indeed store-generated.

HasRequired(a => a.RecipientList).WithOptional(b => b.RecipientListImport);

When I change the relationship to 1..* I'm able to specify that the foreign key is "IdRecipientList", so the insert works:

HasRequired(a => a.RecipientList).WithMany(b => b.RecipientListImports).HasForeignKey(c => c.IdRecipientList);

I'd like to know if you think my reading is correct, and in that case, how would I be able to either:

1. Override the Reverse Poco Generator behaviour of generating a 0..1 relationship when there's a FK which is also a UK, generating a 1..* instead
2. Be able to indicate that the foreign key in a 0..1 relationship is a column rather than the primary key one

For the time being, i think to get over this problem, I could define another configuration partial class for RecipientListImport and override the foreign key definition.

Thank you

[sjh37]

Hi @pedrompc Are you using v2 of the generator? I ask because I ran into this very scenario when developing v3 and it should be covered. If you are using v3, then I've got an enhancement to make! The latest v3 code is available at DropBox.

[pedrompc]

Hello, I tried it with v3, but I'm getting the same behaviour. The same relationship is generated in the Configuration class, and I didn't find any setting in the .tt file to override this behaviour. Let me know if there are any news regarding this issue. Thank you

[sjh37]

#545 May, just may have fixed this for you as it indicates alternate primary keys through the use of unique indexes.

Copy the latest EF.Reverse.POCO.v3.ttinclude file into your project and let me know if this helps. Probably not, but worth a try.

[Surya-1732]

Hi @pedrompc I have gone threw all u r conversations; I am facing Exactly the same thing ;is there anything how to do that; or continue to configure them as one to many?? thanks

[sjh37]

@Surya-1732 are you using .Net 4.x or .NetCore? What is your database schema for both tables?

[sjh37]

I have double-checked the generated entity framework code, and it's correct, both with and without the Unique Key definition resulting in 1..1 and 1..*. Therefore I will close this case. However, if you have any problems, please leave a comment, and I will see it.