1

I am doing a project and in that I two table agent and user. The user table contains a foreign key called agentid which takes the value from the id of the agent table. so here I need to delete an agent in the agent table and at the same time it must effect to the user table and must delete the row in the user table that having the same id of the agent that was deleted. eg, suppose if i deleted agent 10 from agent table, all the details in the id 10 must be deleted along with that the agentid with 10 in the user table must also be deleted. when I simply delete an agent in agent table, it generates an error that

The DELETE statement conflicted with the REFERENCE constraint

I dont know how to solve my problem and my code is

Controller

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public async Task<ActionResult> DeleteConfirmed(int id)
{
   AgentMaster agentMaster = await db.AgentMasters.FindAsync(id);
   db.AgentMasters.Remove(agentMaster);
   await db.SaveChangesAsync();
   return RedirectToAction("Index");
}

model

 public partial class AgentMaster
{
    public AgentMaster()
    {
        this.Users = new HashSet<User>();
    }

    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public virtual ICollection<User> Users { get; set; }
}

DbContext

 public NewEntities()
        : base("name=NewEntities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
        modelBuilder.Entity<AgentMaster>()
        .HasOptional(c => c.Users)
        .WithOptionalDependent()
        .WillCascadeOnDelete(true);
    }

This is my controller part, when I tried with join the table and delete the record it always shows the same error . Can anyone please help me to find a solution for my problem ?????

user355
  • 39
  • 2
  • 10

4 Answers4

2

If you are using SQL Server for Database, then just open your SQL Management Studio and open relationship windows for columns on which you have defined Foreign Keys relation. There is an option Insert And Update Specification, Expand it and for Delete Rule, select Cascade option.

enter image description here

For your scenario, after making these changes, if you delete agent id from agent table then it will automatically delete data referenced by this agent id in your user table.

mmushtaq
  • 3,430
  • 7
  • 30
  • 47
0

you don't any activity on asp.net , but you can enable Cascade option on relation between two table in dbms.

Aiyoub A.
  • 5,261
  • 8
  • 25
  • 38
  • how can I do this cascade delete ? by joining two table and delete the result ? – user355 Apr 23 '17 at 16:35
  • You are asked in this post clearly marked : [link]( http://stackoverflow.com/questions/43573410/how-to-delete-rows-of-two-different-table-having-same-id-mvc/43573495#answer-43573945) – Aiyoub A. Apr 24 '17 at 06:01
0

You can not directly delete the record of one table that is being referenced by another table with foreign key.

You will have to explicitly delete the related rows of the users table or you can use ON DELETE CASCADE to automatically delete the rows of the users table.

Amita K.
  • 1
  • 1
  • can you please explain how to do this cascade delete. sorry because I am new to mvc, didn't get completely how to do this delete operation – user355 Apr 23 '17 at 16:36
0

this error occurs when your table is dependent on another table in your case its one to many relationship so add this in your dbcontext 

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<AgentMaster>()
    .HasOptional(c => c.Users)
    .WithOptionalDependent()
    .WillCascadeOnDelete(true);
}
Usman
  • 4,615
  • 2
  • 17
  • 33
  • sorry I didn't get this answer can you please explain this or what is this override OnModelCreating(DbModelBuilder modelBuilder), didn't get that linehow can I change that part with my code? – user355 Apr 23 '17 at 17:16
  • you just have to add this in dbcontext and you can check this [DbContext.OnModelCreating Method](https://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext.onmodelcreating(v=vs.113).aspx) – Usman Apr 23 '17 at 17:21
  • @user355 in code you showed db.AgentMasters how are you initializing the db? – Usman Apr 23 '17 at 17:23
  • private A.db = new A(); – user355 Apr 23 '17 at 17:25
  • and A is your dbcontext? – Usman Apr 23 '17 at 17:28
  • you dont have to rename just add the above code in dbcontext (A) – Usman Apr 23 '17 at 17:44
  • but there is already a OnModelCreating Method is created and shows error on OnModelCreating that the same name was already defines a memberwith same parameter types. Should I need to change the name ? – user355 Apr 23 '17 at 17:46
  • if you already have OnModelCreating then just add the code inside already created OnModelCreating – Usman Apr 23 '17 at 17:52
  • @user355 can you add db context in question – Usman Apr 23 '17 at 18:14
  • have included dbContext class . – user355 Apr 23 '17 at 18:29
  • @user355 remove throw new UnintentionalCodeFirstException(); from dbcontext – Usman Apr 23 '17 at 18:55
  • thank you so much its working :) but what is the need of this throw new UnintentionalCodeFirstException(); statement ? – user355 Apr 23 '17 at 19:01
  • @user355 when ever you override any predefined function it sets exception by default so you have to remove it if you want to change its default implementation. you can accept my answer if it helped :) – Usman Apr 23 '17 at 19:04
  • your answer helped me a lot but what if I need to do to get default exception – user355 Apr 23 '17 at 19:11
  • @user355 you dont have to throw exception in OnModelCreating and if you want to handle exception just use [Try Catch Block](http://stackoverflow.com/questions/14973642/how-using-try-catch-for-exception-handling-is-best-practice) in action – Usman Apr 23 '17 at 19:14