How to load varbinary(max) fields only when necessary with ADO.NET Entity Framework?

Question

I have a varbinary(max) field in one of my tables but I don't need it every time and I'm looking for a way to fetch it from the database only when necessary. I'm using ADO.NET Entity Framework. How to do that?

Answer 1

One way would be to project your result set into an anonymous type when you don't need the blob:

from entity in context.Entities
select new 
{
    Field1 = entity.Field1,
    Field2 = entity.Field2
}

In this example, only Field1 and Field2 will be loaded.

This method has the disadvantage that you cannot update the returned instance and do context.SaveChanges. Though I would argue that saving an instance without full knowledge of the instance is borderline unsafe. This method is good when you want a long list of return the instances, but will be querying for a single instance, varbinary field and all, before you actually update.

Answer 2

The solution was to create a separate table with the varbinary field and make 1-to-1 relationship between the tables