Entity Framework data type for document storage in SQL Server 2008.

document entity-framework sql-server-2008 store types


I'm attempting to use the Entity Framework to store a document in SQL Server 2008.

I think I've finished writing the code to do this. Which Data Type to utilize in SQL Server and my entity model is the current issue I'm having.

My first selection, "Image," results in a "Invalid mapping" issue when I update the model. According to the Type drop-down in the entity's attributes, I can see that there isn't an equivalent to "Image" there.

I tried "varbinary(MAX)" after that, and I can see that it corresponds to "binary" in the entity model. However, the code stops when I execute it because it warns me that the data will be truncated. My choice of "varbinary(MAX)" was influenced by the fact that the SQL Server Data Type "binary" is 8000 bytes long; as a result, the entity model seems to be reducing or mapping "varbinary(MAX)" to "binary."

Is this accurate?

If so, could you kindly advise me on what my data types should be (in both SQL Server 2008 and in my entity model)? Any recommendations?

11/11/2010 8:49:27 PM

Accepted Answer

It will be ok buddy, just change your model's data type to "byte"; if you want more clarification, just post a remark.


Dude, I previously tried it with Linq to Sql, and this time I tried it with EF. Your conceptual model, Foo.edmx, has a data type of Binary (you can open it using Visual Studio's open with context menu and then choose Xml Editor or any other text editor like notepad), but your generated file, Foo.designer.cs, has a data type of Byte[].

And the restriction you indicated before doesn't exist. With a 10000-byte test, it successfully added without cutting off my array. Regarding benchmarking on whether to save documents in a database or a file system, I read an article that claimed that in Sql Server 7, the file system performed better when retrieving stored data, but that in later versions of Sql Server, the database speed took over and that Sql Server was recommended for document saving.

In my opinion, while storing documents, I prefer to save them on a database if they are not too enormous (NoSql DBs has great performance here as far as I know),

Integrity of my data comes first

Second: Increased potential for performance (cause if your folder has large number of files, reading and writing files in those folder slows down gradually more and more unless you organize them in more than one folder and preferably in a tree like folders),

Third: Security rules that you can more easily apply to them via your application (although you can also accomplish this using a file system technique).

Fourth: You may take use of the features your DBMS offers for querying, manipulating, and... those files.

and a lot more:-)

11/13/2010 5:50:28 PM

Popular Answer

Instead of storing actual documents in the database, you should ideally keep the the document's URL, which then leads to the actual document on the web server (or some other storage, CDN, etc).

However, you should use FILESTREAM. if you must keep it in SQL Server (and because you are using SQL 2008).

Additionally, EF4 supports it (i believe). It corresponds to binary.

However, as I said, I'm not sure how well this will work. Perform some benchmarks; if they show that it isn't running well enough, consider utilizing the standard ADO.NET/FileStream API.

I still believe it should be placed on the file system rather than a database (my opinion of course)

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow