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:UsersErikPictureserik.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:UsersErikDocumentsvisual studio 2010ProjectsCfTestStudents.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.

Write a Comment

Your email address will not be published. Required fields are marked *