Using SQL Server Filestream with LINQ

Question

Accessing a BLOB file in the database using LINQ works like this:

var query = from file in database 
where file.FileID = fileId 
select file;

When I activate Filestream on this table LINQ queries the database via T-SQL. Which is a bad practice on larger files.

According to this site: http://www.codeproject.com/Articles/128657/How-Do-I-Use-SQL-File-Stream it should be done with a SqlCommand, query for the path and then access the file directly with SqlFileStream.

Select FileData.PathName() As Path,
GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext
From PictureTable Where PkId = (Select Max(PkId) From PictureTable)

Is now possible to access the file in a slicker (more "LINQ-er") fashion?

StuS · Answer 1 · 2013-10-24T20:05:12.783

Ultimately this will not be a LINQ query, because you have to translate LINQ query to classic SQL query (by using DataContext.GetCommand) and call BeginExecuteReader from SqlCommand.

public class TerrasoftFiles : ITerrasoftFiles
{
    public TerrasoftFiles()
    {
        this.sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["TTDataReader"].ConnectionString);
        this.sqlCommand = new SqlCommand(@"SELECT FileData FROM tbl_Files WHERE ID = @ID", sqlConnection);
    }

    private SqlConnection sqlConnection;
    private SqlCommand sqlCommand;

    public IAsyncResult BeginGetFiles(Guid ID, AsyncCallback asyncCallBack, object asyncState)
    {
        sqlCommand.Parameters.Add(new SqlParameter("@ID", ID));

        sqlConnection.Open();

        return sqlCommand.BeginExecuteReader(asyncCallBack, asyncState);
    }

    public Stream EndGetFiles(IAsyncResult asyncResult)
    {
        using(sqlConnection as IDisposable)
           using (sqlCommand as IDisposable)
                using (var Reader = sqlCommand.EndExecuteReader(asyncResult))
                {
                    return (Reader.Read()) ? Reader.GetSqlBytes(0).Stream : Stream.Null;
                }
    }
}

Well your example query could be used in LINQ and provide me with the data like your SQLCommand. But I want to query for the SQL-PathName() with LINQ. So I can access the file directly and bypass the DBMS. — Vulcano, Oct 24 '13 at 10:25
Maybe this [article](http://www.bluelemoncode.com/post/2011/11/16/Upload-and-Download-files-in-database-using-MVC-Linq-to-SQL.aspx) is right for your case — StuS, Oct 24 '13 at 19:54
Unfortunately not really. Your article saves the data directly in the table. Which is fine for files below 1MB but not for larger files. Have you checked out the "new" Filestream feature? Its kind of awesome once it works :) https://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/ — Vulcano, Oct 25 '13 at 05:37

Using SQL Server Filestream with LINQ

1 Answers1