I've been working on a little MVC app here at work to help the vets record their consultation history, along with disease management and medication scripting. One of the features they've been asking for is the ability to attach files to a consultation record. For example, photographs of an anmial or a shed.

I knew that I would want to store said attachments in the database so that they're backed up along with the corresponding data, so I have implemented the feature using Entity Framework, storing the files into a varbinary(max) field.

First, here's the class structure for the files (the "attachments") themselves:

Notice that the attachment content is stored as a raw byte array in a separate class. I map the two classes to the same table in the DbContext, as well as configurating a "ConsultationAttachments" table to join consultations to attachments:

I'm not sure that the special ".png" case needs to be in there, but I figured I'd leave it in there for now.

Wrapping Up

In a future version of Entity Framework I'd like to see support for the FILESTREAM data type introduced in SQL Server 2008, but for now the idea of storing the file in a binary blob works fine. It's great that I can get a list of files without needing to retrieve their content - it's like lazy loading without any of the associated problems. :)