CODEWITHSUNDEEP
c#entity-framework
r3plica
r3plica

Reputation: 13367

EntityFramework, Database design Table with nullable foreign keys

So I have this issue with my database design. I have four tables:Garments, Attachments, Images and Fonts.

A Garment can have multiple or no Attachments so that is easy to map:

modelBuilder.Entity<Garment>().HasMany(m => m.Attachments).WithOptional().HasForeignKey(m => m.GarmentId);

But the complication arrises with Attachments. I have read that having multiple nullable foreign keys is bad practice, but I can't work out how to map this flow.

An Attachement can either have an Image OR a Font. It can never have both and must have at least one.

My initial class looked like this:

public class Attachment
{
    public int ImageId { get; set; }
    public int FontId { get; set; }
    [Required] public int GarmentId { get; set; }
    [MaxLength(50)] [Required] public string PanelId { get; set; }

    public Image Image { get; set; }
    public Font Font { get; set; }
}

but this doesn't enforce anything. Is there anyway way that EF can handle this in a good way?

Upvotes: 0

Views: 44

Answers (1)

Guillaume
Guillaume

Reputation: 13128

You can use Entity Framework Validation and especially IValidatableObject

public class Attachment : IValidatableObject
{
    //...

    public Image Image { get; set; }
    public Font Font { get; set; }

    public IEnumerable<ValidationResult> Validate(ValidationContext validationContext) 
    { 
        if (Image == null && Font == null) 
        { 
            yield return new ValidationResult 
             ("Image or Font must be specified", new[] { "Image", "Font" }); 
        }
        if (Image != null && Font != null) 
        { 
            yield return new ValidationResult 
             ("You cannot provide both an Image and a Font", new[] { "Image", "Font" }); 
        }
    }
}

If you really need it and support is provided, you may add a constraint on the data store. For SQL Server, see CHECK CONSTRAINT.

Upvotes: 1

Related Questions