Entity Framework: how to delete related entity upon update or delete of 'generic' parent

Question

I have a Product class, which contains a Picture property. Other classes may also have a picture, e.g. a Customer can have a Picture.

Product.cs:

public class Product
{
    public Picture Picture { get; set; }
}

Customer.cs:

public class Customer
{
    public Picture Picture { get; set; }
}

Picture.cs:

public class Picture
{
    public string Type { get; set; }

    public byte[] Content { get; et; }
}

The picture is always optional, i.e. Products and Customers may or may not have a picture. However, a picture is uniquely linked to exactly one 'parent' entity (either a Product or a Customer). Without the parent, there's no use for the Picture to exist. Note that there's no link from Picture to the parent class, because this parent can be of multiple types (Product or Customer).

Another requirement is that I want to have full control over whether the picture is loaded or not, through eager or lazy loading. E.g. when retrieving a list of products, I don't want the picture to be fetched, but if a single product is requested, the picture should be included.

My question: how can I configure Entity Framework so that:

The Picture record is deleted when the parent (product or customer) record is deleted
The Picture record is deleted whenever the parent record is updated and the new version does not contain a picture
The Picture record is replaced (delete + create of a record) whenever the parent is updated with a new picture
The picture is optional
Always keep control over whether the picture is loaded or not

I'm using fluent API to define the relationship. Currently, there's no cascading delete whatsoever.

public class ProductMap : EntityTypeConfiguration<Product>
{
    public ProductMap()
    {
        ToTable("PRODUCTS");

        HasKey(x => x.Id);

        HasOptional(x => x.Picture).WithOptionalDependent().Map(m => m.MapKey("PICTUREID"));
    }
}

I tried using WithRequired but this generates errors because there's no link or foreign key from Picture to Product/Customer.

Why just not create to seperate tables as CustomerPictures and ProductPictures? — Adil Mammadov, Jul 20 '16 at 07:10
You can only do this if you accept that each entity has its own foreign key in `PICTURES`. — Gert Arnold, Jul 22 '16 at 19:50

score 3 · Accepted Answer · answered Jul 23 '16 at 09:47

you can do as follow:

public abstract class Picture
{
    public int Id {get;set;}
    public string Type { get; set; }
    public byte[] Content { get; set; }
}

public class ProductImage:Picture
{
    public int ProductId {get;set;}
    public virtual Product Product {get;set;}
}
public class CustomerImage:Picture
{
   public int CustomerId {get;set;}
   public virtual Customer Customer{get;set;}
}

then you can configuer like this: ex. for product:

HasOptional(x=>x.ProductImage)
 .withRequired(x=>x.Product)
 .HasForeignKey(x=>x.ProductId); //cascade on delete is default true

In this way you can load picture when you need it, and if you delete product item, the image will be deleted as well. The image is optional, and can be replaced. On update you must specified a new image or delete the old image.

Hope this alternative will help you chose exact what you want.

Thanks for your answer. While implementing it, I noticed there's no HasForeignKey method, but the general idea of your solution works great. The bounty is yours! — fikkatra, Jul 25 '16 at 11:29

Sayan Pal · Answer 2 · 2016-07-19T09:56:03.663

I think you can configure the Picture as ComplexType that will make the properties of Picture class columns in the parent table.

This means:

Whenever the parent is deleted, the corresponding Picture is deleted.
Whenever the parent is updated with new Picture data (updated picture data or no data), corresponding columns in the parent table are also updated.

How you can configure the Picture entity as ComplexType is described here: http://weblogs.asp.net/manavi/entity-association-mapping-with-code-first-part-1-one-to-one-associations.

Minimally this is what you need to do:

public class MyDbContext:DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Product> Products { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.ComplexType<Picture>();
    }
}

If you run migration then possibly it will generate the migrations similar to following:

    public override void Up()
    {
        CreateTable(
            "dbo.Customers",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Picture_Type = c.String(),
                    Picture_Content = c.Binary(),
                })
            .PrimaryKey(t => t.Id);

        CreateTable(
            "dbo.Products",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Picture_Type = c.String(),
                    Picture_Content = c.Binary(),
                })
            .PrimaryKey(t => t.Id);

    }

Additionally you can also write fluent configuration for ComplexType as follows:

public class PictureConfig : ComplexTypeConfiguration<Picture>
{
    public PictureConfig()
    {
        Property(t => t.Type).HasColumnName("PictureType");
        Property(t => t.Content).HasColumnName("PictureContent");
    }
}

and add this configuration in DbContext:

public class MyDbContext:DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Product> Products { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //modelBuilder.ComplexType<Picture>();
        modelBuilder.Configurations.Add(new PictureConfig());
    }
}

If you now add-migration, it might look like the following:

    public override void Up()
    {
        CreateTable(
            "dbo.Customers",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    PictureType = c.String(),
                    PictureContent = c.Binary(),
                })
            .PrimaryKey(t => t.Id);

        CreateTable(
            "dbo.Products",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    PictureType = c.String(),
                    PictureContent = c.Binary(),
                })
            .PrimaryKey(t => t.Id);

    }

Hope this helps.

Update based on comment: This part of the update does not really give you exactly what you want, but rather makes a mere suggestion.

I think you are looking for a relation like the following:

Product---1-----1---Picture ---1------1---Customer
|- Id               |- Id                 |- Id
|- PictureId?                             |- PictureId?

That is, you may keep nullable PictureId on the parent classes, and change this PictureId, whenever, the picture is changed.

Disadvantage:You need to manage the data manipulation activity (CRUD) on your own.

Advantage:This way you have full control over when to load the image, as loading the parent does not load the Picture automatically. Also this enables you to decouple the Picture from your database and use some sort of blob storage (may be on cloud or so).

Thank you for your answer, I found it very interesting. However, I ran into two issues. One is that Complex Types cannot be nullable. As Picture is always optional, the is a problem. The second is that I want to have full control over when I load the picture and when I don't, because it's a large file. This level of control (using lazy or eager loading) is not possible with Complex Types. I will update my question to make these requirements more clear. — fikkatra, Jul 19 '16 at 09:36
@fikkatra I have updated my answer with a mere suggestion. Check if you like. — Sayan Pal, Jul 19 '16 at 09:57

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

0

I found this here on StackOverflow as well and maybe it will help you:

modelBuilder.Entity<Product>()
    .HasKey(c => c.Id)
    .HasRequired(c => c.User)
    .WithRequiredDependent(c => c.UserProfile)
    .WillCascadeOnDelete(true);

I think you need to change your code a little bit but it should work...

Original Post: EF Code First Fluent API - Cascade Delete

Maybe this can help you as well: Cascade delete using Fluent API

edited May 23 '17 at 10:29

Community

1
1

answered Jul 19 '16 at 08:38

brothers28

1,196
17
23

This requires the dependent class to have a property referring to the parent class (UserProfile has a property called User), which isn't the case in my situation. The relationship needs to be unidirectional. – fikkatra Jul 19 '16 at 08:41
Why even create a separate table with the picture inside and not have the picture information in the Product/User Table itself? Do you have any benefit from that or do you need this anywhere? – brothers28 Jul 19 '16 at 08:44
Because the picture is a large piece of data, and I want to have control over when I fetch it, and when I don't. Because I can't tell Entity Framework to ignore a column, but I CAN tell Entity Framework to ignore a relationship, this is how I designed it. – fikkatra Jul 19 '16 at 08:46
Uhm but yes you can tell EF to ignore a column by just not selecting it – brothers28 Jul 19 '16 at 08:47
you can tell to `EF` to not load relations by setting the `LazyLoading` to false! – Lucian Bumb Jul 23 '16 at 09:55

score 0 · Answer 4 · answered Jul 22 '16 at 19:28

Add the call to delete the Picture where you are deleting the the product. If you are doing this in a number of places re-factor to encapsulate this operation. I find the EF configurations to be less than transparent but a simple call to delete in a DeleteProduct method or DeleteCustomer method are easy to read and understand.

Entity Framework: how to delete related entity upon update or delete of 'generic' parent

4 Answers4