Wednesday, February 8, 2012

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.

21 comments:

obondo said...

Hi Erik, this post is very interesting to me, but
now I have a question for you: suppose I need to assign student's IDs manually so I change table schema to reflect this; the StudentId column is still primary key but its identity property is set to false.
The code in Program.cs remains the same, I just introduce some logic to generate a new ID each time I need to add a student.
When I run the app I get a DbUpdateException on the db.SaveChanges() call telling me that I'm trying to assign a NULL value to the StudentId column. That's not true as I verified I assigned a new value to that column.
Why is this happening?

Thanks very much in advance
andrea

ErikEJ said...

Hi, how can you introduce logic without code changes? Can you share your project? (Email via contact info on the blog)

obondo said...

Erik I don't want to focus on the logic I use to get the new IDs, just suppose I have one and it wors fine. For instance you could delete all the records in the students table and then try to add a new record with ID = 1. Doing so I get the DbUpdateException I told you.
I'm going to email you my sample code...

ErikEJ said...

I have replied to your email

ErikEJ said...

SB: Just select the image byte array from the database, and convert to an image

Steve Chadbourne said...

This still seems to be a problem in EF 5.0

ErikEJ said...

Steve: Thanks for the heads up, nice to know that this is still useful info!

arobincaron said...

Thank you for your post -- it got me back on track with saving images. Have you considered instead of disabling validation to instead remove the EF validation error that prevents the save from going through. You do that by overriding the DbContext.ValidateEntity method. I posted code here: http://stackoverflow.com/a/15481902/1030660

ErikEJ said...

Arobin: Is that not what I am doing already?

arobincaron said...

Your code tells EF to not validate the Student entity at all. The code I was suggesting would only remove the validation error against the specific property that was causing a problem (for example the Photo property). Turns out that after I posted this suggestion here (and on stackoverflow) I tested using MaxlengthAttribute without a parameter and found that it works too to prevent the EF validation error. So using Maxlength is an even better answer.

ErikEJ said...

I will test that, but I cannot see what "code" you posted on SO !?

ErikEJ said...

I see now, that simply specifying MaxLength prevents any validation errors! Will update the blog post and make a note on SO also - thanks.

Simon said...

If I use [MaxLength] without any parameters, I get "Default values not supported". If I set a value like [MaxLength(N)] N cannot be bigger than 8000. Is there a way to store blobs that are bigger than 8000?

ErikEJ said...

Simon: That sound odd. When do you get this message? Using which EF version?

Simon said...

I get it when calling DbContext.SaveChanges(). I am using EF 5.0

ErikEJ said...

I will test with EF6 and let you know... Otherwise email me a repro project

ErikEJ said...

Simon: Sorry,cannot repro with EF6

ErikEJ said...

Mr. Bit - deleted your comment by mistake, but doubt I can repro with EF6, as stated above, pls email repro project to me

Mr. Bit said...

My mistake. Your doubt is correct. I also could not reproduce the error with EF6.
Oh god...my wrong and blind mind...D'oh!
I was setting the wrong property... :)Everything works fine now. Thanks for your answer. Nice blog.
Regards.

Unknown said...

I've a SQL Server Compact database. I did a reverse POCO after following article:

https://github.com/ErikEJ/SqlCeToolbox/wiki/EntityFramework-Reverse-POCO-Code-First-Generator.

But I'm getting exception: System.NotSupportedException
Message: "Binary column with MaxLength greater than 8000 is not supported."

It's recommended not to change file generated by reverse POCO. What should I do to overcome this limitation?

ErikEJ said...

Ricky: Let us continue the support discussion here: https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator/issues/179 - and please provide the database schema and the code generating this error.