Hi,

I have a table called Album, with a Primary Key “AlbumId”. This table also has a title field that has a unique constraint defined such that an album with the same title cannot be entered. I also have a table called Media (to hold all of the individual songs) with a Primary Key “MediaId”. I have an intermediate table to relate the two called AlbumMedia, with the Primary Key being a combination of the AlbumId, MediaId, and Order field. Thus there is a 1:n relationship between Album and AlbumMedia. And 1:1 between AlbumMedia and Media.

I have a method to save a collection of Albums. Because there is additional business logic that needs to happen when doing a bulk save, I have a method “BeginSave” that begins a transaction and sets up the entity objects. Ultimately this calls SaveEntityCollection(). After the other business logic runs (which requires knowing the AlbumId for each album), the code then calls a method to commit the transaction (or rollback if any problem). In the commit/rollback methods after calling the appropriate method, I call Dispose() on the adapter object and also set the adapter object to null.

I have unit tests to confirm the commit and rollback work as expected. Each setup the same collection of two Albums (with songs) and call the “BeginSave” method on the collection. One test then calls Commit, and then retrieves the data from the database and asserts the album/songs are as expected. The other test calls Rollback, and then asserts that no album/song data is saved to the database. The testing framework is setup such that each test deletes all data from all associated tables so that each test is completely independent of the others and they all start in the same known state.

My problem has to do with the order the unit tests are run in. The tests run fine by themselves. If I run them as a suite such that the ‘commit’ test is run followed by the ‘rollback’ test, the tests run fine. However if the test order is such that the ‘rollback’ test runs followed by the ‘commit’ test, then the ‘commit’ test fails with a unique constraint exception on the Album title, and I don’t understand why. I’ve set a breakpoint in the debugger between the two tests and I’ve confirmed that there is no data in the database between the two tests. So I don’t understand why the system thinks that the album already exists in the database. I’m not sure if I am doing something incorrectly in the code, or this is a weird anomaly with running in the unit test framework such that something is left around between the two tests. Each test will start by creating a brand new AlbumEntity object (and the resulting entity objects), so I don’t see how something could be leftover. The stacktrace is posted at the end of this email.

My environment is LLBLGen Pro 2.6 (using the adapter framework), VS2008, .Net 3.5 framework, and NUnit 2.4.8.

Any pointers would be greatly appreciated. Thanks, Beth

Stacktrace:

The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
An exception was caught during the execution of an action query: Violation of UNIQUE KEY constraint 'UQ_Album_Title'. Cannot insert duplicate key in object 'dbo.Album'.
The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
   at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse, Boolean autoCommit)
   at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntityCollection(IEntityCollection2 collectionToSave, Boolean refetchSavedEntitiesAfterSave, Boolean recurse)
   at Com.Ims.Cis.Cmt.CmtBl.AlbumCollection.SaveEntityCollection(EntityCollection`1 entityCollection) in C:\content\ContentTrunk\ContentManagementTool\src\Com.Ims.Cis.Cmt.CmtBl\AlbumCollection.cs:line 72
   at Com.Ims.Cis.Cmt.CmtBl.AlbumCollection.BeginSave(String connectionString, String userId, List`1 albumsToAdd) in C:\content\ContentTrunk\ContentManagementTool\src\Com.Ims.Cis.Cmt.CmtBl\AlbumCollection.cs:line 35
TestCase 'Com.Ims.Cis.Cmt.CmtBlDatabaseTests.AudioCollectionDatabaseTestFixture.C_InsertAndRetrieve_AlbumCollection'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of an action query: Violation of UNIQUE KEY constraint 'UQ_Album_Title'. Cannot insert duplicate key in object 'dbo.Album'.
The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  ----> System.Data.SqlClient.SqlException : Violation of UNIQUE KEY constraint 'UQ_Album_Title'. Cannot insert duplicate key in object 'dbo.Album'.
The statement has been terminated.
    at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse, Boolean autoCommit)
    at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntityCollection(IEntityCollection2 collectionToSave, Boolean refetchSavedEntitiesAfterSave, Boolean recurse)
    C:\content\ContentTrunk\ContentManagementTool\src\Com.Ims.Cis.Cmt.CmtBl\AlbumCollection.cs(72,0): at Com.Ims.Cis.Cmt.CmtBl.AlbumCollection.SaveEntityCollection(EntityCollection`1 entityCollection)
    C:\content\ContentTrunk\ContentManagementTool\src\Com.Ims.Cis.Cmt.CmtBl\AlbumCollection.cs(46,0): at Com.Ims.Cis.Cmt.CmtBl.AlbumCollection.BeginSave(String connectionString, String userId, List`1 albumsToAdd)
    C:\content\ContentTrunk\ContentManagementTool\test\Com.Ims.Cis.Cmt.CmtBlDatabaseTests\AudioCollectionDatabaseTestFixture.cs(71,0): at Com.Ims.Cis.Cmt.CmtBlDatabaseTests.AudioCollectionDatabaseTestFixture.C_InsertAndRetrieve_AlbumCollection()
    --SqlException
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()

Hi Beth,

Could you please attach the code file where you have such tests?

Hopefully this is enough.

Thanks, Beth

Using SQL profiler, can you see the transaction being rolled back when you run the tests in the order that it fails?

It sounds very much like the transaction is still "dangling" open...

The other tools in SQL management studio should also be able to tell you if anything is still holding a lock open on the table.

Matt

Minor detail I forgot to mention - I am using SQL Server Express 2005, which does not come with a profile tool. I've done a google search and I see there are a couple of free profile tools for sql express - I'll try to give one of those a try later today.

It does sound like something is leftover in the transaction. However I have stepped through the debugger and confirmed that either the commit or rollback is called on the transaction. And following that call is a call to Dispose() on the adapter, and also setting the adapter to null. The following test then creates a new adapter, and creates a new transaction.

Thanks, Beth

Could you tell us what two methods are you running that fires this behavior? Also, could you give us some script that create your database schema and some data to test?

One thing to understand is that unittests don't have an order. If you want two tests to run in a given order, make 1 test and call both methods. Your TearDown method has to assure that all data is gone so a next test will indeed have a clean db. We use this setup ourselves in our unittests and have no problems.

The thing you forgot to attach was the most important thing: the code of Rollback and Commit and also BeginTransaction. What's done there? Do you start a TransactionScope transaction? What's the isolation level? Do you share an adapter or not?

Attached are scripts to create the database schema and insert some static data. Note the database name in these scripts is a parameter - you'll have to update to use whatever database name you want. Also in the unit test code, you'll have to update the database connection string to match your environment. Again, we are using sql server express 2005.

A note about our unit tests with the database. Our framework is such that the build server recreates the database at the beginning of the testing (drop existing, create, create schema, insert static data). So the database/schema is only created once per suite of unit tests. Then each individual test is responsible for deleting any data it puts in the database such that the state of the database is the same at the beginning of each test. The delete portion during cleanup simply makes sql calls to 'DELETE FROM..." the relevant tables.

And yes, I agree that there should be no order involved in the unit tests - each should be entirely self composed. I only started looking at the order when I found that the tests pass when run individually, but fail when run as a suite. Usually that is the result of either a test not cleaning up after itself, or some static variable leftover with some state from the previous test. I can force the order to be something specific if I name the tests in a certain manner (at least with NUnit) - this is only something I am doing to expose the failure.

To follow the code, use the AudioCollectionNewTestFixture, which contains the two tests (the other tests are Ignored). One test is responsible for testing the insert functionality by calling the methods AudioCollection:BeginSave() and AudioCollection:CommitSave(). The other test is responsible for testing the rollback functionality by calling the methods AudioCollection:BeginSave() and AudioCollection:Rollback(). Again, the test data is included in the unit test class (although you will need the AudioTestData class in this new zip file).

If you look at the AudioCollection object, BeginSave() method, it creates a new adapter each time (although it is needed to be a member variable). It starts a transaction as IsolationLevel.Serializable. It then runs through the business objects and creates the entity objects and puts them into an EntityCollection. Then the adapter.SaveEntityCollection() method is called on the EntityCollection with the parameters set to refetch after save and recurse.

The CommitSave() method simply calls the Commit() method on the adapter, then Disposes of the adapter, and sets it to null.

The Rollback() method simply calls the Rollback() method on the adapter, then Disposes of the adapter and sets it to null.

Since each test creates a new AudioCollection each time, and the adapter is created each time as well, I don't see how the Rollback() of the first test (Rollback) is somehow affecting the ability to Commit in the 2nd test such that there is a unique constraint violation in the database.

Thanks again for your help, Beth

You have attached a lot of code, but all we need to really look at is the code inside BeginSave, CommitSave and Rollback methods.

Also do you have SQL 2005 SP1 installed. Anyway I think you should test this on another database to make sure its nothing related to the SQL Express 2005.

I actually have SQL Server Express 2005 SP2 installed.

I do not have SQL Server 2005, but I do have SQL Server 2000. I was able to reproduce the problem with SQL Server 2000, so this is not an issue specific to sql server 2005 express.

Using sql server 2000 was a good thing though as it allowed me to use the profiler. I did not see anything to cause alarm in the profile though - running the two tests within the NUnit framework does indeed use two different transactions. The profile shows insert stmts into the related tables and then finally the insert into the Album table. When running the test suite such that the rollback test is followed by the commit test, the failure happens in the commit test when inserting the Album data.

I am now convinced that this problem is due to the unit testing framework and there is something about running these two tests in the same process. If I run in the debugger and stop between the two tests and run the same insert stmt that fails in query analyzer it runs just fine. If there were truly an issue with the previous rollback not entirely cleaning itself up, I would not be able to do that (nor would I be able to run the tests in that order individually).

I am just going to move on, unless someone can convince me that there is something else to investigate.

Thanks a lot for your tips and suggestions. Beth

I am now back to suspecting there is something wrong with the way we are either setting up our entity objects or using SaveEntityCollection.

To make a long story short, I attempted to rewrite my unit tests to get rid of the unique constraint error. However I found the problem still existed, and the unique constraint just exposed the problem. If I change the data to be something different, I don't get the unique constraint error, however my test not only inserts the data from the "commit" test (2 albums), but also the data from the "rollback" test (2 other albums). Even though my entity collection only has the two commit albums, after I commit the transaction there are 4 albums in my database.

The code calls SaveEntityCollection with the recurse flag set to true and the refetch flag also set to true. I've experimented with setting the recurse flag to false. When I run the same test, I now only get the 2 commit albums saved, but I no longer get any related data in other tables in the database.

So there is something going on with SaveEntityCollection and recurse true. Our group is pretty new to llblgen pro, so there certainly could be something wrong with the way we are using the tool and setting up our entities. However I have stepped through the code enough to know that my entity collection only has 2 items, and I have only created two AlbumEntity() objects. And at the beginning of each test I create a new adapter object. I have also stepped through the code and seen that the adapter object, when first created, is not in a transaction, and then after I commit or rollback, the fields show it is no longer in a transaction.

I tried searching the llblgen help for information on SaveEntityCollection(), but there really isn't much in there that goes into detail. Is there some other documentation that describes in detail and shows examples of using that method?

I have also started looking at the tracing capabilities of llblgen and have turned that feature on while running my unit tests. However at this time I don't really know what I am looking for. Right now I'm using a trace level of 3, I will give the verbose level 4 a try and see what I get. If anyone can give me pointers as to what I should be looking for with regards to SaveEntityCollection, that would be great.

Thanks, Beth

I've changed my trace level to 4 and see some interesting output. I've attached a snippet from the output.

The first test, "rollback" inserts one album ("Different Album") that contains one song ("Different Song") and the user id is "rollbackUser". SaveEntityCollection is called with that AlbumEntity object and recurse is true. The transaction is created first (serializable), and then there is a separate call to rollback the transaction. The rollback disposes of the adapter object.

The second test, "commit" inserts two albums with user id "commitUser". Thus the EntityCollection has two items of type AlbumEntity. A new adapter object is created, the transaction is created, and the SaveEntityCollection method is called. This time Commit is called on the adapter. This actually inserts the album (and song) from the rollback test such that there are now three albums in the database.

The snippet is from the second test only. I don't know what it means, but I can see that there are three different AlbumEntities being inserted into the insert queue. And that can't be good. The guids are given, and I can reference the guid from "Different Album" as the same guid used when inserting the data in the first test.

How can I find out why "Different Album" is included in the insert queue?

Thanks, Beth

How can I find out why "Different Album" is included in the insert queue?

I don't know, search your entire solution for it. I could find it in the code you have attached before, while "Red Hot Chili..." album was there.

Anyway it seems not to be an issue with LLBLGen Pro.

If you want to know which entities are included in a save, you can calculate the save queues from the entity graph. Any entity in the entity object graph in a unit of work is included in the process. Either use the ObjectGraphUtils class (in ORMSupportClasses) to calculate the save queues or use the UnitOfWork's ConstructSaveProcessQueues method and then call GetInsertQueue and GetUpdateQueue to get the list of entities to process.

The thing might be that you have an entity referenced by another entity X and X is added to a unitofwork and the referenced entity therefore is also considered. If it has dirty fields, it will be saved.

As the code you specified is rather big, it's a huge undertaking to find the spot where things go wrong. With unit-tests, please stay on the safe side: dont re-use data structures/objects unless you are absolutely sure they're stateless or can't change.

Yes, the problem is indeed an entity reference problem. I have been able to isolate the source of the problem to the referenced genre entity used by both albums.

In the first rollback test, I have "Different Album" that references a Genre entity object "pop". I rollback that transaction.

Then I run the commit test with "Red Hot..." that also references the pop genre entity object. In fact if I stop in the debugger when creating this album object (before I set the genre) and look at the pop genre object, it does indeed have a reference to an album already - and that album is the "Different Album" from the rollback test. And that is the root of my problem. If I change the genre to something else, the problem goes away.

So I can fix the unit test code such that I can get past this problem. However I would still like to understand a bit more what is going on so I can be convinced this won't be a problem in our production system.

The genres are fixed, thus the user will not ever be able to add a new genre, they must always select from the given list that was generated from the database data. If I have a user that attempts to create a new album, "Album One" and selects a genre of pop. Now my AlbumEntity object sets the primary genre to the PopPrimaryGenre entity. For some strange reason there is a problem when saving the data and the transaction gets rolled back. Now the user decides to create "Album Two", also with pop primary genre. This creates a new AlbumEntity object, but sets the genre to the same PopPrimaryGenre object. If the user now successfully saves Album Two, it seems like Album One will also end up saved to the database. I can probably test this theory out to see if it is an issue, but I thought I'd see what you thought.

Is there something specific that the code should be doing when it needs to set a relation from one entity object to an existing one already in the database? Especially in terms when a transaction gets rolled back such that the existing entity no longer thinks it has a relation to the entity that was rolled back?

Thanks, Beth

If I have two entities, Customer and Order, and they have a 1:n relationship, I can do: myOrder.Customer = myCustomer;

and this under the hood does: myCustomer.Orders.Add(myOrder);

Now, if I keep myCustomer around, the orders assigned to that customer will also kept around. This is not likely a problem in many cases, however in some cases it can be a problem, namely when some entities are cached and are on the PK side of a relationship(here, customer is on the PK side of a 1:n relationship).

You can safely hide the field mapped onto the relationship on the PK side. So I guess you have: Genre and Album, and they have a 1:n relationship. Then you can hide the field in Genre on the relationship Genre-Album. This is likely the field 'Albums' in Genre (haven't looked at your code personally)

Of course, this has the side effect that you don't have the 'Albums' field in genre to navigate to the list of albums of that genre.

Though you can still assign an album to a genre, as on that side there's a field mapped onto the relationship, so I can still do: myAlbum.Genre = myGenre;

as there's no field mapped onto Genre-Album in Genre, this won't add myAlbum to Genre.Albums because Genre.Albums isn't there. However it does allow you to save myAlbum properly. Of course, as there's no field mapped onto Genre-Album, saving Genre won't save associated albums because there aren't any (Genre has no notion of Album)

So it's a tradeoff. if you want to cache Genre instances, you have to take this into account. if you don't cache genre, you can ignore it and adjust the test with code which cleans up genre (as in: uses a new instance).

Yes, you are correct in that between Genre and Album there is a 1:n relationship. The Album table has a foreign key to the Id field in the Genre table.

I opened up the llblgen database project and saw how to hide fields (I didn't know you could do that). I haven't yet tried this, but based on what you've said and the behavior I'm seeing, I believe if I did hide the Albums field in Genre, my problem would go away.

I will have to do some thinking on the overall database design as to whether this makes sense or not. My initial thought is that we don't actually need to traverse from genres to albums, and so hiding this field may be acceptable.

Thanks again for your help. I have certainly learned a lot this past week. Beth