Entity Framework - Db design suggestion

Question

I have two models with many-to-many relationship:

public class Person
{
    public int PersonId { get; set; }
    public string Name { get; set;}
    public virtual ICollection<Hobby> Hobbies { get; set; } = new List<Hobby>();
}

and

public class Hobby
{
    public int HobbyId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Person> Persons { get; set; } = new List<Person>();
}

In other words a person may have several hobbies and a hobby could interest many persons.

Now I have below model which has many-to-many relationship with the Person model:

public class Activity
{
    public int ActivityId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Person> Persons { get; set; } = new List<Person>();
}

I have setup my Blazor WASM project and I have populated all the tables using EntityFramework Code First. Here is how my data structure in the database looks like:

1. I have populated the Hobbies table with example data
2. I have populated the Persons table with example data, and assigned at least one or more hobby from the table above to each person.
3. I have implemented all CRUD operations for the two above processes and everything works just fine.

Where am I stuck and what am I trying to achieve? I want to create a new activity with persons based on hobbies. Let's say that my Hobbies table contains the following hobbies:

• Art
• Sports
• Photography

And my Persons table contains the following persons:

• Clara
• Mathew
• Jack
• Rose

I would like to create new activities like below:

The problem is that with my current design of the database, I can't keep track of what specific Hobby was chosen from each Person to create the Activity. I need a way to reflect that in the database.

For instance, if I browse my activity View.razor, the Hobby field in the activity would list all the hobbies and not that specific one I created the activity based upon.

Please feel free to ask for more details as I may have not been sufficiently clear about everything.

Any suggestions?

Answer 1

@Eliya Amanoeel, Welcome to Microsoft Q&A, based on your description, you want to achieve the one to one relationship for Activity and Hobby.

I find that I need to make some changes on your many to many relationships so that I could get the output of activities.

Here is a code example you could refer to.

    public class Person
    {
        public int PersonId { get; set; }
        public string Name { get; set; }
        public virtual ICollection<PersonHobby> personHobbies { get; set; } = new List<PersonHobby>();

    }
    public class Hobby
    {
        public int HobbyId { get; set; }
        public string Name { get; set; }
        public virtual ICollection<PersonHobby> personHobbies { get; set; } = new List<PersonHobby>();
        public Activity activity { get; set; }  
 
    }
    [Keyless]
    public class PersonHobby
    {
        
        public int PersonId { get; set; }

        public Person person { get; set; }  


        public int HobbyId { get; set; }
       
        public Hobby hobby { get; set; }

    }
    public class Activity
    {
        public int ActivityId { get; set; }
        public string Name { get; set; }
        public int HobbyId { get; set; }
        public Hobby hobby { get; set; }    
        //public virtual ICollection<Person> Persons { get; set; } = new List<Person>();

    }
    public class MyContext:DbContext
    {
        public DbSet<Activity> Activitys { get; set;}

        public DbSet<Person> Persons { get; set; }  

        public DbSet<PersonHobby> personHobbies { get; set; }
        public DbSet<Hobby> Hobbies { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Data Source=(localdb)\\ProjectModels;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False");
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<PersonHobby>().HasKey(h => new { h.PersonId, h.HobbyId });
            modelBuilder.Entity<PersonHobby>()
                .HasOne(ph => ph.person)
                .WithMany(ph => ph.personHobbies)
                .HasForeignKey(ph => ph.PersonId);
            modelBuilder.Entity<PersonHobby>()
               .HasOne(ph => ph.hobby)
               .WithMany(ph => ph.personHobbies)
               .HasForeignKey(ph => ph.HobbyId);

            modelBuilder.Entity<Activity>()
                .HasOne(h=>h.hobby)
                .WithOne(h=>h.activity)
                .HasForeignKey<Hobby>(e => e.HobbyId)
        .IsRequired();
        }
    }

       static void Main(string[] args)
        {
            MyContext context = new MyContext();
            Person person1 = new Person();
            person1.Name = "Clara";
            Person person2 = new Person();
            person2.Name = "Jack";
            Person person3 = new Person();
            person3.Name = "Rose";
            Hobby hobby = new Hobby();
            hobby.Name = "Arts";
            hobby.personHobbies.Add(new PersonHobby { person = person2 });
            hobby.personHobbies.Add(new PersonHobby { person = person3 });
   
            Hobby hobby1 = new Hobby();
            hobby1.Name = "Sports";
            hobby1.personHobbies.Add(new PersonHobby { person = person2 });
            hobby1.personHobbies.Add(new PersonHobby { person = person3 });

            Hobby hobby2 = new Hobby();
            hobby2.Name = "Photography";
            hobby2.personHobbies.Add(new PersonHobby { person = person1 });
            hobby2.personHobbies.Add(new PersonHobby { person = person3 });


            Activity activity1 = new Activity();
            activity1.hobby = hobby;
            activity1.Name = "act1";

            Activity activity2 = new Activity();
            activity2.hobby = hobby1;
            activity2.Name = "act2";

            Activity activity3 = new Activity();
            activity3.hobby = hobby2;
            activity3.Name = "act3";
            context.Activitys.Add(activity1);
            context.Hobbies.Add(hobby);
            context.Activitys.Add(activity2);
            context.Hobbies.Add(hobby1);
            context.Activitys.Add(activity3);
            context.Hobbies.Add(hobby2);
            context.SaveChanges();
            foreach (var item in context.Activitys)
            {
                Console.WriteLine("Activity is "+item.Name);
                Console.WriteLine("Hobby is " + item.hobby.Name);

                foreach (var p in item.hobby.personHobbies)
                {
                    Console.WriteLine(  p.person.Name);
                }
                Console.WriteLine(  "***************");
            }
       


        }

Tested result:

Hope my code example could help you.

Best Regards,

Jack

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.