Saving images and long strings to SQL Server Compact with Entity Framework 4.1 Code First

This StackOverflow question points out an issue with EntityFramework 4.1, when used with a SQL Server Compact table with image columns. The “image” type is the predecessor to varbinary(MAX), and is used for storing large binary values, sometimes referred to as BLOBs.

I have created a sample Console application with fixes for the related issues.

To re-create the application, install EntityFramwork 4.1, create a C# Windows Console application in Visual Studio 2010, and add .NET references to:

System.Data.Entity
EntityFramework
System.ComponentModel.DataAnnotations

Then add code similar to this:

using System;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
//Also added reference to EntityFramework.dll (EF 4.1)
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;

namespace CfTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new StudentContext())
            {
                var student = new Student { Name = "Erik", Photo = ConvertImageToByteArray(@"C:\Users\Erik\Pictures\erik.jpg"), StudentId = 1 };
                student.LongText = new string('x', 6666);
                db.Students.Add(student);
                int recordsAffected = db.SaveChanges();

                Console.WriteLine(
                    "Saved {0} entities to the database, press any key to exit.",
                    recordsAffected);

                Console.ReadKey();
            }

        }

        private static byte[] ConvertImageToByteArray(string fileName)
        {
            Bitmap bitMap = new Bitmap(fileName); 
            ImageFormat bmpFormat = bitMap.RawFormat; 
            var imageToConvert = Image.FromFile(fileName);
            using (MemoryStream ms = new MemoryStream())
            {
                imageToConvert.Save(ms, bmpFormat);
                return ms.ToArray();
            }
        } 
    }


    public class Student 
    {
        public int StudentId { get; set; }
        public string Name { get; set; }

        // Required to force Code First to create a ntext column, not a nvarchar(n)
        [MaxLength]
        public string LongText { get; set; }

        // Required to force Code First to create an image column, not a binary(n)
        [MaxLength]
        public byte[] Photo { get; set; }
    }

    public class StudentContext : DbContext
    {
        public DbSet<Student> Students { get; set; }
    }

}

And add an app.config with contents like this. The connection string name matches the DbContext name and this causes Code First to magically create a database file in the specified location, with a Students table:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="StudentContext"
         providerName="System.Data.SqlServerCe.4.0"
         connectionString="Data Source=C:\Users\Erik\Documents\visual studio 2010\Projects\CfTest\Students.sdf"/>
  </connectionStrings>
</configuration>

This is the resulting Students table:

-- Script Date: 11-07-2011 09:43  - Generated by ExportSqlCe version 3.5.1.3

CREATE TABLE [Students] (


  [StudentId] int NOT NULL  IDENTITY (1,1)


, [Name] nvarchar(4000) NULL


, [LongText] ntext NULL


, [Photo] image NULL


);


GO


ALTER TABLE [Students] ADD CONSTRAINT [PK__Students__000000000000000A] PRIMARY KEY ([StudentId]);


GO





This code demonstrates how to convert an image to a byte array, and also highlights the attributes required to work with image columns:

// Required to force Code First to create an image column, not a binary(n)
       [MaxLength]
       public byte[] Photo { get; set; }

The Photo column must be decorated with the MaxLength attribute.

// Required to force Code First to create a ntext column, not a nvarchar(n)
[MaxLength]
public string LongText { get; set; }

Likewise the LongText column must specify the MaxLength attribute.

public class StudentContext : DbContext
{
    public DbSet<Student> Students { get; set; }
}

UPDATE: @DamienGuard, of LINQ to SQL and EF Code First fame, pointed out that a better and more provider agnostic solution is to use MaxLength rather than TypeName = “ntext”.

UPDATE 2: Using [MaxLength] prevents any validation errors, and disabling validation is not required.