We are facing a very complex issue in our production application.

1. We have a WCF method which creates a complex Entity in the database with all its relation.

   public void InsertEntity(Entity entity) { using(TransactionScope scope = new TransactionScope()) { EntityDao.Create(entity); }

   }
   

2. EntityDao.Create(entity) method is very complex and has huge pieces of logic. During the entire process of creation it creates several child entities and also have several queries to database.

3. During the entire WCF request of entity creation usually Connection is maintained in a ThreadStatic variable and reused by the DAOs. Although some of the queries in DAO described in step 2 uses a new connection and closes it after use.

Overall we have seen that the above process behaviour is erratic. Some of the queries in the inner DAO does not even return actual data from the database? The same query when run to the actaul data store gives correct result.

What can be possible reason of this behaviour?