File Attachments using MVC and Entity Framework

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:

public class Attachment
{
    public int Id { get; set; }
    public string FileName { get; set; }
    public string Description { get; set; }

    public AttachmentContent Content { get; set; }
}

public class AttachmentContent 
{
    internal AttachmentContent() { }

    public AttachmentContent(byte[] content)
    {
        Content = content;
    }

    public int Id { get; set; }
    public byte[] Content { get; set; }
}

At the moment I'm not using the "Description" property, but I figured it was worth having in there just in case.

Next we have the consultation to which the files are attached:

public class Consultation : IFarmBased
{
    [Key]
    public int Id { get; set; }

    public ICollection<Attachment> Attachments { get; set; }

    // lots of other properties omitted
}

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:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    Configure(modelBuilder.Entity<Consultation>());
    Configure(modelBuilder.Entity<Attachment>());
    Configure(modelBuilder.Entity<AttachmentContent>());

    // lots of other mapping code omitted
}

private void Configure(EntityTypeConfiguration<Consultation> consultations)
{
    consultations.ToTable("Consultations");

    consultations.Property(c => c.Id).HasColumnName("ConId");

    consultations.HasMany(c => c.Attachments)
                 .WithMany()
                 .Map(m => { 
                     m.ToTable("ConsultationAttachments"); 
                     m.MapLeftKey("ConId"); 
                     m.MapRightKey("AttachmentId"); 
                  });
}

private void Configure(EntityTypeConfiguration<Attachment> attachments)
{
    attachments.ToTable("Attachments");

    attachments.Property(a => a.Id).HasColumnName("AttachmentId");
    attachments.Property(a => a.FileName).HasColumnName("AttachmentName");
    attachments.Property(a => a.Description).HasColumnName("AttachmentDescription");

    attachments.HasRequired(a => a.Content).WithRequiredPrincipal();
}

private void Configure(EntityTypeConfiguration<AttachmentContent> attachments)
{
    attachments.ToTable("Attachments");

    attachments.Property(a => a.Id).HasColumnName("AttachmentId");
    attachments.Property(a => a.Content).HasColumnName("AttachmentContent");
}

So ... now I have the ability to fetch a consultation with its attachments (but without the contents of those attachments) like this:

var consultation = db.Consultations.Include("Attachments").FirstOrDefault(...);

... and then when the user clicks on an attachment, I can fetch its content thusly:

var att = db.Attachments.Include("Content").FirstOrDefault(...);

The last step is to return that file to the user so they can open or save it. Here's my controller action method for that:

public ActionResult Index(int id)
{
    // the above logic for fetching the attachment is in the _attachments service object
    var att = _attachments.GetAttachment(id);
    if (att == null) return HttpNotFound("Attachment not found");

    return File(att.Content.Content, GetMimeType(att.FileName), att.FileName);
}

I use a helper method called GetMimeType which I found on Stack Overflow:

static string GetMimeType(string fileName)
{
    string mimeType = "application/unknown";
    string ext = Path.GetExtension(fileName).ToLower();

    Microsoft.Win32.RegistryKey regKey = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey(ext);
    if (regKey != null && regKey.GetValue("Content Type") != null)
    {
        mimeType = regKey.GetValue("Content Type").ToString();
    }
    else if (ext == ".png")
    {
        mimeType = "image/png";
    }
    return mimeType;
}

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. :)