title: Using SQL Server Filestream with LINQ
body: <p>Accessing a BLOB file in the database using LINQ works like this:</p>
  
  <pre><code>var query = from file in database 
  where file.FileID = fileId 
  select file;
  </code></pre>
  
  <p>When I activate Filestream on this table LINQ queries the database via T-SQL. Which is a bad practice on larger files.</p>
  
  <p>According to this site: <a href="http://www.codeproject.com/Articles/128657/How-Do-I-Use-SQL-File-Stream" rel="nofollow">http://www.codeproject.com/Articles/128657/How-Do-I-Use-SQL-File-Stream</a> it should be done with a <code>SqlCommand</code>, query for the path and then access the file directly with <code>SqlFileStream</code>.</p>
  
  <pre><code>Select FileData.PathName() As Path,
  GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext
  From PictureTable Where PkId = (Select Max(PkId) From PictureTable)
  </code></pre>
  
  <p>Is now possible to access the file in a slicker (more "LINQ-er") fashion?</p>