c# - Threading and SqlFileStream. The process cannot access the file specified because it has been opened in another transaction -

i extracting content of files in sql file table. following code works if not use parallel.

i getting following exception, when reading sql file stream simultaneously (parallel).

the process cannot access file specified because has been opened in transaction.

tl;dr:

when reading file filetable (using get_filestream_transaction_context) in parallel.foreach above exception.

sample code try out:

https://gist.github.com/nerdpad/6d9b399f2f5f5e5c6519

longer version:

fetch attachments, , extract content:

var documents = new list<extractedcontent>(); using (var ts = new transactionscope(transactionscopeasyncflowoption.enabled)) {     var attachments = await dao.getattachmentsasync();      // extract content simultaneously     // documents = attachments.todbdocuments().tolist(); // works     parallel.foreach(attachments, => documents.add(a.todbdocument())); // doesn't      ts.complete(); } 

dao read file table:

public async task<ienumerable<searchattachment>> getattachmentsasync() {     try     {         var commandstr = "....";          ienumerable<searchattachment> attachments = null;         using (var connection = new sqlconnection(this.databasecontext.database.connection.connectionstring))         using (var command = new sqlcommand(commandstr, connection))         {             connection.open();              using (var reader = await command.executereaderasync())             {                 attachments = reader.tosearchattachments().tolist();             }         }          return attachments;     }     catch (system.exception)     {         throw;     } } 

create objects each file: object contains reference get_filestream_transaction_context

public static ienumerable<searchattachment> tosearchattachments(this sqldatareader reader) {     if (!reader.hasrows)     {         yield break;     }      // convert each row searchattachment     while (reader.read())     {         yield return new searchattachment         {             ...             ...             uncpath = reader.to<string>(constants.uncpath),             contentstream = reader.to<byte[]>(constants.stream) // get_filestream_transaction_context()              ...             ...         };     } } 

read file using sqlfilestream: exception thrown here

public static extractedcontent todbdocument(this searchattachment attachment) {     // read file     // exception thrown here     using (var stream = new sqlfilestream(attachment.uncpath, attachment.contentstream, fileaccess.read, fileoptions.sequentialscan, 4096))     {         ...         // extract content file     }      .... } 

update 1:

according this article seems isolation level issue. has ever faced similar issue?

the transaction not flow in parallel.foreach, must manually bring transaction in.

//switched thread safe collection. var documents = new concurrentqueue<extractedcontent>(); using (var ts = new transactionscope(transactionscopeasyncflowoption.enabled)) {     var attachments = await dao.getattachmentsasync();     //grab reference current transaction.     var transaction = transaction.current;     parallel.foreach(attachments, =>     {         //spawn dependant clone of transaction         using (var depts = transaction.dependentclone(dependentcloneoption.rollbackifnotcomplete))         {             documents.enqueue(a.todbdocument());             depts.complete();         }     });      ts.complete(); } 

i switched list<extractedcontent> concurrentqueue<extractedcontent> because not allowed call .add( on list multiple threads @ same time.