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.

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:

22 comments:

Hi Erik, this post is very interesting to me, butnow 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?

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

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

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.

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?

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.

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.