CODEWITHSUNDEEP
c#entity-framework
xcelm
xcelm

Reputation: 571

Generating non primary key IDs using Entity Framework

Hi I would like to generate non-primary key IDs based on "master" category. In my Domain Model shown below it would be that lvl2 IDs are generated based on lvl1 IDs. I guess I should use fluent API in order to do this, but I haven't found any specific example of this.

Example of what it should look like in DB is:

ID  CatL1Id CatL1Name CatL2Id CatL2Name
1   1       PC        1       AllInOne
2   1       PC        2       Classic PC
3   2       Laptop    1       NetBook
4   2       Laptop    2       Class Laptop
5   3       Mac       1       MacBook Pro

My domain model is

public class Category
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int CategoryId { get; set; }

    public int CategoryLevel1Id { get; set; }

    public int CategoryLevel2Id { get; set; }

    public int CategoryLevel3Id { get; set; }

    public string CategoryLevel1Name { get; set; }

    public string CategoryLevel2Name { get; set; }

    public string CategoryLevel3Name { get; set; }

    public virtual ICollection<Product> Products { get; set; }
}

Any help appreciated! Thanks in advance

Upvotes: 0

Views: 83

Answers (2)

Tanveer Yousuf
Tanveer Yousuf

Reputation: 406

Your domain model when translated into database tables would be against database normalization principles.

Problem: Levels columns in the category entity would create redundancy and not to mention a change in the model would be needed if you figure out later that there are more levels to accommodate.

Solution: Here is what I suggest to you:

Step 1. Modify your entity and create a parent and child relationship like this:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace DomainModel
{
    public class Category
    {
        public Category()
        {
            Children = new HashSet<Category>();
        }

        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        public int? ParentId { get; set; }

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

        public virtual Category Parent { get; set; }
    }
}

The parent child relationship allows you to create as many levels as you need. If a category doesn't belong to a level (in other words is a root category), its parent would be null.

Step 2. In the OnModelCreating() method of your DbContext class, specify the relationship as follows:

modelBuilder.Entity<Category>()
    .HasMany(e => e.Children)
    .WithOptional(e => e.Parent)
    .HasForeignKey(e => e.ParentId);

The end table in the database would look like this:

enter image description here

Upvotes: 4

Sani Huttunen
Sani Huttunen

Reputation: 24395

I'd split that table into two tables. One for Cat1 and one for Cat2 with a foreign key to Cat1. In that way you won't have a lot of duplicate Cat1 entries and you can easily map Cat2 to the correct Cat1.

This will make it a one-to-many releationship between Cat1 and Cat2.

I wouldn't worry about exactly what the Id values are since that is irrelevant.
The mapping is more important and also that your indices are correctly setup.

So something like this is what I imagine:

Category 1:
ID  CategoryLevel1Name 
1   PC
2   Laptop
3   Mac

Category 2:
ID  CategoryLevel1Id CategoryLevel2Name 
1   1                AllInOne
2   1                Classic PC
3   2                NetBook
4   2                Class Laptop
5   3                MacBook Pro

public class Category1
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string CategoryLevel1Name { get; set; }

    public ICollection<Category2> Category2s { get; set; }
}

public class Category2
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int CategoryLevel1Id { get; set; }
    public string CategoryLevel2Name { get; set; }

    [ForeignKey("CategoryLevel1Id ")]
    public Category1 Category1 { get; set; }
}

Disclaimer: Code not tested.

Upvotes: 0

Related Questions