MVC Delete record with multiple required foreign key restraint

Question

Deleting from a join-payload table in MVC, is no fun at all. Similar questions are related to a single foreign key and my situation is unique enough that I warranted it a question for itself (if not, please point me to a reference)

I've got this model (which acts as my joint table with payload - it acts as a join table between ApplicationUser and Car which are both data models)

public class UserHasCar
{
    // Foreign keys
    [Key, Column(Order = 0)]
    public string ApplicationUserId { get; set; }

    [Key, Column(Order = 1)]
    public int CarId { get; set; }

    // Navigation properties
    [Required]
    public virtual ApplicationUser ApplicationUser { get; set; }

    [Required]
    public virtual Car Car { get; set; }

    // Additional fields
    public int YearsDriven { get; set; }

}

public class Car
{
    public int ID { get; set; }

    public virtual ICollection<UserHasCar> UserHasCars { get; set; }
}

public class ApplicationUser : IdentityUser
{
    public virtual ICollection<UserHasCar> UserHasCars { get; set; } 
}

The problem exists when I am trying to delete a record (delete a UserHasCar entity from the database). Because a UserHasCar has two foreign keys, after running this code I get the following error:

List<UserHasCar> list = (from t in context.UserHasCars where t.ApplicationUserId == userId select t).ToList();

        foreach (UserHasCar car in list)
        {
            context.UserHasCar.Remove(car); // BOOM!
        }

The nasty error is: A referential integrity constraint violation occurred: A primary key property that is a part of referential integrity constraint cannot be changed when the dependent object is Unchanged unless it is being set to the association's principal object. The principal object must be tracked and not marked for deletion.

My table definition looks like this:

CREATE TABLE [dbo].[UserHasCars] (
[ApplicationUserId] NVARCHAR (128) NOT NULL,
[CarId]           INT            NOT NULL,
[YearsDriven]  INT            NOT NULL,
CONSTRAINT [PK_dbo.UserHasCars] PRIMARY KEY CLUSTERED ([ApplicationUserId] ASC, [CarId] ASC),
CONSTRAINT [FK_dbo.UserHasCars_dbo.AspNetUsers_ApplicationUserId] FOREIGN KEY ([ApplicationUserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_dbo.UserHasCars_dbo.Cars_CarId] FOREIGN KEY ([CarId]) REFERENCES [dbo].[Cars] ([ID]) ON DELETE CASCADE
);

Do I have to do something simple like EntityState.Deleted/Modified to my two foreign keys? I know its complaining about my clustered primary key and how I cannot delete it without removing the references to my foreign keys... I'm sure I'm missing something small here.

EDIT

As I'm looking through more closely, this line List<UserHasCar> list = (from t in context.UserHasCars where t.ApplicationUserId == userId select t).ToList(); isn't pulling in the right ApplicationUser or Car from my database table, all values are null. I can see that the query isn't pulling in the proper ApplicationUser or Car, but why?

Are there any other tables, that use this table `UserHasCars` for a Foreign Key constrain on column [ApplicationUserId] with `ON DELETE CASCADE`? — Stoleg, Aug 27 '14 at 15:56
@Stoleg No, but I stumbled across the fix to this problem. I will mention it — reZach, Aug 29 '14 at 00:11

score 1 · Answer 1 · answered Aug 28 '14 at 20:19

1

Foreign key data is being sent to the model?

If you are working with Entity Framework test:

Modelo.Remove (data) Modelo.SaveChanges ();

answered Aug 28 '14 at 20:19

Lmleon19

11
2

score 1 · Answer 2 · answered Aug 29 '14 at 14:02

1

Can you try removing it from the main entities? For example

var user = dbContext.Users.Where(u => u.userId == userId);

foreach(var car in user.Cars) {
    user.Cars.Remove(car);
}

answered Aug 29 '14 at 14:02

adelb

791
7
26

score 1 · Answer 3 · edited May 23 '17 at 10:29

1

You may be able to amend the deletion/cascading rules on your database regarding key dependencies. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

Be sure to read up first though and be aware of exactly what you are doing:

When/Why to use Cascading in SQL Server?

edited May 23 '17 at 10:29

Community

1
1

answered Aug 31 '14 at 10:39

ne1410s

6,864
6
55
61

score 0 · Accepted Answer · answered Oct 08 '14 at 15:04

The problem was the Car and User id were being set to default values, and didn't represent actual rows in the table. I've written about how I've solved it on this page (near the bottom):

http://programmerscheatbook.blogspot.com/2014/08/mvc-making-join-table-with-payload.html

ApplicationDbContext db = new ApplicationDbContext();

string userId = [USER ID];
int carId = [CAR ID];

List <UserHasCar> list = (from rec in db.UserHasCars
                      join car in db.Cars on rec.CarId equals car.Id
                      where rec.ApplicationUserId == userId && rec.CarId == carId
                      select rec).ToList();

foreach (UserHasCar car in list)
{
    db.UserHasCars.Remove(car);
}

db.SaveChanges();

score -1 · Answer 5 · edited Jul 02 '15 at 14:49

-1

public ActionResult DeleteConfirmed(long id)
{
    EmployeeContacts employeecontect = db.EmployeeContacts.Find(id);
    if(employeecontect!=null)
    { 
        db.EmployeeContacts.Remove(employeecontect);
        db.SaveChanges();
    }
    Employee employee = db.Employee.Find(id);
    db.Employee.Remove(employee);
    db.SaveChanges();

    return RedirectToAction("Index");
}

edited Jul 02 '15 at 14:49

Holt

36,600
7
92
139

answered Jul 02 '15 at 05:37

sheroz

1
1

MVC Delete record with multiple required foreign key restraint

5 Answers5