How do I define an entity class to be shared as a collection across multiple entities using Entity Framework Core?

Question

I'm using Entity Framework Core with PostGreSQL in an ASP.NET Core Web API. I'm having difficulty defining a set of entities where both Class A and Class B contain a collection of Class C.

The resulting tables / columns that get generated during a DB migration are not what I'd expect.

Consider the following table definitions:

public class Supplier
{
    [Key]
    public Guid SupplierId { get; set; }
    public string SupplierName { get; set; }
    public List<Commodity> Commodities { get; set; }
}

public class Consumer
{
    [Key]
    public Guid ConsumerId { get; set; }
    public string ConsumerName { get; set; }
    public List<Commodity> Commodities { get; set; }
}

public class Commodity
{
    [Key]
    public Guid CommodityId { get; set; }
    public string CommodityName { get; set; }
}

These classes result in the following tables / columns being generated:

Suppliers
   SupplierId
   SupplierName
   
Consumers
   ConsumerId
   ConsumerName
   
Commodities
   CommodityId
   CommodityName
   ConsumerId

How do I create the model class definitions so that the generated tables/columns preserve these relationships? Is there a way to enforce join tables getting created?

Is there a specific set of class/field annotations that I should be using?

score 2 · Accepted Answer · answered Apr 24 '21 at 08:53

public class Supplier
{
    [Key]
    public Guid SupplierId { get; set; }
    public string SupplierName { get; set; }
    public ICollection<Commodity> Commodities { get; set; }
}

public class Consumer
{
    [Key]
    public Guid ConsumerId { get; set; }
    public string ConsumerName { get; set; }
    public ICollection<Commodity> Commodities { get; set; }
}

public class Commodity
{
    [Key]
    public Guid CommodityId { get; set; }
    public string CommodityName { get; set; }
   
    [Foreignkey("Supplier")
    public Guid SupplierId { get; set; }
    public Supplier Supplier { get; set; }
    
    
    [Foreignkey("Consumer")
    public Guid ConsumerId { get; set; }
    public Consumer Consumer{ get; set; }
}

To understand ICollection and IList better, I'd recommend: ICollection vs List

And this webpage has some good information about different relationships with EF. One-to-Many with EF

As per the default convention, EF makes a property as foreign key property when its name matches with the primary key property of a related entity. DataAnnotations

thx for the detailed explanation and links. Didn't know about ICollection vs List — JohnB, Apr 24 '21 at 18:21

score 1 · Answer 2 · answered Apr 24 '21 at 06:50

Given that these are one-to-many relationships, we'll want the Commodity table to be pointing to the Supplier and Consumer tables. You should add the relationship columns explicitly in the Commodity model:

public class Commodity
{
    [Key]
    public Guid CommodityId { get; set; }
    public string CommodityName { get; set; }

    public Guid SupplierId { get; set; }
    public Supplier Supplier { get; set; }

    public Guid ConsumerId { get; set; }
    public Consumer Consumer { get; set; }
}

No need to change the Supplier and Consumer models. This change should set up SupplierId and ConsumerId to be foreign keys, and then you should be able to access the relationships from either side.

How do I define an entity class to be shared as a collection across multiple entities using Entity Framework Core?

2 Answers2