How to delete a zero or one foreign key relation in EF Code First? FK to be deleted in tables when optional entity deleted

Question

I have two classes, one of them holds nullable ID property of other (as foreign key) and virtual navigation property. When I delete my entity, I want it's ID to be deleted from other tables where hold as foreign key. It doesn't happen and I get error:

"The DELETE statement conflicted with the REFERENCE constraint FK_MyDb.ArtWorks_MyDb.ImageFiles_ImgId. The conflict occurred in database MyDatabase , table MyDb.ArtWorks, column 'ImgId'. The statement has been terminated."

Here is the entity holding foreign key:

public class ArtWork : EntityBase
{
 public int ID { get; set; }
 public int? ImgId { get; set; }
 public virtual ImageFile Img { get; set; }
}

And this is the entity which when I delete error occurs.

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

As far as I can see on migration file things are properly created:

            CreateTable(
            "MyDb.ArtWorks",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    ImgId = c.Int()
                })
            .PrimaryKey(t => t.ID)
            .ForeignKey("MyDb.ImageFiles", t => t.ImgId)
            .Index(t => t.ImgId);

Other one:

            CreateTable(
            "MyDb.ImageFiles",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true)
                })
            .PrimaryKey(t => t.ID);

I've deleted here irrelevant properties to be brief.

I've tried out few fluent api definitions, none changed the situation. What is wrong with these definitions?

Thank you.

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

Before calling Db.ImageFiles.Remove(myImageFile), you should remove the Artworks that depends on it :

ImageFile.Artworks.Clear();//If you have this relation
//Otherwise
foreach (var artwork in Db.Artworks.Where(x=>x.ImgId == myImageFile.ID)){
     artwork.ImgId = null;
}

An other solution is to ask EF to delete on cascade. You can have a look at this post if you want (StackOverflow : Entity Framework (EF) Code First Cascade Delete). I personnaly prefer keep the control on what I'm doing...

edited May 23 '17 at 11:50

Community

1
1

answered Sep 03 '14 at 10:04

gilles emmanuel

221
3
16

To use the method proposed in other Stackoverflow question you linked, I can not declare foreign keys as nullable int. I'd like to declare them like so. There should be an automatic propagation when a row in a table deleted, ID contained as FK in other tables get deleted as well. – Bogac Sep 03 '14 at 12:49
you actually can declare foreign key as nullable... Take a look at this maybe ? http://stackoverflow.com/questions/5668801/entity-framework-code-first-null-foreign-key – gilles emmanuel Sep 03 '14 at 12:52
I know that you can, check my example, it's like that. But when you do that fluent API declarations like .HasOptional() and .WithOptionalDependent() become erroneous. For fluent API they should NOT be nullable. – Bogac Sep 03 '14 at 13:07

score 0 · Answer 2 · answered Sep 03 '14 at 14:23

This is how I solved this situation, but I don't like it:

Created an "Action Filter Attribute" which executes before Delete action of related controller. That filter deletes ID of to-be-deleted-object from other tables/repositories then returns back to actual Delete action.

Because all foreign keys are deleted before actual object deletetion, no problem occurs.

How to delete a zero or one foreign key relation in EF Code First? FK to be deleted in tables when optional entity deleted

2 Answers2