Streaming and Linq Blobs

Question

I have an object that I use to store document metadata in a table. The body of the document can be very large, sometimes> 2 GB, so I will store it in the nvarchar (max) field in SQL 2008. I will use SQL 2008 later to index this field. I will not use filestreams because they are very database limiting and prevent some types of concurrency blocking schemes.

This object is provided to the developer through LinqToSQL. I am concerned that the field will be large, and I saw a .Net bomb with OutOfMemory exception if the text is> 1.5 GB.

So, I wonder, can I handle this blob as a stream with Linq? Or do I need to get around Linq at all if I want to use blob?

Answer 1

Given the answer "Can a LINQ query retrieve BLOB files [...]" I suspect you're out of luck. The System.Data.Linq.Binary type has no mechanism for streaming - basically it is just an immutable representation of an array of bytes.

There might be some deep LINQ mojo that you could call, but I suspect it really should be pretty deep.

It is possible that the Entity Framework will handle this - I have not researched this.

Answer 2

I ended up writing my own method around linqtoSql using the writing method available for varchar (max) objects in SQL. This allows developers to place database inserts for large data types.