What am I trying to do?The system that I am involved with at the moment has the need to store files with some of it's entities. We've started out by using Entity Framework 5 Code First (although in this post I'll be working with Entity Framework 6) to create the data model and database mappings which is working nicely and is proving itself a very useful framework.
When looking at saving file data along with an entity there are a few choices:
- Store the file contents directly in the database
- Save the file contents to the file system
- Use a database table with FILESTREAM enabled
- Use a FILETABLE
The next option is a reasonable option and with recent versions of Windows Server we have a transactional file system. So far so good, but it would be nice to not have to think about two persistence mechanisms.
FILESTREAMs were introduced in SQL Server 2008 and allow you to store unstructured data on the file system, so it feels like we're using the right tools for the job but they're all nicely in the same package. The problem here is that Entity Framework doesn't support FILESTREAM.
Lastly there's FILETABLE which was introduced with SQL Server 2012. This is like FILESTREAM, but rather than defining it at a column level you get a table created for you which provides information from the file system. It is a really nice system, but it didn't quite fit with how we're structuring data and it's also not supported by Entity Framework.
So the option that I would ideally like to work with here is the FILESTREAM option as it gives me all of the database goodness but with the performance of the file system. But there is just that minor sticking point of it not being supported by Entity Framework. After a fair amount of playing around with the technology and research into the problem I figured that I could probably make it work by falling back to basic ADO.NET for handling the FILESTREAM part of the requests. Whilst this was an option I didn't really want to start having different technology choices for doing database work, so the goal was to see how much I could get away with in Entity Framework.
Setting up the test solution
The database serverWith SQL Server the default install options will not give you a FILESTREAM enabled server but you can enable it. I'm not going to go into how with this post as Microsoft have some pretty good documentation available on how to do this.
This also means that we can't let Entity Framework create the database for us, so you will need to create an empty, FILESTREAM enabled database and point to that.
The outline of the projectI created the solution in Visual Studio 2013, and delving into the most creative parts of my mind I came up with a solution that has hotels, with rooms and multiple pictures of each room (okay, not my most creative moment, but it gets the job done).
So in this solution I have my data model which is pretty simple. I have some locations, at each location there are a number of hotels, each hotel has rooms and each room has photos.
|Solution Data Model|
|Photo entity configuration|
At this point if we were to deploy the database we would get a table with no data information and a blank property in our entity. What we need to do next before any of this is useful is to somehow get a FILESTREAM column into the Photo table. The solution to this is to use Entity Framework migrations, the basics of which I'll not cover here and leave it as an exercise to the reader.
Migrations provides us with a migration class for each migration added to uplift and roll-back the changes to the database. The useful method for us in this class is the Sql method which allows us to execute SQL commands; using this we can add our ROWGUID column and our FILESTREAM column with all the constraints we need (and of course the appropriate commands to remove it all again as well for the Down method).
|Migrations SQL commands|
So that's half the battle won, the next challenge is being able to read to and write from the table.
Storing and retrieving file dataSo how do we query data in a FILESTREAM column? Well this is the bit where we fall back to the System.Data.SqlTypes namespace, specifically the SqlFileStream class. We use this class to read the contents of the file back from the server as a stream, but this only works in the context of a SQL transaction.
So the first thing we need to do is get the file path and the SQL transaction information, we can then pass this to the SqlFileStream constructor to get our stream, after which it's just a case of reading from the byte array in our entity and writing to the SqlFileStream stream. To get this information we need to run a custom SQL statement. We could do this using a SqlCommand object, but I still want to stick to Entity Framework a bit more, fortunately there's the DbContext.Database.SqlQuery<TElement> class which we can use to run raw SQL statements, it also handles parameters so we can parametrize the query (great for guarding against SQL injection attacks) and it an enumerable collection mapped to TElement (which does not have to be a part of our data model).
|Raw Data Query|
Running all of this inside of a transaction scope will get information required (the call to "First" will enumerate the collection) to pass to the SqlFileStream constructor, we can then use this to write data to the stream.
|Writing to the FILESTREAM|