CODEWITHSUNDEEP
entity-frameworkentity-framework-6
Hooman Bahreini
Hooman Bahreini

Reputation: 15559

Cannot add or update a child row when adding a new row

I have seen this answer and am using the solution that it is suggesting but still getting the same error.

I have an Agency table with a foreign key constraint to User Table, this the Agency table:

CREATE TABLE agency (
    AgencyId                BIGINT          NOT NULL    AUTO_INCREMENT,
    UserId                  BIGINT          NOT NULL,
    AgencyName              VARCHAR(150)    NOT NULL,

    CONSTRAINT PK_Agency PRIMARY KEY(AgencyId),
    CONSTRAINT FK_Agency_User FOREIGN KEY (UserId) REFERENCES user (UserId) ON DELETE RESTRICT ON UPDATE RESTRICT
);

This is my Agency entity:

[Table("agency")]
public class Agency
{
    public long AgencyId { get; set; }

    [Required]
    public long UserId { get; set; }

    [ForeignKey("UserId")]
    public User User { get; set; } // <-- user Entity

    [Required]
    [StringLength(GlobalConstants.MaxLengthForLongName)]
    public string AgencyName { get; set; }
}

Now I want to add a new Agency for an existing User:

public void AddOrUpdateAsAdminUser(Agency agency)
{
    if (agency.UserContact.UserId <= 0)
    {
        throw new Exception($"User: {agency.User.Email} does not exists");
    }

    if (agency.AgencyId > 0)
    {
        // update existing agency
        _context.Agency.Attach(agency);
        _context.Entry(agency).State = EntityState.Modified;
        _context.Entry(agency).Property(x => x.UserId).IsModified = false;
        _context.Entry(agency.User).State = EntityState.Detached; // <-- don't update User
    }
    else
    {
         // add new agency: exception is thrown here
        _context.Agency.Add(agency);
        _context.Entry(agency.User).State = EntityState.Detached; // <-- Don't update User
    }

    _context.SaveChanges();
}

_context.SaveChanges(); line throws the following exception:

"Cannot add or update a child row: a foreign key constraint fails (\"dbName\".\"agency\", CONSTRAINT \"FK_Agency_User\" FOREIGN KEY (\"UserId\") REFERENCES \"user\" (\"UserId\"))"

System.Exception {MySql.Data.MySqlClient.MySqlException}

Upvotes: 0

Views: 262

Answers (2)

Hooman Bahreini
Hooman Bahreini

Reputation: 15559

The problem was with my mapping logic, when mapping AgencyViewModel to Agency, I was only mapping UserViewModel to User... which means UserId was 0 (not mapped) when I wanted to save the agency.

[Table("agency")]
public class Agency
{
    public long AgencyId { get; set; }

    [Required]
    public long UserId { get; set; }  // <-- I was NOT mapping UserId

    [ForeignKey("UserId")]
    public User User { get; set; } // <-- I was mapping User

    [Required]
    [StringLength(GlobalConstants.MaxLengthForLongName)]
    public string AgencyName { get; set; }
}

Upvotes: 0

Thaks
Thaks

Reputation: 720

You're getting this error because you're trying to add/update a row to agency that does not have a valid value for the UserID field based on the values currently stored in table1.

Upvotes: 1

Related Questions