Concatenate multiple rows into a single one in the same table with Razor and Create operation

Question

Hello everyone

I'm building an inventory system where i have the Products table with:

PK ID - Name - Category

I need to assign Products to existing products in this table. Maybe this sounds a little bit confusing so i designed an example:

This is the table i currently have

This is what i want to achieve but i don't know how.

My questions are, how can i edit my Products model for a correct reference of these items? I should use varchar, or a list, or reference items by it's id?

If it's via String, how can i concatenate the multiple rows into that one?

Answer 1

You'll need to fix the design otherwise you'll be pulling your hair out when it comes time to query the data. Assignable items should exist in a separate table maybe named Accessory including a primary key that identifies a Mouse (1) and a Keyboard (2). Create a third table that relates an Accessory to a Product named ProductAccessories. The ProductAccessories table will have a ProductId and an AccessoryId column which makeup the primary key for the ProductAccessories table.

The Laptop in your example will have two records in the ProductAccessories table.

1 (laptop) | 1 (mouse)
1 (laptop) | 2 (keyboard)

By the way, this is called a many-to-many relationship and explained in another one of your threads on this subject. Please see the Entity Framework docs to learn navigation basics.

Relationships, navigation properties, and foreign keys
Code First Data Annotations
Fluent API - Relationships