Working with ternary associations

Question

I have a class:

class User
{
    IDictionary<Post, DateTime> SavedPosts { get; set; }
}

This is saved into a Many-To-Many table association [User] -< [Saves] >- [Post]

[Saves] table:

ID_USER (int)
ID_POST (int)
SAVED_ON (DateTime)

Mapping looks like this:

<map name="SavedPosts" table="[SAVES]">
  <key column="ID_USER" />
  <index-many-to-many column="ID_POST" class="Post" />
  <element column="SAVED_ON" />
</map>

If I do this:

 User u = db.Get<User>(1); //get user with ID = 1
 Post p = db.Load<Post>(5); //load post with ID = 5

 //Let's say I know that the post #5 is saved
 DateTime savedon = u.SavedPosts[p];

Nhibernate executes:

 SELECT ID_USER, SAVED_ON, ID_POST FROM [SAVES] WHERE ID_USER = 1

but...If a user has like 100 000 saved posts??!?! I don't want to fetch it all.

So I can do something like this:

 db.CreateQuery(@"select s
                    from User u
                    join u.SavedPosts s
                   where u.ID = :userId
                     and s.index = :postId")
   .SetInt32("userId", u.ID)
   .SetInt32("postId", p.ID)
   .UniqueResult<DateTime>();

And it works ok.

But how can I update or insert SavedPosts? Everytime when I "do something" on the IDictionary SavedPosts it loads all the data (look at the above SQL statement) from the database .

The "do something" means:

u.SavedPosts[p] = DateTime.Now; //for update
u.SavedPosts.Add(p, DateTime.Now); //for insert

So the result I would be satisfied with is:

• One hit on database that gets one on none records from the SAVES table,
• If I get the record then I do update to it,
• If I don't get the record I do insert.

How can I do that?