Best approach for a Binary column with LINQ to SQL

Question

My SQL 2008 table, let's call it tblDocument, has columns like name, creator, sequenceNumber, followed by a "DocumentContent" varbinary(max) column that contains the document itself.

I'm using LINQ to SQL. If I want to display the rows of tblDocument in an interface without retrieving a multi-megabyte binary over the wire for each row, what's the best way to accomplish that? Is there a way to do this so that the filestream doesn't get accessed until I read from the property, or something like that?

Thanks!

Answer 1

Take a look here: LINQ To SQL Tutorials - Lazy-Loading Properties for Performance

Answer 2

You can use Linq to create an anonymous object that doesn't include the DocumentContent column. Like this:

var list = from item in db.tblDocument
  select new  
  {
    item.ID,
    item.name,
    item.creator,
    item.sequenceNumber
  };

Then later use the ID to retrieve just the DocumentContent when you need it:

var content = db.tblDocument
  .Where(x => x.ID == MyRowID)
  .Select(x => x.DocumentContent).Single();