mardi 29 avril 2014

Cascade delete dans NHibernate Fluent en utilisant ID Composite sur une relation HasMany avec contrainte d'intégrité - Stack Overflow


I have an error, and no matter how much I Google it I cannot find a fix for it. I have read and tried to solve it using the following topics. These are the closest I've found related to my issue:



DB Schema


Four tables are involved in my mapping: Member, Vehicle, MemberVehicles and LastUsedVehicles.



  • Tables Member and Vehicle are business objects in the application.

  • Table MemberVehicles is an association table (many-to-many) with no extra columns. This relationship works with no issues.

  • Table LastUsedVehicles has a composite key consisting of Vehicle_uid and Member_uid, which are foreign keys to those corresponding tables. The table also has an extra DateLastUsed column to store the timestamp. The mapping of this table is my issue.


The Problem


When I delete a Member, I need all of his Vehicles to also be deleted (this works fine - the records are deleted from the many-to-many table), as well as all of the associated LastUsedVehicles records (this is the part that does not work).


When I execute this code...


    try
    {
        using (IUnitOfWork unitOfWork = unitOfWorkFactory.Create())
        {
            Member member = nHibernateMemberRepository.GetMemberBySomeMethod(x,y)
            if (member != null)
            {
                if (nHibernateMemberRepository.Delete(unitOfWork, member))
                {
                    unitOfWork.Commit();
                }
            }
        }
    }
    catch (Exception)
    {
        throw;
    }

... I receive this error:



System.Exception : Attempt to commit during UnitOfWork failed; transaction was rolled back due to the following error: The given key was not present in the dictionary. ----> System.Collections.Generic.KeyNotFoundException : The given key was not present in the dictionary.



I do not receive an error when the LastUsedVehicles table is empty.


Mappings


Member


public class MemberMap : ClassMap<Member>
{
    public MemberMap()
    {
        Table("member");

        HasManyToMany<Vehicle>(x => x.Vehicles) // working fine
            .Table("member_vehicles")
            .ParentKeyColumn("MEMBER_UID")
            .ChildKeyColumn("VEHICLE_UID")
            .Inverse()
            .Cascade.SaveUpdate()
            .Cascade.AllDeleteOrphan();

        HasMany(x => x.LastUsedVehicleses)
            //.Table("LASTUSED_VEHICLES")
            .KeyColumn("MEMBER_UID")
            .Fetch.Select()
            //.KeyColumns.Add("VEHICLE_UID")
            //.KeyColumns.Add("MEMBER_UID")
            .Inverse()
            .Cascade.AllDeleteOrphan();
    }
}

Vehicle


    public class VehicleMap : ClassMap<Vehicle>
    {
        public VehicleMap()
        {
            Table("vehicles");

            HasManyToMany(x => x.Members) // working fine
                .Table("member_vehicles")
                .ParentKeyColumn("VEHICLE_UID")
                .ChildKeyColumn("MEMBER_UID")
                .Not.LazyLoad()
                .Cascade.SaveUpdate();

            HasMany(x => x.LastUsedVehicles)
                .KeyColumn("VEHICLE_UID")
                .Inverse()
                .Fetch.Select()
                .Cascade.AllDeleteOrphan();

        }
    }

LastUsedVehicle


    public class LastUsedVehiclesMap : ClassMap<LastUsedVehicles>
    {
        public LastUsedVehiclesMap()
        {
            Table("lastused_vehicles");

            CompositeId()
                .KeyReference(x => x.Vehicle, "VEHICLE_UID")
                .KeyReference(x => x.Member, "MEMBER_UID");

            Map(x => x.DateLastUsed)
                .Column("DATELASTUSED")
                .Not.Nullable();

            // Things that I tried
            //References(x => x.Member)
            //    .Fetch.Select()
            //    .Cascade.None()
            //    .Column("MEMBER_UID");
            //
            //References(x => x.Vehicle)
            //    .Fetch.Select()
            //    .Cascade.None()
            //    .Column("VEHICLE_UID");

            //Version(x => x.DateLastUsed)
            //    .Column("DATELASTUSED")
            //    .Not.Nullable();

        }
    }

Classes


Member


public class Member : PrimaryKeyBase
{
    private readonly Iesi.Collections.Generic.ISet<Vehicle> _vehicles;
    private Iesi.Collections.Generic.ISet<LastUsedVehicles> _lastUsedVehicles;

    public Member()
    {
        _vehicles = new HashedSet<Vehicle>();
        _lastUsedVehicles = new HashedSet<LastUsedVehicles>();
    }

    // ... GetHashCode ...

    public virtual Iesi.Collections.Generic.ISet<Vehicle> Vehicles
    {
        get { return _vehicles; }
    }

    public virtual void AddVehicle(Vehicle vehicle)
    {
        vehicle.AddMember(this);
        _vehicles.Add(vehicle);
    }

    public virtual Iesi.Collections.Generic.ISet<LastUsedVehicles> LastUsedVehicleses
    {
        get { return _lastUsedVehicles; }
        set { _lastUsedVehicles = value; }
    }
}

Vehicle


public class Vehicle : PrimaryKeyBase
{
    private Iesi.Collections.Generic.ISet<Member> _members;
    private Iesi.Collections.Generic.ISet<LastUsedVehicles> _lastUsedVehicles;

    public Vehicle()
    {
        _members = new HashedSet<Member>();
        _lastUsedVehicles = new HashedSet<LastUsedVehicles>();
    }

    public virtual Member Member
    {
        get { return _members.FirstOrDefault(); }
    }

    public virtual IEnumerable<Member> Members
    {
        get { return _members; }
    }

    public virtual void AddMember(Member member)
    {
        _members.Add(member);
    }

    public virtual void AddLastUsedVehicle(LastUsedVehicles lastUsedVehicles)
    {
        lastUsedVehicles.Vehicle = this;
        lastUsedVehicles.Member = this.Member;
        _lastUsedVehicles.Add(lastUsedVehicles);
    }

    public virtual void ClearLastUsedVehicles()
    {
        _lastUsedVehicles.Clear();
    }

    public virtual LastUsedVehicles LastUsedVehicle
    {
        get { return _lastUsedVehicles.FirstOrDefault(); }
    }

    public virtual IEnumerable<LastUsedVehicles> LastUsedVehicles
    {
        get { return _lastUsedVehicles; }
    }
}

LastUsedVehicles


    public class LastUsedVehicles : CompositeKeyBase
    {
        public virtual Vehicle Vehicle { get; set; }
        public virtual Member Member { get; set; }
        public virtual DateTime DateLastUsed { get; set; }

        public virtual bool Equals(LastUsedVehicles other)
        {
            if (ReferenceEquals(null, other)) return false;
            if (ReferenceEquals(this, other)) return true;
            return Equals(other.Vehicle, Vehicle) && Equals(other.Member, Member);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != typeof(LastUsedVehicles)) return false;
            return Equals((LastUsedVehicles)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((Vehicle != null ? Vehicle.GetHashCode() : 0) * 397)
                    ^ Member.GetHashCode()
                    ^ DateLastUsed.GetHashCode();
            }
        }

    }

Debugging Info


To make it clearer, I'm also attaching some SQL queries and their results.


// Delete a member
delete from member m where account = 'X'; // FAIL - Child record found - The member has vehicles - So makes sense

// Delete all vehicles from a member from vehicles table
delete from vehicles mv where mv.vehicle_uid =
(select mv.vehicle_uid from member_vehicles mv where member_uid = (
select mm.member_uid from member mm where account = 'X')); //

// Delete all vehicles from a member from association table
delete from member_vehicles mv where member_uid = (
select mm.member_uid from member mm where account = 'X');

// Delete data on LASTUSED_VEHICLES - works
delete from LASTUSED_VEHICLES lv
where lv.vehicle_uid =
  (select mv.vehicle_uid from membervehicles mv where member_uid = (
          select mm.member_uid from member mm where account = 'X'));

Once the table LASTUSED_VEHICLES is empty, I can delete a member.



Sorry for the length of the post but this scenario seems to be quite unique. Thanks for the attention.



I have an error, and no matter how much I Google it I cannot find a fix for it. I have read and tried to solve it using the following topics. These are the closest I've found related to my issue:



DB Schema


Four tables are involved in my mapping: Member, Vehicle, MemberVehicles and LastUsedVehicles.



  • Tables Member and Vehicle are business objects in the application.

  • Table MemberVehicles is an association table (many-to-many) with no extra columns. This relationship works with no issues.

  • Table LastUsedVehicles has a composite key consisting of Vehicle_uid and Member_uid, which are foreign keys to those corresponding tables. The table also has an extra DateLastUsed column to store the timestamp. The mapping of this table is my issue.


The Problem


When I delete a Member, I need all of his Vehicles to also be deleted (this works fine - the records are deleted from the many-to-many table), as well as all of the associated LastUsedVehicles records (this is the part that does not work).


When I execute this code...


    try
    {
        using (IUnitOfWork unitOfWork = unitOfWorkFactory.Create())
        {
            Member member = nHibernateMemberRepository.GetMemberBySomeMethod(x,y)
            if (member != null)
            {
                if (nHibernateMemberRepository.Delete(unitOfWork, member))
                {
                    unitOfWork.Commit();
                }
            }
        }
    }
    catch (Exception)
    {
        throw;
    }

... I receive this error:



System.Exception : Attempt to commit during UnitOfWork failed; transaction was rolled back due to the following error: The given key was not present in the dictionary. ----> System.Collections.Generic.KeyNotFoundException : The given key was not present in the dictionary.



I do not receive an error when the LastUsedVehicles table is empty.


Mappings


Member


public class MemberMap : ClassMap<Member>
{
    public MemberMap()
    {
        Table("member");

        HasManyToMany<Vehicle>(x => x.Vehicles) // working fine
            .Table("member_vehicles")
            .ParentKeyColumn("MEMBER_UID")
            .ChildKeyColumn("VEHICLE_UID")
            .Inverse()
            .Cascade.SaveUpdate()
            .Cascade.AllDeleteOrphan();

        HasMany(x => x.LastUsedVehicleses)
            //.Table("LASTUSED_VEHICLES")
            .KeyColumn("MEMBER_UID")
            .Fetch.Select()
            //.KeyColumns.Add("VEHICLE_UID")
            //.KeyColumns.Add("MEMBER_UID")
            .Inverse()
            .Cascade.AllDeleteOrphan();
    }
}

Vehicle


    public class VehicleMap : ClassMap<Vehicle>
    {
        public VehicleMap()
        {
            Table("vehicles");

            HasManyToMany(x => x.Members) // working fine
                .Table("member_vehicles")
                .ParentKeyColumn("VEHICLE_UID")
                .ChildKeyColumn("MEMBER_UID")
                .Not.LazyLoad()
                .Cascade.SaveUpdate();

            HasMany(x => x.LastUsedVehicles)
                .KeyColumn("VEHICLE_UID")
                .Inverse()
                .Fetch.Select()
                .Cascade.AllDeleteOrphan();

        }
    }

LastUsedVehicle


    public class LastUsedVehiclesMap : ClassMap<LastUsedVehicles>
    {
        public LastUsedVehiclesMap()
        {
            Table("lastused_vehicles");

            CompositeId()
                .KeyReference(x => x.Vehicle, "VEHICLE_UID")
                .KeyReference(x => x.Member, "MEMBER_UID");

            Map(x => x.DateLastUsed)
                .Column("DATELASTUSED")
                .Not.Nullable();

            // Things that I tried
            //References(x => x.Member)
            //    .Fetch.Select()
            //    .Cascade.None()
            //    .Column("MEMBER_UID");
            //
            //References(x => x.Vehicle)
            //    .Fetch.Select()
            //    .Cascade.None()
            //    .Column("VEHICLE_UID");

            //Version(x => x.DateLastUsed)
            //    .Column("DATELASTUSED")
            //    .Not.Nullable();

        }
    }

Classes


Member


public class Member : PrimaryKeyBase
{
    private readonly Iesi.Collections.Generic.ISet<Vehicle> _vehicles;
    private Iesi.Collections.Generic.ISet<LastUsedVehicles> _lastUsedVehicles;

    public Member()
    {
        _vehicles = new HashedSet<Vehicle>();
        _lastUsedVehicles = new HashedSet<LastUsedVehicles>();
    }

    // ... GetHashCode ...

    public virtual Iesi.Collections.Generic.ISet<Vehicle> Vehicles
    {
        get { return _vehicles; }
    }

    public virtual void AddVehicle(Vehicle vehicle)
    {
        vehicle.AddMember(this);
        _vehicles.Add(vehicle);
    }

    public virtual Iesi.Collections.Generic.ISet<LastUsedVehicles> LastUsedVehicleses
    {
        get { return _lastUsedVehicles; }
        set { _lastUsedVehicles = value; }
    }
}

Vehicle


public class Vehicle : PrimaryKeyBase
{
    private Iesi.Collections.Generic.ISet<Member> _members;
    private Iesi.Collections.Generic.ISet<LastUsedVehicles> _lastUsedVehicles;

    public Vehicle()
    {
        _members = new HashedSet<Member>();
        _lastUsedVehicles = new HashedSet<LastUsedVehicles>();
    }

    public virtual Member Member
    {
        get { return _members.FirstOrDefault(); }
    }

    public virtual IEnumerable<Member> Members
    {
        get { return _members; }
    }

    public virtual void AddMember(Member member)
    {
        _members.Add(member);
    }

    public virtual void AddLastUsedVehicle(LastUsedVehicles lastUsedVehicles)
    {
        lastUsedVehicles.Vehicle = this;
        lastUsedVehicles.Member = this.Member;
        _lastUsedVehicles.Add(lastUsedVehicles);
    }

    public virtual void ClearLastUsedVehicles()
    {
        _lastUsedVehicles.Clear();
    }

    public virtual LastUsedVehicles LastUsedVehicle
    {
        get { return _lastUsedVehicles.FirstOrDefault(); }
    }

    public virtual IEnumerable<LastUsedVehicles> LastUsedVehicles
    {
        get { return _lastUsedVehicles; }
    }
}

LastUsedVehicles


    public class LastUsedVehicles : CompositeKeyBase
    {
        public virtual Vehicle Vehicle { get; set; }
        public virtual Member Member { get; set; }
        public virtual DateTime DateLastUsed { get; set; }

        public virtual bool Equals(LastUsedVehicles other)
        {
            if (ReferenceEquals(null, other)) return false;
            if (ReferenceEquals(this, other)) return true;
            return Equals(other.Vehicle, Vehicle) && Equals(other.Member, Member);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != typeof(LastUsedVehicles)) return false;
            return Equals((LastUsedVehicles)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((Vehicle != null ? Vehicle.GetHashCode() : 0) * 397)
                    ^ Member.GetHashCode()
                    ^ DateLastUsed.GetHashCode();
            }
        }

    }

Debugging Info


To make it clearer, I'm also attaching some SQL queries and their results.


// Delete a member
delete from member m where account = 'X'; // FAIL - Child record found - The member has vehicles - So makes sense

// Delete all vehicles from a member from vehicles table
delete from vehicles mv where mv.vehicle_uid =
(select mv.vehicle_uid from member_vehicles mv where member_uid = (
select mm.member_uid from member mm where account = 'X')); //

// Delete all vehicles from a member from association table
delete from member_vehicles mv where member_uid = (
select mm.member_uid from member mm where account = 'X');

// Delete data on LASTUSED_VEHICLES - works
delete from LASTUSED_VEHICLES lv
where lv.vehicle_uid =
  (select mv.vehicle_uid from membervehicles mv where member_uid = (
          select mm.member_uid from member mm where account = 'X'));

Once the table LASTUSED_VEHICLES is empty, I can delete a member.



Sorry for the length of the post but this scenario seems to be quite unique. Thanks for the attention.


0 commentaires:

Enregistrer un commentaire