Entity Framework 6 and sql server filestream

I’m trying to use entity framework with sql server filestream for big byte[] properties in my domain classes. At the moment to make it, I manually write custom scripts into the “Sql” void inside my migrations to create tables that stores these data into my filegroup.
Do you think about a better way to make it?

Entity Framework doesn’t support FILESTREAM columns and C# doesn’t support byte arrays bigger than 2GB in case you are dealing with really large values.

First rethink if you really want to store large binary data in the database or if there are better concepts for you problem domain.

IMHO mapping byte arrays to FILESTREAM columns doesn’t make too much sense. The only benefit (AFAIK) of FILESTREAM is that you can access it as a Stream. If you need that you are on your own since EF doesn’t support that.

In case you come to the conclusion that you really need FILESTREAM in an EF environment - yes, it’s doable. I don’t think you can have the FILESTREAM data in an entities property but I came up with an acceptable solution in a Repository/UnitOfWork-environment with entities referring to their respective data stream via an id.

The pattern made that much easier since you need a transaction to access FILESTREAM data as a Stream and the UnitOfWork was a handy place to handle these transactions. In the repository I enforced the referential integrity (since I could’t configure relations to a table EF doesn’t even know about). It has two additional functions: Add(entity, stream) that handles the streaming of the data to the database and setting the id to that stream in the entity and GetStream(entity) to get the Stream associated with the entity. They are two- and one-liners using a helper class that does all the SQL and SqlFileStream magic behind the scenes. And it generates the SQL for the migrations creating the table in the Up() and deleting it in the Down() method (to finally answer your question :wink:)

But again: I would’t have done that if it hadn’t been an explicit requirement of the product owner…