question

PhamNam-3513 avatar image
0 Votes"
PhamNam-3513 asked DanielZhang-MSFT commented

How to combine one-to-many relationship and many-to-many relationship in Entity Framework Core

My requirement is design a system that: Allow a user to create a post, tag one or more other users in post. So I tried to create 3 classes of entities as below:

 public class User 
     { 
         public int Id { get; set; } 
         public string Username { get; set; } 
         public List<Post> CreatedPosts{ get; set; }.  //To get all posts user created 
         public List<PostTag> PostTags{ get; set; }.   //Use with PostTag table to get all posts user was tagged 
     }  

 public class Post 
     { 
         public int Id { get; set; } 
         public string Title { get; set; } 
         public User Author { get; set; }.  //To set user who created post  
         public List<PostTag> PostTags { get; set; }.  
     } 

 public class PostTag 
     { 
         public int PostId { get; set; } 
         public Post Post { get; set; } 
         public int UserId { get; set; } 
         public User User { get; set; }  //user who tagged in post 
     } 

Because a user can create many post so it will be a one-to-many relationship. A user can be tagged in many posts and many users can be tagged in a post so it will be a many-to-many relationship. So I tried to configure using Fluent API as below:

 public class ApplicationDbContext : DbContext 
     { 
         public DbSet<User> Users { get; set; } 
     
         public DbSet<Post> Posts { get; set; } 
     
         protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 
         { 
                 optionsBuilder.UseSqlServer(@"Server=localhost\SQLEXPRESS;Database=DbRelationshipDB;Trusted_Connection=True;MultipleActiveResultSets=true");
         } 
     
         protected override void OnModelCreating(ModelBuilder modelBuilder) 
         { 
             //Configure One-to-many for: a user can create many posts 
             modelBuilder.Entity<User>() 
                 .HasMany(x => x. CreatedPosts) 
                 .WithOne(x => x. Author) 
                 .IsRequired(); 
     
             //Configure many-to-many for: a post can contains many users and a user can be tagged in many posts 
             modelBuilder.Entity<PostTag>() 
                 .HasKey(t => new { t.PostId, t.UserId }); 
     
             modelBuilder.Entity<PostTag>() 
                 .HasOne(x => x.Post) 
                 .WithMany(x => x. PostTags) 
                 .HasForeignKey(x => x.PostId); 
     
             modelBuilder.Entity<PostTag>() 
                 .HasOne(x => x. User) 
                 .WithMany(x => x.PostTags) 
                 .HasForeignKey(x => x.UserId); 
         } 
     } 

I could add migration successfully but could not update the database. Please help me how to configure this.
I am sorry English not my tongue language.

dotnet-csharpdotnet-entity-framework-core
· 11
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Well, I would say that if your database table schemas are correct in regards to their relationships that should have been done with SSMS and data inserted into the tables using T-SQL in using SSMS to test it out, then you should have been able to scaffold using ef core db first to build the model and the DBcontext. You doing it manually really buys you nothing IMHO. Or you could installed EF Core Power tools and do "reverse engineering" that would have done the same thing buy using the graphical UI and selected the tables to generate the model with the DbContext. By doing this, you would not be asking the questions you are asking the question.

continued.....

0 Votes 0 ·

Also, you should be dependency injecting the connectionstring into the dbcontect and not hard coding it.

https://hackernoon.com/asp-net-core-how-to-use-dependency-injection-in-entity-framework-core-4388fc5c148b

EF migrations is not a Database Administration tool, IMHO, and you should know the basics of DBA as a software developer working with relational databases.

0 Votes 0 ·

Thanks for you reply and suggestion. I used database-first approach, then with a few fixes of generated models I could update database after adding migration. Now next problem is using of many-to-many relationship in EF Core.

0 Votes 0 ·

Hi @PhamNam-3513,
>>Now next problem is using of many-to-many relationship in EF Core.
What is the specific problem? Please explain in detail.
And there are no default conventions available in Entity Framework Core which automatically configure a many-to-many relationship. You need to configure it using Fluent API.
More details please refer to this document.
Best Regards,
Daniel Zhang


0 Votes 0 ·
Show more comments

For querying that's "include" and "Joins" Linq are used. Also EF will auto get the children to the parent within the parent object with EF using navigation properties automatically to pull the child objects in its collection related to the parent within the parent.

To save child objects including the parent on an update, then you place the child object into its collection within the parent and set its state property.

if parent is being added and it has children in its collection to be added, then just saving the parent saves it and the children in their collection.

if the parent is being updated and child has object to be added or updated in the collection, then the appropriate state must be set.

I suggest that you be aware of the disconnected state in using EF, particularly in a Web program that is stateless.


https://www.c-sharpcorner.com/UploadFile/d87001/connected-and-disconnected-scenario-in-entity-framework/

continued..

0 Votes 0 ·

Myself, I don't fool with any of it, and I just use the DAO pattern that's on a per table basis in using EF or any other database persistence technology. it means that a DAO parent does its CRUD for itself, and DAO child to its DAO parent does its CRUD for itself, basically.

https://javarevisited.blogspot.com/2013/01/data-access-object-dao-design-pattern-java-tutorial-example.html

II also use the DTO pattern that means a DTOParent has its DTOChild collection within the DTOParent. And mapping of the DTO between EF entity happens.

https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

The DTO pattern is an abstraction away form the underlying database technology and EF or whatever the database technology is never seen or used directly by the client. All the client sees is the DTO.

You can see the DAO and DTO patterns being used in the MVC and WebAPI projects example solution on Github.

https://github.com/darnold924/PublishingCompany
.

0 Votes 0 ·
Show more comments

hard-code connection string just for quick test run.

0 Votes 0 ·

0 Answers