Understanding many to many relationships and Entity Framework

Question

I'm trying to understand the Entity Framework, and I have a table "Users" and a table "Pages". These are related in a many-to-many relationship with a junction table "UserPages". First of all I'd like to know if I'm designing this relationship correctly using many-to-many: One user can visit multiple pages, and each page can be visited by multiple users..., so am I right in using many2many?

Secondly, and more importantly, as I have understood m2m relationships, the User and Page tables should not repeat information. I.e. there should be only one record for each user and each page. But then in the entity framework, how am I able to add new visits to the same page for the same user? That is, I was thinking I could simply use the Count() method on the IEnumerable returned by a LINQ query to get the number of times a user has visited a certain page.

But I see no way of doing that. In Linq to Sql I could access the junction table and add records there to reflect added visits to a certain page by a certain user, as many times as necessary. But in the EF I can't access the junction table. I can only go from User to a Pages collection and vice versa.

I'm sure I'm misunderstanding relationships or something, but I just can't figure out how to model this. I could always have a Count column in the Page table, but as far as I have understood you're not supposed to design database tables like that, those values should be collected by queries...

Please help me understand what I'm doing wrong...

Answer 1

You are doing it right.

In the Entity Data Model (EDM) Many-To-Many relationships can be represented with or without a join table, depending on whether it contains some additional fields. See the article below for more details.

---

In your case, the User entity will directly reference a collection of Page entities and vice versa, since your model doesn't include a mapping for the User_Page join table.

In order to add visits to a certain page on a user you could for example do something like:

using (var context = new YourEntityModelObjectContext())
{
     var page = context.Pages.FirstOrDefault(p => p.Url == "http://someurl");
     var user = context.Users.FirstOrDefault(u => u.Username == "someuser");

     user.Pages.Add(page);

     context.SaveChanges();
}

Or you could do it from the other side of the relation:

using (var context = new YourEntityModelObjectContext())
{
     var page = context.Pages.FirstOrDefault(p => p.Url == "http://someurl");
     var user = context.Users.FirstOrDefault(u => u.Username == "someuser");

     page.Users.Add(user);

     context.SaveChanges();
}

In both cases a new record will be added to the User_Page join table.

If you need to retrieve the number of pages visited by a particular user you could simply do:

using (var context = new YourEntityModelObjectContext())
{
     var user = context.Users.FirstOrDefault(u => u.Username == "someuser");
     var visitCount = user.Pages.Count;
}

Related resources:

• Many to Many Relationships in the Entity Model