1

We have a project which used Entity Framwork 4.0. We draw the model and then generate an SQL Compact 3.5 DB.

Since we needed a large BLOB store, we created a column with the Image data type (since Binary is limited to 8000 bytes). However, when updating to EF 4.1 our model was silently converted to having a Binary column instead!

No worries, we thought, we'll just change it back. Problem is Image cannot be selected anymore! And - specifying binary and setting length to a large value, say 100000, gives an error when trying to generate the DB.

We have found some pointers working with a code first approach, from EF 4.1 Release Notes, A related ADO.net team blog post, A third post describing the related issue and A SO questing discussing the related issue. However, all these talk about the issue using the Code First approach.

Any pointers on solving it Model First?

Thanks!

Community
  • 1
  • 1
Victor
  • 3,999
  • 3
  • 24
  • 27
  • Could you manually edit the EDMX file? – ErikEJ Aug 10 '11 at 06:47
  • Yes, we probably could. We also could edit the database after generation. Firstly though, we dont want to do that since we currently update the database frequently. Also it seems rather bad if 8000 bytes is the limit after "upgrading" to EF 4.1 from EF 4.0... – Victor Aug 10 '11 at 08:18

2 Answers2

0

You need to open model file .edmx with XML editor. And in <edmx:StorageModels> section, change Type for your data Property from varbinary to image. For example:

<EntityType Name="DataSet">
 <Property Name="data" Type="image" Nullable="false" />
</EntityType>

You may also need to manually change the type of the column in .sdf file.

This is a little hack, so you have to do this every time you change your model.

shfire
  • 837
  • 7
  • 7
0

convert your image to a byte array and store it like that then convert to image when you pull it out of the database that is how I store most of my blob type data as far as the code first approach issues you mentioned is there a specific reason you cannot use code first API and fluent mapping with your database

Edit:

Use the varbinary(max) for datatypes larger than 8kb as image will be deprecated in the future ref in your edmx file if you are using model first then you can set the column datatype in the properties section when the column is selected in the designer if you are using database first design then you can change the datatype in the database then regenerate model from the database

codefirst api create your data base then use reverse engineer codefirst using entity framework powertools ctp to generate your code first models and context

Chris McGrath
  • 1,727
  • 3
  • 19
  • 45
  • Thanks, that is the idea. The problem is not the serialization or deserialization but rather that we cannot get Entity Framework 4.1 to produce _any_ column type capable of holding enough bytes. – Victor Aug 10 '11 at 08:13
  • image should be able to hold up to 2gb of data but should be avoided in a production server as it is going to be removed in a future version of sql server varbinary(max) is the datatype microsoft suggests using for this type of binary data that will exceed 8000b – Chris McGrath Aug 10 '11 at 12:19
  • Chris: The question relates to SQL Server Compact, not SQL Server, in Compact image is fully supported, and there is no varbinary(MAX) – ErikEJ Aug 10 '11 at 12:27
  • whoops sorry bout that keep in mind tho that sql compact has strict limitations regarding database you will likely need to rethink your implementation if you want to use blobs of the size you are looking for image in sql compact has a size limitation of approx 1gb (1,073,741,823) and overall database size of 4gb total http://msdn.microsoft.com/en-us/library/ms172424%28v=SQL.110%29.aspx – Chris McGrath Aug 11 '11 at 13:50