Categories/Subcategories in asp.net mvc

Question

We are making a marketplace like https://www.etsy.com/. And we have a problem in categorising the listings. We want to categories the item in the Listing in 3 levels, f.ex it has to be categories in this order:

 Category 1 
    Sub Category 1.1
       Sub Category 1.1.1

One of the important thing is that when you choose a category, f.ex. Electronics, then in the subcategory you can only see stuff like pc, smartphone, tv etc.

This is what we have now

 public class Listing
{
    public int ListingId { get; set; }
    public String Name { get; set; }

    public int Subcategory2Id { get; set; }
    public virtual Subcategory2 Subcategory2 { get; set; }
}



public class Category
{
    public int CategoryId { get; set; }
    public String CategoryName { get; set; }

    public virtual ICollection<Subcategory1> Subcategory1s { get; set; }
}



public class Subcategory1
{
    public int Subcategory1Id { get; set; }
    public String Subcategory1Name { get; set; }

    public int CategoryId { get; set; }
    public virtual Category Categories { get; set; }

    public virtual ICollection<Subcategory2> Subcategory2s { get; set; }
}



public class Subcategory2
{
    public int Subcategory2Id { get; set; }
    public String Subcategory2Name { get; set; }

    public int Subcategory1Id { get; set; }
    public virtual Subcategory1 Subcategory1s { get; set; }

    public virtual ICollection<Listing> Listings { get; set; }
}

and in the IdentityModels-ApplicationDbContext we have

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public DbSet<Listing> Listings { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<Subcategory1> Subcategory1s { get; set; }
    public DbSet<Subcategory2> Subcategory2s { get; set; }

    public ApplicationDbContext()
        : base("DefaultConnection", throwIfV1Schema: false)
    {
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }
}

The thing is we are not sure this is he right way to do it, and we dont know how to proceed, the meaning is that when you create a listing you have to have 3 drop down list, where you choose the respective categorys. So first you choose your category, and then you are able to select the subcategory 1 etc...

Answer 1

You should absolutely not have multiple category/subcategory entities. A category can have a parent and it can have children, but they're all "categories".

public class Category
{
    public int Id { get; set; }

    public int? ParentId { get; set; }
    public virtual Category Parent { get; set; }

    public virtual ICollection<Category> Children { get; set; }
}

ParentId is nullable, because top-level categories have no parent.

Entity Framework tends to get confused by self-referencing relationships, so you might need a little fluent config to help it out:

public class Category
{
    // properties

    public class Mapping : EntityTypeConfiguration<Category>
    {
        public class Mapping()
        {
            HasOptional(m => m.Parent).WithMany(m => m.Children);
        }
    }
}

Then, in your context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new Category.Mapping());
}

With all that in place, when you're in your "Electronics" category, you'd show the subcategories simply by iterating over it's Children property.

UPDATE

If you need the full hierarchy rather than just one level at a time, you have a couple of options. First, you can just include multiple levels when querying:

db.Categories.Include("Children.Children");

That's not highly efficient, though, and I definitely would not recommend delving much deeper than tertiary children. However, that's all you're asking for, so this is still a workable method.

Second, you can create a stored procedure to walk the hierarchical structure for you. It's a little more complex, but with a combination of WITH and UNION ALL, you can create a flat representation of the hierarchy and then recursively use LINQ's GroupBy to work it back into a hierarchical structure.

There's a final potential third option in @Hackerman's recommendation of using HIERARCHYID, but unfortunately, to do that, you must completely remove Category from your EF context, which also means removing any direct relationships to it, as well. To relate a product to a category, you could only store the id (not as a foreign key), and then use that id to manually lookup the category in a second step. Unfortunately, while this solution makes dealing the the hierarchy easier, it makes doing everything else more difficult. Either way, it's up to you, though.