When using Entity Framework Code First Approach there is one additional table

Question

I'm using Entity Framework code first approach to create mydatabase. I've got four simple classes:

My first class is Category:

public class Category
{
    [Key]
    public int CategoryId { set; get; }        
    public DateTime CreatedOn { set; get; }    
    public int? ParentCategoryId { set; get; }    
    public virtual ICollection<Product> Products { set; get; }    
    public virtual ICollection<CategoryLanguage> CategoriesLanguages { set; get; }    
    public Category()
    {
        this.Products = new HashSet<Product>();
        this.CategoriesLanguages = new HashSet<CategoryLanguage>();
    }
}

My Second class -CategoryLanguage.cs

public class CategoryLanguage
{
    [Key]
    public int Id { set; get; }    
    [Required]
    public string Title { set; get; }    
    public int CategoryId { set; get; }    
    public virtual Category Category { set; get; }    
    public int LanguageId { set; get; }
    public virtual Language Language { set; get; }
}

My third class - Product.cs

public class Product
{       
    [Key]
    public int ProductId { set; get; }
    public decimal Price { set; get; }
    public int Quantity { set; get; }
    public string Image { set; get; }
    public virtual ICollection<Category> Categories { set; get; }
    public virtual ICollection<ProductLanguage> ProductLanguages { set; get; }
    public Product()
    {
        this.Categories = new HashSet<Category>();
        this.ProductLanguages = new HashSet<ProductLanguage>();
    }
}

and my last class

public class ProductLanguage
{
    [Key]
    public int Id { set; get; }
    public int ProductId { set; get; }
    public virtual Product Product { set; get; }
    public int LanguageId { set; get; }
    public virtual Language Language { get; get; }
    public string Name { set; get; }
    public string ShortDescription { set; get; }
    public string Description { set; get; }
}

Here is my DbContext

public class EcommerceDBContext:DbContext
{
    public EcommerceDBContext() : base("DefaultConnection"){}
    public DbSet<Category> Categories { set; get; }
    public DbSet<CategoryLanguage> CategoriesLanguages { set; get; }
    public DbSet<Product> Products { set; get; }
    public DbSet<ProductLanguage> ProductsLanguages { set; get; }
}

It's curious that in my database I see another table, ProductCategories with two columns: Product_ProductID and Category_CategoryID.

So it's true that Product table and Categories table have many-to-many relationship and it's also true that I'm using lazy loading to be able to load all the products for Category; and all the categories for product. I'm just curious how code-first engine decides to make this table. (after all the collections are virtual and they should not exist in the database).

Community · Accepted Answer · 2020-06-20 09:12:55Z

You have answered your own question with the words

[the] product table and categories table have a many-to-many relationship

The lazy loading and virtual are not relevant - virtual just enables lazy loading by creation of proxy types at runtime (see references below).

Code first has simply detected that the two tables are linked with a many-many relationship because you have, within each class, an ICollection property of the related class: P has many C and C has many P. It therefore creates the join table^† using the naming convention <LeftTable><RightTable><plural modifier>, with the primary key columns from each side of the relationship.

References

See MSDN: Loading Related Entities "Lazy Loading" and "Turning off lazy loading for specific navigation properties", virtual is almost mentioned as a passing comment in these sections, but it is the reason lazy loading works.

^† join, link or mapping table depending on your preference

Slauma · Accepted Answer · 2014-05-10 11:26:00Z

When EF Code First decides how to map your model classes to a database schema it inspects the model classes and especially the navigation properties between those classes. In your example it finds that Category has a Products collection (a category has many products) and that a product has a Categories collection (a product has many categories). From this EF concludes that the relationship between the two entities is many-to-many. In order to map this to a relational database a link table is required which is the ProductCategories table that is introduced.

There are more details of course that control the mapping - like data annotations and Fluent API. But for your specific example the mapping relies on conventions and the class definitions only.

BTW: The fact that your navigation properties are virtual doesn't matter. Mapping between entities and database schema is independent on navigation properties being virtual (supporting lazy loading) or not.

Collectives™ on Stack Overflow

When using Entity Framework Code First Approach there is one additional table

2 Answers 2

References

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

References

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related