Using SQL FileTable in Entity Framework

FileTable is a fixed schema regular sql table which is used to store blob data. Built on top of FILESTREAM feature, introduced in SQL Server 2008, and the hierarchyid data type allows us to store binary data in database and at the same time access it via Windows explorer! Yeap, thanks to FILESTREAM feature, the blob data sent to FileTable is stored in a totally different manner than other regular sql tables. Moreover stored data can become accessible to the Windows API by SQL Server exposed windows share folder. We can access this folder from Windows explorer level. What is even more exciting we can modify the content of this folder, add, remove files, add, remove subdirectories, and all changes are reflected in sql FileTable automatically!
Apart from that using FileTable, and FILESTREAM in general, as a blob storage, drastically improves the performance of database operations, especially when the amount of blob data is significant. Additionally native support for sql transactions (NTFS transactions are embeded in FILESTREAM operations), better maintainability makes this solution a way better choice than storing blob data on ordinary file system (file server) or totally in database table.

To benefit from FileTable feature we must prepare few things first. All of those are well described by Lenni Lobel in his great post series about FILESTREAM and FileTable here, here, here and here, but in a brief we need to:

1. Enable FILESTREAM feature on a SQL server service level (with use of SQL Server Configuration Manager for instance)
2. Enable FILESTREAM on SQL Server instance (using SQL Server Management Studio (SSMS) or by using sp_configure system function)
3. Create FILETABLE – enabled database with defining a filegroup and a place to store our blob data

Important note: we can use FileTable feature only on regular SQL Server instances. According to this documentation, FILESTREAM is not supported on a localdb database.

After that (assuming our database is called Documents)we need to enable non transacted access to our file stream so we can explore it from Windows level

   1: ALTER DATABASE Documents

   2: SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL)

… and set the name for shared folder

   1: ALTER DATABASE Documents

   2: SET FILESTREAM (DIRECTORY_NAME = 'DocumentsShare')

When we have all set up, creating a FileTable is as simple as running following query

   1: CREATE TABLE Documents AS FILETABLE

File table has predefined, fixed schema, so it is all we need to define. Among many columns, used to store a file information and file attributes, there are few that are most important:

• file_stream – FILESTREAM enabled varbinary column is used to store a reference to the physical blob file on the disk,
• name – the name of the file
• path_locator and parent_path_locator – those columns (of type hierarchyid) represent the directory/files tree structure
• stream_id – unique stream guid; in fact this is a result of using a FILESTREAM enabled column in a table, if you read about FILESTREAM you will probably know that this column quarantee to be unique for each row in FIleTable and does not change even when we move file to different location in the tree

Ok that is the introduction to FIleTable. Let’s now try to consume it in an WPF application using Entity Framework 6.0. For the sake of simplicity we want to display all documents in a DataGrid and be able to create and upload new document on a button click.

Our form can look like this

We have few columns mapped into a DataGrid. The Id column represents the stream_id column of FileTable. A Data column is mapped to a binary content of the file. The file is stored on local disk and opened with default windows associated process when user clocks on Open button. Above table is already filled up, but we will yet get to how to retrieve data from FileTable.

Since Entity Framework does not support FileTable (nor FILESTREAM by the way) we cannot simply map our entities to the file table. Trying to map a FileTable in Entity model designer raises a strange error

Error 6004: The table 'Documents.dbo.Documents' is referenced by a relationship, but cannot be found. 

This is caused by hierarchyid data type used in File Table, which is not supported in Entity Framework.

The workaround for this, I am going to present, is based on sql view and stored procedure mapping for CUD operations in Entity Framework.

1. We will create a view on Documents File Table which extract only those data we need and we map this view in EF
2. We will create stored procedures for Add and Delete entry in File Table (Update is not necessary since we do not expects to updates files, it can be an exercise for self practicing)
3. We will map stored procedures for  adding and deleting entities in EF.

For the sake of simplicity the view will contain only 5 columns

   1: CREATE VIEW [dbo].[DocumentsView]

   2: AS

   3: SELECT stream_id, file_stream, name, is_directory, file_stream.PathName() AS unc_path

   4: FROM dbo.Documents

stream_id which we will treat as unique identifier of our documents, file_stream – the actual file data, name, is_directory flag and the unc_path which is the unc path pointing to the physical file in a sql FILESTREAM storage folder.

Then we need to create two (at least) stored procedures.

One for inserting new document to our File Table (since we cannot do it via view)

   1: CREATE PROCEDURE [Documents_Add] (@filename nvarchar(255),@filedata varbinary(max))

   2: AS

   3: DECLARE @docid uniqueidentifier;

   4: BEGIN

   5:     SET @docid = NEWID();

   6:     INSERT INTO dbo.Documents(stream_id, file_stream, name) VALUES (@docId ,@filedata,@filename);

   7:     SELECT stream_id, file_stream.PathName() as unc_path FROM dbo.Documents where stream_id = @docid;

   8: END

And one for deleting

   1: CREATE PROCEDURE [Documents_Del] (@docId uniqueidentifier)

   2: AS

   3: BEGIN

   4:     SET NOCOUNT ON;

   5:     DELETE from Documents where stream_id = @docId;    

   6: END

Having that in place we can now go to our application project and Add new item, select ADO.NET Entity Data Model, choose to generate model from existing database, connec to to our database and in the next screen select only the view and both stored procedures, as shown below

After clicking on Finish the edmx designer should be displayed. We right click on or DocumentsView and choose the Stored procedure mapping. Then we map the Documents_Add and Documents_Del procedures as follows

And that’s it. Now we can simply bind the DocumentsView collection to a DataGrid

   1: ctx = new DocumentsEntities();

   2: ctx.DocumentsView.Load();

   3: gridFileTable.ItemsSource = ctx.DocumentsView.Local;

If we want to add a new file from a disk into our FileTable we just need to create new DocumentsView object and store it as regular EF entity

   1: var newDocument = new DocumentsView();

   2: newDocument.file_stream = File.ReadAllBytes("C:\temp\somefile.txt");

   3: newDocument.name = Path.GetFileName("somefile.txt");

   4: ctx.DocumentsView.Add(newDocument);

   5: ctx.SaveChanges();

Same if we want to remove file from File Table

   1: ctx.DocumentsView.Remove(newDocument);

   2: ctx.SaveChanges();

We can see and modify files in sql FILESTREAM shared folder. In my case this is \\<my_machine_name>\sqlexpress\DocumentsShare\Documents.

Thanks to procedure mapping all those operations are transparent so we can work with File Table data as with regular entity.

The same can be done with Code First approach. Since stored procedure mapping is now supported in Code First also, we should be able to connect to an existing database and map our virtual entity to stored procedures. I will leave this for your self practice.