Entity Framework - Db design suggestion

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 Hobbies { get; set; } = new List();
}

and

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

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 Persons { get; set; } = new List();
}

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:

I have populated the Hobbies table with example data
I have populated the Persons table with example data, and assigned at least one or more hobby from the table above to each person.
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:

Activity
Name	Hobby	Person
Activity X	Art	Clara, Mathew
Activity Y	Sports	Jack, Rose
Activity Z	Photography	Clara, Rose

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?

public class ActivityPersonHobby { public int ActivityPersonHobbyId { get; set; } public int ActivityId { get; set; } public Activity Activity { get; set; } public int PersonId { get; set; } public Person Person { get; set; } public int HobbyId { get; set; } public Hobby Hobby { get; set; } }

// Example code to create a new activity with persons based on hobbies var activity = new Activity { Name = "Activity X" }; var clara = dbContext.Persons.FirstOrDefault(p => p.Name == "Clara"); var mathew = dbContext.Persons.FirstOrDefault(p => p.Name == "Mathew"); var artHobby = dbContext.Hobbies.FirstOrDefault(h => h.Name == "Art"); activity.ActivityPersonHobbies.Add(new ActivityPersonHobby { Person = clara, Hobby = artHobby }); activity.ActivityPersonHobbies.Add(new ActivityPersonHobby { Person = mathew, Hobby = artHobby }); dbContext.Activities.Add(activity); dbContext.SaveChanges();

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Rajkiran Swain3y ago

Based on your description, it seems like you're looking for a way to associate specific hobbies from each person to an activity and reflect that relationship in the database. One possible solution is to introduce a new entity to represent the relationship between hobbies, persons, and activities. Here's a suggestion:

Create a new entity called ActivityPersonHobby to represent the relationship between hobbies, persons, and activities:

public class Activity
{
    public int ActivityId { get; set; }
    public string Name { get; set; }
    
    public virtual ICollection ActivityPersonHobbies { get; set; } = new List();
}