Fluent NHibernate one-to-many insert/delete problem

Question

I am trying to add and remove elements from a list mapped as .HasMany(), but nHibernate executes some weird queries on this simple scenario:

if (Profile.Artists.Any(x => x.Artist == artist))
{
    Profile.Artists.Remove(Profile.Artists.Single(x => x.Artist == artist));
}
else
{
   Profile.Artists.Add(new Artist { Artist = artist, User = User.Current.ID });
}

This method is executed withing a transaction. What I get is a series of SELECT statements for the whole collection:

SELECT ... WHERE artis0_.User=?p0;?p0 = 5

And so on, and then nHibernate tries to update Profile first and Artists second. Though Artists can really only be either deleted or inserted (note: with Inverse() Artists only get inserted and never deleted).

UPDATE user_profile SET UserFK = ?p0 ...

UPDATE user_artists SET User = null WHERE User = ?p0 AND User = ?p1 AND Artist = ?p2;?p0 = 5, ?p1 = 5, ?p2 = 16

Mapping is done like this:

mapping.HasMany<Artist>(x => x.Artists)
                .KeyColumn("User")
                .Inverse()
                .Cascade.All();

None of this makes any sense to me, especially the series of SELECT statements. What am I doing wrong here?

Answer 1

If you want to NHibernate to delete orphans use cascade mode all-delete-orphan

 mapping.HasMany<Artist>(x => x.Artists)
            .KeyColumn("User")
            .Inverse()
            .Cascade.AllDeleteOrphans();

Answer 2

Your selects are being fired because of your Any call, which is iterating a lazy-loaded collection loading each item as it goes.

If you really need to iterate that collection, then you either live with it being lazy-loaded and having the selects, or you eager load it.

Answer 3

Apparently the Profile object was stored in the web Session and the nHibernate Session was stored in the Items, so naturally the Profile became detached and was being updated, which also updated its children, triggering the full reload of the collection.

The solution was to simply reload Profile on each request.