sql-server-2008-r2entity-framework-5transactionscopesqlfilestream

SQL FILESTREAM breaking


Scenario: I recently added a component to my ASP.NET MVC app that allows them to upload files into the database. Since these files average more than 2MB, I elected to use FILESTREAMs. I save the HttpPostedFileBase to a temp file, do some business logic, then upload the file. After upload, the user is redirected to a page that views the file, inline in the browser. Here is the relevant upload code:

var DocContext = new DocumentEntities();
var dbFile = new File
{
    DocumentID = Guid.NewGuid(),
    Name = fileName,
    Type = file.ContentType
};
DocContext.Document.Add(dbFile);
DocContext.SaveChanges();

using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
using (var sqlFS = dbFile.Open(DocContext, FileAccess.Write))
using (var tempFS = tempFile.OpenRead())
{
    tempFS.CopyTo(sqlFS);
    scope.Complete();
}

Here is the relevant viewing/download code:

public ActionResult File(Guid? id = null)
{
    if (id == null)
        return RedirectToActionPermanent("Index");

    return File(DocContext.Document.Find(id.Value) as File);
}

private ActionResult File(File file)
{
    if (file == null)
        throw new HttpException(404, "Unknown document type");

    var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead });
    Disposing += d => { scope.Complete(); scope.Dispose(); };

    var fs = file.Open(DocContext, FileAccess.Read);
    Disposing += d => fs.Dispose();

    return new Misc.InlineFileStreamResult(fs, file.MimeType) { FileDownloadName = file.FileName, Inline = true };
}

And the Open method:

public partial class File
{
    public SqlFileStream Open(DocumentEntities db, FileAccess access)
    {
        var path = db.Database.SqlQuery<string>(
            @"SELECT FileData.PathName() FROM [File] WHERE DocumentID = @docID",
            new SqlParameter("docID", DocumentID)).First();
        var context = db.Database.SqlQuery<byte[]>(
            @"SELECT Get_FILESTREAM_TRANSACTION_CONTEXT() FROM [File] WHERE DocumentID = @docID",
            new SqlParameter("docID", DocumentID)).First();

        return new SqlFileStream(path, context, access);
    }
}

Viewing a previously uploaded file works beautifully. Viewing a file that a user has uploaded themselves (recently?), the following exception occurs: The transaction operation cannot be performed because there are pending requests working on this transaction.

What's going on?

UPDATE: I assume because I'm a SQL sysadmin, I can upload files and view them fine.


Solution

  • When the database entry had a MIME type instead of an extension in the Type column, I was using IIS and web.config to deduce what extension to give the file. But normal users didn't have permission to read the web.config. So, when a non server admin user tried to view a file that was stored with a MIME type instead of an extension, my routine would throw a permission exception, which would somehow trigger the transaction operation exception. No clue how.