I am extracting content of the Files in SQL File Table. The following code works if I do not use Parallel.

I am getting the following exception, when reading sql file stream simultaneously (Parallel).

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

TL;DR:

When reading a file from FileTable (using GET_FILESTREAM_TRANSACTION_CONTEXT) in a Parallel.ForEach I get the above exception.

Sample Code for you to try out:

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

Longer Version:

Fetch Attachments, and extract content:

var documents = new List<ExtractedContent>();
using (var ts = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    var attachments = await dao.GetAttachmentsAsync();

    // Extract the content simultaneously
    // documents = attachments.ToDbDocuments().ToList(); // This works
    Parallel.ForEach(attachments, a => documents.Add(a.ToDbDocument())); // this 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 for each file: The object contains a reference to the GET_FILESTREAM_TRANSACTION_CONTEXT

public static IEnumerable<SearchAttachment> ToSearchAttachments(this SqlDataReader reader)
{
    if (!reader.HasRows)
    {
        yield break;
    }

    // Convert each row to 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 the file using SqlFileStream: Exception is thrown here

public static ExtractedContent ToDbDocument(this SearchAttachment attachment)
{
    // Read the file
    // Exception is thrown here
    using (var stream = new SqlFileStream(attachment.UNCPath, attachment.ContentStream, FileAccess.Read, FileOptions.SequentialScan, 4096))
    {
        ...
        // extract content from the file
    }

    ....
}

Update 1:

According to this article it seems like it could be an Isolation level issue. Has anyone ever faced similar issue?

The transaction does not flow in to the Parallel.ForEach, you must manually bring the transaction in.

//Switched to a thread safe collection.
var documents = new ConcurrentQueue<ExtractedContent>();
using (var ts = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    var attachments = await dao.GetAttachmentsAsync();
    //Grab a reference to the current transaction.
    var transaction = Transaction.Current;
    Parallel.ForEach(attachments, a =>
    {
        //Spawn a dependant clone of the transaction
        using (var depTs = transaction.DependentClone(DependentCloneOption.RollbackIfNotComplete))
        {
            documents.Enqueue(a.ToDbDocument());
            depTs.Complete();
        }
    });

    ts.Complete();
}

I also switched from List<ExtractedContent> to ConcurrentQueue<ExtractedContent> because you are not allowed call .Add( on a list from multiple threads at the same time.