A dependent property in a ReferentialConstraint is mapped to a store-generated column

Question

I get this error when writing to the database:

A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'PaymentId'.

public bool PayForItem(int terminalId, double paymentAmount, 
      eNums.MasterCategoryEnum  mastercategoryEnum, int CategoryId, int CategoryItemId)
    {

        using (var dbEntities = new DatabaseAccess.Schema.EntityModel())
        {
            int pinnumber = 0;
            long pinid = 1; //getPinId(terminalId,ref pinnumber) ;
            var payment = new DatabaseAccess.Schema.Payment();
            payment.CategoryId = CategoryId;
            payment.ItemCategoryId = CategoryItemId;
            payment.PaymentAmount = (decimal)paymentAmount;
            payment.TerminalId = terminalId;
            payment.PinId = pinid;

            payment.HSBCResponseCode = "";
            payment.DateActivated = DateTime.Now;
            payment.PaymentString = "Payment";
            payment.PromotionalOfferId = 1;
            payment.PaymentStatusId = (int)eNums.PaymentStatus.Paid;

            //payment.PaymentId = 1;

            dbEntities.AddToPayments(payment);
            dbEntities.SaveChanges();
        }
        return true;
    }

The schema is:

Weather

Answer 1

Is it possible that you defined a bad column relation between your tables?

In my case, I had different columns and one was set as autonumeric.

Copyhold

I mistakenly made one of my foreign keys an Identity (auto increment). This is the error I got. – Partlow

Doh! I had left the foreign key part of the relationship as the default given by SQL Server 2008 Management Studio, which was the primary key fields of the child table, not the column I had created to contain the foreign key value. – Cloven

If you inspect the exception in the Quick Watch window (i.e. (e as System.Data.Entity.Infrastructure.DbUpdateException).Entries), you can see which table contains the primary key being referenced. – Kella

Wouldn't it be cool if the EF error messages just stated what the problem was instead of spitting out gobbledy-gook? – Tengdin

I used this query to view all the relationships in one view #8094656 – Enabling

Duh! I had a one to one relationship and the tables wasn't accepting nulls. – Amputate

In my recent instance of this, the identity column in Attachment table was trying to FK to the AttachmentContent table that just has the binary content to isolate it from other attachment metadata. AttachmentID is the PK for both tables, but the FK relationship was simply backwards. Upon reversing it, it was fine (where the dependency is on the Identity column in the actual parent table, not dependent on the child table). – Casanova

Answer 2

This error says that you are using unsupported relation or you have error in your mapping. Your code is most probably absolutely unrelated to the error.

The error means that you have some relation between entities where foreign key property in dependent entity is defined as store generated. Store generated properties are filled in the database. EF doesn't support store generated properties as foreign keys (as well as computed properties in primary keys).

Premonish

i can add the row in sql server with the same info. when you say Store Generated, can you give an example ? – Weather

EF is not SQL Server. It has its own limitation. Simply find where you use any DB generated FK property called PaymentID and deal with it. – Premonish

ok we have a paymenthistory table that has paymentId as a foreign key, do i need to add a row in there ? – Weather

It is not about adding row but about definition of the column. How do you set that column? – Premonish

just clicked the relationship model in visual studio and im getting Name 'Payment' cannot be used in type 'Payment'. member names cannot be the same as their enclosing type. Any ideas – Weather

It says that property cannot have same name as a type where it is defined. It looks like your model is invalid. – Premonish

well as it is a forign table, i dont add a row at this stage. For items in the current table that are foreign, i set the values manually. ie payments.promotionalCodeId = 1; – Weather

i used the model creator in visual studio 2010 – Weather

But if you get error you mentioned before your project should not even compile because that is not error of EDMX but error of the code. – Premonish

lol, i have removed the reference and now the model works. is thre anything else i should look for. – Weather

fixed it, my database design was incorrect, and referencing my params better now payment.Category = dbEntities.Categories.Where(catid => catid.CategoryId == categoryId ).First(); cheers :) – Weather

Answer 3

I had the same problem. Based on the answers provided here I was able to track it and solve it, but I had a strange issue described below - it might help somebody in the future.

On my dependent tables, the foreign Key columns have been set to StoreGeneratedPattern="Identity". I had to change it to "None". Unfortunately, doing so inside designer didn't work at all.

I looked in the designer-generated XML (SSDL) and these properties were still there so I removed them manually. I also had to fix the columns on the database (remove the Identity(1,1) from CREATE TABLE SQL)

After that, the problem went away.

Walkthrough

Thanks for this tip. Changing the field in the designer from Identity to None changed this in one place in the EDMX, but not in the other place, so I still got the error until I edited the EDMX file myself. Unfortunately EntityFramework then went mad and tried to re-insert related entities in other tables, but still, it was a help in bypassing that error message. – Blas

Answer 4

I had the same problem and after some digging in table design in sql server , I found that mistakenly i set table's primary key also as foreign key.

In this image you can see that JobID is table's primary key but also mistakenly foreign key.

Cancel

It's fine for a column to be both a PK and an FK, but it can introduce unintended issues if that is not intentional. If Job is referred to by something else, then the PK table of the relationship needs to be Job and it's a problem for the PK to also be FK, as we see here. If Job were a child- or sub-table of something else, then the PK + FK pattern would be correct. So if you had say JobCertificate as a table, then it would probably do that, and be dependent on Job, and that's fine because Job is the parent and JobCertificate is the child and does not have Identity Specification on JobID. – Casanova

Answer 5

My problem was caused by redundant defining of the Primary key in the configuration.

this
   .Property(p => p.Id)
   .HasColumnName(@"id")
   .IsRequired()
   .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity) // this is redundant when you want to configure a One-to-Zero-or-One relationship
   .HasColumnType("int");

Remove this line

.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)

---

Example http://www.entityframeworktutorial.net/code-first/configure-one-to-one-relationship-in-code-first.aspx

This is enough to define the relationship

// Configure Student & StudentAddress entity
modelBuilder.Entity<Student>()
            .HasOptional(s => s.Address) // Mark Address property optional in Student entity
            .WithRequired(ad => ad.Student); // mark Student property as required in StudentAddress entity. Cannot save StudentAddress without Student

Disini

Answer 6

Re-check the relationship between Payment and the other tables/entities. Including the ones that shouldn't contain PaymentId because that's where the problem is most likely hiding.

When creating foreign keys in SQL Server Management Studio, the primary key is defaulted, and this default is reverted when the parent table is changed, so be careful to change values in the correct order in the "Tables and Columns" window.

Also, after you've fixed the problematic relationship, there's a good chance that a simple "Refresh" on the model won't correctly remove the erronous relationship from the model and you'll get the same error even after the "fix", so do this yourself in the model before performing a refresh. (I found this out the hard way.)

Ignorance

Answer 7

If you have checked your relationships and are good there.

Delete the table in the edmx and then update from database. This will save you doing the update manually.

Graecoroman

Really thank for your advice, I spent 1 hour to validate my database, but after removed then updated, everything is ok. – Madalene

Answer 8

For me it was a wrongly placed foreign key in the table but even after altering the table to fix it, it was still not working. You need to update the EDMX files (and not enough to "refresh" the table from the model, you need to remove and add the table again in the model).

Emigration

Answer 9

In addition to the accepted answer, if you are using EF Reverse POCO generator or some other tool that generates your POCO's, make sure you regenerate them!

Ytterbia

Never ever forget to re-run your Custom T4 Tool (holding the POCOs) after modifying your external DB-first EF model generator (holding the contextes)... EVER! XD (could as well have gone crazy -.-') – Miscreant

Answer 10

In my case the problem was caused by having a two-way 1-1 relationship:

class Foo{
[Key]
Id
[ForeignKey]
BarId
...
}
class Bar{
[Key]
Id
[ForeignKey]
FooId
...
}

I had to simply remove one of the two foreign keys (not necessary anyway).

Shcherbakov

Answer 11

In my case Id field wich FK just in Entity Framework the propierty "StoreGeneratedPattern" was set "Itentity" instead of "None"

Pals

Answer 12

In my case it was simply that I did not have permissions set properly on the database. I had read only set and Entity framework was giving me a ReferentialConstraint error which threw me off. Added additional write permissions and all was well.

Forwardlooking

Answer 13

In my case, I had a Database Generated property, and a ForeignKey navigation property set up to reference a 1 to 1 related table.

This wasn't something I could remove, I needed to be able to both set the primary key of the entity to be Database Generated AND I needed to be able to reference the 1 to 1 table as a navigation property.

Not sure if this is the same for others, but this problem was only showing up when creating a new entity, reading or editing existing entities did not exhibit the issue, so I got around the issue by creating an inherited version of my Context and using the Fluent method to switch off the navigation property when creating.

So, my original entity looked like this:

public partial class MyEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid id{ get; set; }


    // Navigation
    [ForeignKey("id")]
    public PathEntity Path { get; set; }
}

So I made a special inherited context that looked like this:

    private class _navPropInhibitingContext : EF.ApplicationDBContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<MyEntity>()
                .Ignore(e => e.Path);

        }
    }

and then changed the code that created the new entity to make user of the new context type

    using (var specialContext = new _navPropInhibitingContext())
    {
        var dbModel = new MyEntity() 
        {
            ...
        };

        specialContext.MyEntity.Add(dbModel);
        await specialContext.SaveChangesAsync();
    }

Hope this helps somebody

Chokeberry

Answer 14

I have the same issue.

Here is my case, if you are adding a new record and has a primary key but is not auto-incremented, this will trigger an error.

I thought first that it will automatically generate the key for me so I leave the Id as blank.

Example:

Customer cust = new Customer();
//cust.Id - I left it blank
db.Customer.Add(cust);
db.SaveChanges();

But upon quick investigation, I forgot to set it's Identity to true and that would trigger an error once you do SaveChanges on your DbContext.

So make sure if your Identity is true or not.

Whitten

Answer 15

In my case I was passing auto generated primary key of the same table in foreign key column so entity frame work is throwing an error that it can not set a value of that column which is not generated yet as we can only get autogenerated value after save change

Here BonusRequestId is my primary key which I was doing a mistake

Fawnia