I have a table of files which I want to reference in 2 other tables. Each file is exclusive to one of these tables, which means that I have foreign keys which has no entity in at least one of the linked tables, some files might have a filled key which is linked to no entity at all (since it will be crated at a later time).

Relation: (1 -> n)

Item <- File -> Image

This causes a foreign key exception on insert/SaveChanges, since the database was unable to find the linked entity.

I searched for a solution but was unable to find any articles which address this issue and the solutions I came up with had all at least one code smell.

Question: how do I link these 3 tables without getting a DB exception and producing code/db smells?

Or is maybe the entire data architecture faulty and I should try something different (and if what)?

Solutions which I came up with but would like not to use:

• no foreign key but a new query
• use an intermediary table which has only entities of linked entities (file -> link -> item)
• splitting the Files table into ItemFiles and ImageFiles (I heard this was a DB smell)

Other Information:

• .NET Core 3.1
• EF Core: latest
• Database: Sqlite

Shortened models:

public class FileData
{
    public Item Item { get; set; }
    public ImageData Image { get; set; }
    public Guid Id { get; set; }
    public string HashKey { get; set; }
    // ...
}

public class Item
{
    public FileData[] Files { get; set; }

    public Guid Id { get; set; }
    public string HashKey { get; set; }
    // ...
}

public class ImageData
{
    public FileData[] Files { get; set; }
    public Guid Id { get; set; }
    public string HashKey { get; set; }
    // ...
}

Database configuration:

public class FileDataConfiguration : IEntityTypeConfiguration<FileData>
{
    public void Configure(EntityTypeBuilder<FileData> builder)
    {
        builder.HasKey(file => file.Id);
        builder.HasIndex(file => file.HashKey);
        // ...
    }
}

public class ItemConfiguration : IEntityTypeConfiguration<Item>
{
    public void Configure(EntityTypeBuilder<Item> builder)
    {
        builder.HasKey(item => item.Id);
        builder.HasMany(item => item.Files)
            .WithOne(file => file.Item)
            .IsRequired(false)
            .HasForeignKey(file => file.HashKey)
            .IsRequired(false)
            .HasPrincipalKey(item => item.HashKey);
        builder.HasIndex(file => file.HashKey);
        // ...
    }
}

public class ImageDataConfiguration : IEntityTypeConfiguration<ImageData>
{
    public void Configure(EntityTypeBuilder<ImageData> builder)
    {
        builder.HasKey(image => image.Id);
        builder.HasMany(image => image.Files)
            .WithOne(file => file.Image)
            .IsRequired(false)
            .HasForeignKey(file => file.HashKey)
            .IsRequired(false)
            .HasPrincipalKey(image => image.HashKey);
        builder.HasIndex(image => image.HashKey);
        // ...
    }
}

This Code Throws The Exception

// both examples throw an exception, independent of each other
//example 1:
dbContext.Files.Add(
    new File(){
        HashKey="1"
    }
);
dbContext.SaveChanges();

//example 2:
dbContext.Files.Add(
    new File(){
        HashKey="2"
    }
);
dbContext.Items.Add(
    new Item(){
        HashKey="2"
    }
);
dbContext.SaveChanges();

CodePudding user response：

In general, your FileData entity should contain Guid foreign keys to both Item and ImageData entities, not just navigation properties i.e. Guid ItemId and Guid ImageId. For example:

public class FileData
{
    public Guid ItemId { get; set;}
    public Item Item { get; set; }
    public Guid ImageId {get; set; }
    public ImageData Image { get; set; }
    public Guid Id { get; set; }
    public string HashKey { get; set; }
    // ...
 }

Also in Fluent Api when configuring both Item and ImageData this configuration should be enough

 builder.HasMany(item => item.Files)
        .WithOne(file => file.Item)
        .IsRequired(false)
        .OnDelete(DeleteBehavior.NoAction);

CodePudding user response：

In the end I decided to ditch the foreign keys since I don't need updates on the other entities and start the file-query by hand. Adding the Files as Property didn't seem to be possible, since the error kept to be thrown (I tried this solution).

List of used constraints: Item.Id => Key Item.Hash => Indexed, Unique

Image.Id => Key Image.Hash => Indexed, Unique

File.Id => Key File.Hash => Indexed, not Unique

One of the reasons I decided to keep the IDs of Item and Image was that other entities refer to these mostly via ID, not hash and the hash could change, requiring a lot of updates.