Entity Framework, Fluent API relationships - many-to-one-or-zero

Question

I am trying to setup a specific relationship using FluentAPI on 2 entities. See details below

public abstract class Entity
{
    [Key]
    public int Id { get; set; }
}

public class Inventory: Entity
{
    public int Version { get; set; }
    public string Set_num { get; set; }
    [JsonIgnore]
    public virtual ICollection<InventoryPart> InventoryParts { get; set; }
}

public class InventoryPart : Entity
{
    public int Inventory_id { get; set; }
    public string Part_num { get; set; }
    public int Color_id { get; set; }
    public int Quantity { get; set; }
    public string Is_spare { get; set; }
    public string Img_url { get; set; }                        
    [JsonIgnore]
    public virtual UserPart? UserPart { get; set; }
}

public class UserPart : Entity
{                
    public string Part_num { get; set; }          
    public int Color_id { get; set; }        
    public int Quantity { get; set; }
    public string Location { get; set; }   
    [JsonIgnore]
    public virtual ICollection<InventoryPart> InventoryParts { get; set; }
}

OnModelCreating code:

modelBuilder.Entity<Inventory>()
            .HasMany(b => b.InventoryParts)
            .WithOne()
            .HasForeignKey(lim => lim.Inventory_id);
        
modelBuilder.Entity<InventoryPart>()
            .HasKey(l => new { l.Part_num, l.Color_id }); // This IS NOT unique! Inventory_id, Part_num, Color_id, Is_spare would be the unique compound key
                
modelBuilder.Entity<UserPart>()
            .HasKey(u => new { u.Part_num, u.Color_id }); // This IS unique

modelBuilder.Entity<UserPart>()
            .HasMany(u => u.InventoryParts)
            .WithOne(l => l.UserPart)
            .HasForeignKey(l => new { l.Part_num, l.Color_id });

The goal is to filter an InventoryPart and include InventoryParts and the linked UserPart.

public class GetSetPartsBySpecification : Specification<Inventory>, ISingleResultSpecification
{
    public GetSetPartsBySpecification(string setnum)            
    {
        Query
            .Where(inv => inv.Set_num == setnum)                            
            .Include(ip => ip.InventoryParts)
                .ThenInclude(ip=>ip.UseParts)
            .AsSplitQuery();
    }
}

Notes on the data: Inventory and InventoryParts are imported into a SQLite database periodically. Inventory.Id is the InventoryPart.Inventory_id and is NOT autogenerated, it gets the value from the Excel import file.

An InventoryPart has a many-to-one-or-zero relationship with UserPart.

A UserPart has a one-to-many relationship with InventoryPart.

I don't need the reverse relationship, UserParts.InventoryParts

Question

Is it possible to define a relationship using Fluent API on the above entities structure? You can see my attempt above. I get an error because .HasKey(l => new { l.Part_num, l.Color_id }) on the InventoryPart is not unique.

Notes

I have a version of this working using a set of tables that I create during the import process but I would prefer to us the existing data structure. I can add that code if requested.

Are you trying to map entity classes/relations to existing database tables/relationships or the other way around (code first and migrations for updating database structure)? — Ivan Stoev
– Ivan Stoev, Commented Mar 5, 2023 at 8:27
Code first and migrations. The data is from an external source in Excel. I create entities that match the exact structure of that data. — stevenmahony
– stevenmahony, Commented Mar 5, 2023 at 18:12

Ivan Stoev · Accepted Answer · 2023-03-05 18:35:39Z

0

HasKey is for defining the primary key of an entity. Since none of the following are primary keys, remove them both

modelBuilder.Entity<InventoryPart>()
    .HasKey(l => new { l.Part_num, l.Color_id }); // This IS NOT unique! Inventory_id, Part_num, Color_id, Is_spare would be the unique compound key
                
modelBuilder.Entity<UserPart>()
    .HasKey(u => new { u.Part_num, u.Color_id }); // This IS unique

Instead, use HasForeignKey / HasPrincipalKey to define key property(es) for both sides of the relationship

modelBuilder.Entity<UserPart>()
    .HasMany(u => u.InventoryParts)
    .WithOne(l => l.UserPart)
    .HasForeignKey(l => new { l.Part_num, l.Color_id })
    .HasPrincipalKey(u => new { u.Part_num, u.Color_id })
    .IsRequired(false);

This will define unique composite key in principal entity table, and non unique composite foreign key in the dependent entity table.

Note however that optional relationship requires nullable FK column(s), so in your case you have to change the types of the properties in the InventoryPart class (make them both nullable):

public class InventoryPart : Entity
{
    //...
    public string? Part_num { get; set; }
    public int? Color_id { get; set; }
    // ...
    [JsonIgnore]
    public virtual UserPart? UserPart { get; set; }
    // ...
}

This should do what you are asking for. Don't forget to generate and apply new migration after making the aforementioned mods.

answered Mar 5, 2023 at 18:35

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

stevenmahony Over a year ago

Thanks. made the changes. Recreated the DB (applied the migration). I get a SQLite Error 19: 'FOREIGN KEY constraint failed'. when it tries to save the Inventory_Part data. Its the first record in the file. This is the constraint created on the table in SQLite - CONSTRAINT "FK_inventory_parts_userparts_Part_num_Color_id" FOREIGN KEY ("Part_num", "Color_id") REFERENCES "userparts" ("Part_num", "Color_id"). The particular part its erroring on does not exisit in the UserParts data.

Ivan Stoev Over a year ago

Well, this is how FK relationships work in relational databases, which EF Core is based on. Optional doesn't mean the dependent table could refer to non existing record in the principal table. If this is what you are looking for, then you can't use EF Core navigation properties and database relations, remove/unmap all these from the model and then use manual joins for querying etc.

stevenmahony Over a year ago

Thanks for all your help and time. Ill keep at it.

Collectives™ on Stack Overflow

Entity Framework, Fluent API relationships - many-to-one-or-zero

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related