1

I have a many to many relationship between User and Survey, the relationship is bi-directional, so when adding a new survey for a list of users, I need to do this:

foreach(User user in users)
{
    survey.Users.Add(user);
    user.Survey.Add(survey);
}

Iterating through all of the users looks overkill to me as there can be many users.

Isn't there a better way? Is it possible to have a unidirectional many to many?

Rachel Gallen
  • 27,943
  • 21
  • 72
  • 81
WriteEatSleepRepeat
  • 3,083
  • 3
  • 33
  • 56

2 Answers2

1

Ah, your problem is your database design. Many-to-many relationships will cause you major headaches like this if they aren't decomposed :).

Solution: Create a new table called UserSurvey, with a Primary Key and then a UserID field and a SurveyID field, like so:

----------------------------------------
| UserSurveyID | PrimaryKey            |
| UserID       | Foreign Key to User   |
| SurveyID     | Foreign key to Survey |
----------------------------------------

Then all you need to do is query that table for what you need.

What I would also do is maybe in the nHibernate UserSurvey class, put the actual User property and the Survey property to save you additional querying once you have your UserSurvey objects.

Mathew Thompson
  • 55,877
  • 15
  • 127
  • 148
  • what you describe is what the default HasManyToMany mapping creates. I am not sure I understand your answer... – WriteEatSleepRepeat Jan 21 '13 at 12:05
  • @OPCoder I'm not **too familiar** with Fluent, but I believe the top answer to this question achieves that http://stackoverflow.com/questions/707708/fluent-nhibernate-how-to-map-the-foreign-key-column-as-a-property – Mathew Thompson Jan 21 '13 at 12:09
  • thanks for your answer, but it doesn't help me too much. I do not know how to implement a many to many in the way you described. – WriteEatSleepRepeat Jan 21 '13 at 12:23
  • @OpCoder you simply just add that extra table in, then remove the Surveys collection from the User object and the Users collection from the Survey object, add the User object and the Survey object to the UserSurvey class and you can add and remove using the UserSurvey class. – Mathew Thompson Jan 21 '13 at 12:27
  • how would adding a new survey for a list of users look like? wouldn't still require iterating through all users in order to add to this join table? isn't the same thing as I do now? – WriteEatSleepRepeat Jan 21 '13 at 12:36
  • The `foreach` would stay, but you wouldn't be loading the collections from the database for the `survey.Users` and the `user.Surveys` collections. It would simply be a standard save of a new object for each of the users. – Mathew Thompson Jan 21 '13 at 12:41
  • OK, thanks, I will try to implement what you suggested. All the info I got give example in the way I am doing and not using a manual join table. Are there any cons on this? – WriteEatSleepRepeat Jan 21 '13 at 13:00
  • about your comment that survey.Users and user.Surveys would load collections from database: I see that using ExtraLazyLoad option, it does not do this. – WriteEatSleepRepeat Jan 21 '13 at 14:41
  • @OpCoder Ah really? It still is bad database design though, but it's up to you if you want to keep it :) – Mathew Thompson Jan 21 '13 at 14:42
  • can you please explain why is bad database design? Your table is exactly the same table which is automatically generated by NHibernate. It's the same database design. The only difference is the referencing in the entity – WriteEatSleepRepeat Jan 21 '13 at 15:46
  • Ah, It's automatically generated by NHibernate? I didn't know that. Avoid putting the collections on the classes though, as that has major overhead when you're using them (loads of SQL calls). – Mathew Thompson Jan 21 '13 at 17:13
  • I know what you mean, but the collections wont get loaded if you know what you are doing. NHibernate is still a black art in my opinion :( so difficult – WriteEatSleepRepeat Jan 21 '13 at 17:46
  • @OpCoder I totally agree, it's too easy to hammer the database because everything is abstracted away. That's one of the major pitfalls of both NHibernate and Entity Framework. – Mathew Thompson Jan 21 '13 at 17:56
  • I am not sure but I don't even need to iterate trough the users when creating the Survey. I thin it works if I just set the users collection to the survey's Users property. – WriteEatSleepRepeat Jan 21 '13 at 20:59
  • @OpCoder Yeah can you just do `survey.Users = users`? – Mathew Thompson Jan 21 '13 at 21:41
  • yes. no need to iterate trough all the users and survey to each of their Surveys collection. – WriteEatSleepRepeat Jan 22 '13 at 14:22
0

Yes, you can have a unidirectional many-to-many. There's nothing special you need to do to achieve this, just do not map the other side of the relationship.

If you need to maintain an in-memory representation of the relationship, then you need to add the references to both sides of the relationship as you're doing. This isn't overkill, you have to associate a set of users with a survey so at some point you have to loop through the set of users and make that association.

If you want to avoid loading the survey's user collection, you can check if the collection is initialized before accessing it:

foreach(User user in users)
{
    user.Survey.Add(survey);
    if (NHibernateUtil.IsInitialized(survey.Users)
    {
        survey.Users.Add(user);
    }
}
Jamie Ide
  • 48,427
  • 16
  • 81
  • 117