Hi!

I've got a transaction in which I'm creating a user and adding the user to a role. The addition of the user to the role is within a different method that creates it's own DataAccessAdapter and doesn't re-use the DataAccessAdapter that is in the method that has the transaction. I'm using the ReadCommitted isolation level for the transaction and everything goes well till the FetchEntityCollection within the method that I'm calling. The exception message that I get is:

"An exception was caught during the execution of a retrieval query: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

Here are more of the specifics:

DataAccessAdapter daa = new DataAccessAdapter();

[colorvalue="00CC00"]//The method being called here accesses the user table[/color]
if (IsUsernameExists(daa, aUser.EmailId))
{
  throw new Exception("User exists");
}

daa.StartTransaction(IsolationLevel.ReadCommitted, aUser.EmailId);

aUser.UserTypeId = (byte)EnumUserType.Candidate;
aUser.CreatedOn = DateTime.Now;
[colorvalue="00CC00"]//This causes a save to the user table[/color]
daa.SaveEntity(aUser, true);

[colorvalue="00CC00"]//SNIP[/color]

UserRoles uroles = new UserRoles();
[colorvalue="00CC00"]//This causes a read of both the user and role tables, followed by a write to the user_role table... this never gets past the read of the user table, but it can read the roles table just fine (I tried reversing the order in which it reads the user and roles tables, and noticed that the read of the roles table doesn't cause any problems)[/color]
uroles.AddUsersToRoles(new string[] { aUser.EmailId }, new string[] { UserSecurityRoles.Candidate.ToString() });

daa.Commit();

The addition of the user to the role is within a different method that creates it's own DataAccessAdapter and doesn't re-use the DataAccessAdapter that is in the method that has the transaction

Adding the user to the role should be either done within the same transaction, or after commiting the transaction.

You can't save a record in the UserRole table which refers to a Row in the User table which hasn't been committed yet.

Also you'd get the timeout exception if you attempt to read the User data from outside the transaction, if it isn't comitted yet.