Problems with updating records through Many-To-Many mappings

Question

I have a problem where I have a many-to-many mappings in my table structure creating headaches when trying to edit a simple record.

Example layout of where I am having problems:

Facilities Many-to-One Locations
Facilities One-to-Many Users
Users Many-to-Many Locations
Users One-to-Many PreviousPasswords

If I make a change to a Facilities record (Change the name field) I get the following error upon save:

collection [Users.PreviousPasswords] was not processed by flush()

Mapping looks like:

public FacilitiesMap()
{
    Table("Facilities");

    Id(x => x.ID);
    Map(x => x.Name);
    HasMany(x => x.Users).KeyColumn("FacilitiesID").Cascade.AllDeleteOrphan().Inverse();
    HasMany(x => x.Locations).KeyColumn("FacilitiesID").Cascade.AllDeleteOrphan().Inverse();
}

public UsersMap()
{
    Table("Users");
    Id(x => x.ID);
    Map(x => x.FirstName);
    Map(x => x.LastName);
    References(x => x.Facilities, "FacilitiesID").ForeignKey("ID");
    HasMany(x => x.PreviousPasswords).KeyColumn("UsersID").Cascade.AllDeleteOrphan().Inverse();
    HasManyToMany<Locations>(x => x.Locations)
         .Schema("Members")
         .Table("UsersToLocations")
         .ParentKeyColumn("UsersID")
         .ChildKeyColumn("LocationsID")
         .LazyLoad()
         .Cascade.SaveUpdate().Inverse();
}

public LocationsMap()
{
    Table("Locations");

    Id(x => x.ID);
    Map(x => x.Name);
    References(x => x.Facilities, "FacilitiesID").ForeignKey("ID");
    HasMany(x => x.Patients).KeyColumn("LocationsID").Cascade.AllDeleteOrphan().Inverse();
    HasManyToMany<Users>(x => x.Users)
         .Schema("Members")
         .Table("UsersToLocations")
         .ParentKeyColumn("LocationsID")
         .ChildKeyColumn("UsersID")
         .Cascade.SaveUpdate();
}

public PreviousPasswordsMap()
{
    Table("PreviousPasswords");

    Id(x => x.ID);
    Map(x => x.Password);
    Map(x => x.DateTime);

    References(x => x.Users, "UsersID").ForeignKey("ID");
}

The only way I can do a successful update to the Facilities record is if I use the following function to get the record before changing and saving it:

public Facilities GetFacility(int id)
{
    return FluentSessionManager.GetSession()
        .CreateCriteria<Facilities>()
        .Add(Expression.Eq("ID", id))
        .SetFetchMode("Users", FetchMode.Eager)
        .SetFetchMode("Locations", FetchMode.Eager)
        .UniqueResult<Facilities>();
}

The problem with this method is that where there are 10,000 users it takes a long time to process this query. Or even worse, if we have 100 location as well, then it takes around 2 minutes to get the one Facilities record to edit.

I am sure there is some kind of issue in the Mapping. Not sure how to fix or even where to start. Any help with this would be greatly appreciated. Thanks in advance.

score 0 · Accepted Answer · answered Jan 08 '13 at 08:12

0

Do you really need all the users for the facility? When you only add users you can use

HasMany(x => x.Users).ExtraLazyLoad();

and to improve the query when really all subcollections are needed

public Facilities GetFacility(int id)
{
    var session = FluentSessionManager.GetSession();

    // ignore the result, we only want to cache the results in the session
    session.CreateCriteria<Facilities>()
        .Add(Expression.Eq("ID", id))
        .SetFetchMode("Users", FetchMode.Eager)
        .Future<Facilities>();

    return session.CreateCriteria<Facilities>()
        .Add(Expression.Eq("ID", id))
        .SetFetchMode("Locations", FetchMode.Eager)
        .FutureValue<Facilities>().Value;
}

answered Jan 08 '13 at 08:12

Firo

30,626
4
55
94

Is the ExtraLazyLoad() a new function in the latest nHibernate build? For some reason I don't have it as an option in the version I am using. Thanks. – Michael D. Kirkpatrick Jan 10 '13 at 15:46
I downloaded the latest version and I now have the "ExtraLazyLoad()" command for the mappings. Problem with your solution... Users is mapped HasManyToMany not HasMany. I have never had a problem with HasMany, or references mappings. Just recently I started using HasManyToMany and It is causing all kinds of heartburn. It turns out that if I don't eager load "Users" because of the HasManyToMany mapping I get all kinds of errors when trying to save. – Michael D. Kirkpatrick Jan 10 '13 at 16:29
then please correct `Facilities One-to-Many Users` to `Facilities Many-to-Many Users` in the question. Nevertheless the queries i posted should get rid of the double cartesian product when eager fetching and will ease the pain a little bit – Firo Jan 11 '13 at 06:38
Thanks. It does ease the pain a bit. Good answer. – Michael D. Kirkpatrick Jan 14 '13 at 14:48

Problems with updating records through Many-To-Many mappings

1 Answers1