Clever Geek Handbook

MVC Delete record with multiple required foreign key constraints

Removing from a join-payload table in MVC isn't fun at all. Similar questions are about a single foreign key and my situation is unique enough that I guaranteed it a question for myself (if not, point me to the link)

I have this model (which acts as my joint payload table - it acts as the connection 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 occurs when I try to delete the entry (delete the UserHasCar object from the database). Since 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!
        }

Disgusting error: 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 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 need to do something as simple as EntityState.Deleted / Modified for my two foreign keys? I know he is complaining about my clustered primary key and how I cannot delete it without deleting my foreign key references ... I'm sure I'm missing something small here.

EDIT

As I will go into more detail, this line List<UserHasCar> list = (from t in context.UserHasCars where t.ApplicationUserId == userId select t).ToList();

does not pull in the desired ApplicationUser or Car from my database table, all values ​​are zero. I can see the request is not pulling into the proper ApplicationUser or Car, but why?

+3


source to share


5 answers


The problem was that the Car and User IDs were set to their default values ​​and did not display the actual rows in the table. I wrote about how I solved it on this page (at 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();
0


source


Foreign key data is sent to the model?

If you are working with Entity Framework test:



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

+1


source


Can you try removing it from the main objects? for example

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

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


source


You might be able to change the delete / cascading rules in the database regarding key dependencies. SQL ON DELETE CASCADE, which path is the deletion on?

Be sure to read first, and remember exactly what you are doing:

When / Why Use Cascading in SQL Server?

+1


source 


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");
}
-1


source






More articles:


All Articles