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

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

Answer 2

It is not necessarily to create separate table. You should do several steps. Let's assume that we have table 'Documents' (Id, Name, Data (varbinary)).

1. Open EF designer, copy and paste the 'Document' entity.
2. Rename it to 'DocumentData'. Add mapping to 'Documents' table.
3. Delete 'Data' property in 'Document' entity.
4. Delete 'Name' property in 'DocumentData' entity.
5. Right-click 'DocumentData' entity and add new Association. Select 1-to-1 association with 'Document' entity.
6. Select new association, go to Properties, click '...' on 'Referential Constraint' field, select 'Document' entity as principal, leave all as default (Id -> Id) and click OK.

Now build the project.

NOTE. Now when creating new 'Document' entity you should also create new 'DocumentData' entity even if you don't want to place any data yet:

Document doc = new Document();

doc.Name = "My document";
doc.DocumentData = new DocumentData();

context.Documents.Add(doc);
context.SaveChanges();