Hi People,

I have a many to many relationship User to UserRole to Role. I’m current writing a business method to delete the user and their connected roles.

When I do a delete across the many to many in a unit of work I get the error:

An exception was caught during the execution of an action query: The DELETE statement conflicted with the REFERENCE constraint "FK_UserRole_User". The conflict occurred in database "CS", table "dbo.UserRole", column 'UserID'.

It obvious it is deleting the entity before the many to many connecting table, Is there a way to control the unit of work order? I was hoping it would automatically know which one to delete first but it always seems to try to delete the entity first Any ideas on how to get around this?

Thanks -M

Please post the corresponding code, I'm interested in the part you add entities to the UOW to be deleted.

Hi Walaa,

Sorry I should have posted code to begin with

Here is how I'm trying to doing it:

// Refresh Entity Before Deleting
UserEntity userEntity = GetUserById(userEntity.Id);

// Create New Unit Of work object
UnitOfWork2 uowManyToMany = new UnitOfWork2();

// Add UserRoles To Be Deleted (FK Restraints) 
uowManyToMany.AddCollectionForDelete(userEntity.UserRole);

// Delete Entity
uowManyToMany.AddForDelete(userEntity);

using (IDataAccessAdapter dataAdapter = new DataAccessAdapter())
{
     uowManyToMany.Commit(dataAdapter, true);
}

I'm sure I have just missing somthing. I'm using Adapter, Single classes and visual studio 2005.

Thanks!

The UOW deletes entities in the order you have specified. (FIFO) And the entitycollections are processed before the entities.

So everything should be working fine.

What's the runtimeLibrary version you are using? Also please examine the generated queries to see if they generated in the order you expect or not.

Hi Walaa,

Im not 100% what you mean by runtime Library version do you mean the DLL version? If so I'm using: 2.0.0.60814.

It appears they are going in the wrong order the SQL trace says:

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
A first chance exception of type 'System.InvalidOperationException' occurred in System.dll
Method Enter: CreateDeleteDQ(4)
Method Enter: CreateSingleTargetDeleteDQ(3)
Generated Sql query: 
    Query: DELETE FROM [CS].[dbo].[User] WHERE ( [CS].[dbo].[User].[Id] = @Id1)
    Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 534.

It doesn't even get to the collection it seems to start with the entity first. Everything before this command is a select in the trace.

Thanks -M

Collections are traversed from back to front, and then inserted one at a time in front of the delete queue, which means the userroles have to end up in front of the user entity, in the same order as they're located in the collection. The UoW can't calculate the order of the deletes, because it doesn't have all entities related to the entities in question in memory.

I wonder why you don't add Role entities as well to the UoW?

Also, what does GetByUserid() do? Does it really fetch the UserRole entities? If not, there's no userrole entity to delete, so it won't delete them first.

I second what Frans says, most probably there is no entities in the userEntity.UserRole collection. Please check it out.

Also you may use UOW method GetCollectionElementsToDelete() before you commit to check if there are any collections inside.

Most probably GetUserById() did not use a prefetchPath to fetch the UserRole collection while fetching the UserEntity.

Hi Guys,

Thanks heaps for your help so far, Here is the code for the GetUserById. I dont want to add roles in to the collection for deletion, Becuase I dont actually want to delete the role, Just the user and the link between the user and the role.

        /// <summary>
        /// Get UserEntity By User Id.
        /// </summary>
        /// <param name="userId">The Id of the userEntity to fetch.</param>
        /// <returns>A <see cref="UserEntity"/> instance.</returns>
        public static UserEntity GetUserById(int userId)
        {
            UserEntity entity = new UserEntity(userId);

            using (IDataAccessAdapter dataAdapter = new DataAccessAdapter())
            {
                // Prefetch the users roles.
                IPrefetchPath2 Path = new PrefetchPath2((int)EntityType.UserEntity);
                Path.Add(UserEntity.PrefetchPathRoles);
                Path.Add(UserEntity.PrefetchPathCustomers);

                // Fetch The Entity
                dataAdapter.FetchEntity(entity, Path);

            }

            return entity;
        }

When it goes to delete the collection the users roles collection has 1 item in it userEntity.Roles[0].Name = "Client" so it is connected and the data is there. Looking at the database shows there is a new user record, a new userrole record and a role entity all connected together.

Hmm should I be deleting the RolesEntity rather than the UserRoleEntity Connection?

Thanks M

No you shouldn't delete the roles.

If the collection contains data indeed, it should be executed first. I'll do a test.

I already though I had a unittest testing this:

[Test]
public void UnitOfWorkDeleteTest()
{
    // first create an order and a customer entity
    CustomerEntity customer = EntityCreator.CreateNewCustomer(1);
    customer.TestRunId = _testRunID;
    AddressEntity newAddress = EntityCreator.CreateNewAddress(1);
    newAddress.TestRunId = _testRunID;
    customer.VisitingAddress = newAddress;
    customer.BillingAddress = newAddress;

    OrderEntity order1 = new OrderEntity();
    order1.Customer = customer;
    order1.TestRunId = _testRunID;
    order1.OrderDate = DateTime.Now;
    OrderEntity order2 = new OrderEntity();
    order2.Customer = customer;
    order2.TestRunId = _testRunID;
    order2.OrderDate = DateTime.Now;

    using( DataAccessAdapter adapter = new DataAccessAdapter())
    {
        // save it 
        Assert.IsTrue(adapter.SaveEntity(customer, true));

        int customerId = customer.CustomerId;

        // now delete the bunch using a unit of work object.
        UnitOfWork2 uow = new UnitOfWork2();

        uow.AddCollectionForDelete(customer.Orders);
        Assert.IsTrue(uow.AddForDelete(customer));
        Assert.IsTrue(uow.AddForDelete(newAddress));
        uow.Commit(adapter, true);

        CustomerEntity refetchedCustomer = new CustomerEntity(customerId);
        Assert.IsFalse(adapter.FetchEntity(refetchedCustomer));
        Assert.AreEqual(EntityState.OutOfSync, refetchedCustomer.Fields.State);
    }
}

This succeeds. It doesn't matter when I add the collection, before or after the single entities.

THe entities are refetched after the save, so they're simply fetched entities. So I can't reproduce it.

Hi Otis,

I just playing with it now. It seems the collection delete just isn't being executed, I removed the code deleting the entity. It doesn't delete the collection at all, Is there something I'm missing to tell it to delete the collection?

Thanks M

Okay Solved It

The problem was the function being used to fetch the user entity wasn't fetching the UserRoles Table but rather just the roles table. Thanks for you help guys my stupid mistake!

Thanks M