Entity Framework avoid retrieving column unless explicitly requested

Question

I've got an SQL table that stores a person record where one column is an image. I'm using the table in an ASP.Net MVC application and I only want to retrieve the image column in the controller action that is used by my HTML image source attribute.

How can I prevent the column storing the image from being retrieved by my LINQ query without having to resort to explicitly requesting every desired column but still be able to retrieve it with an explicit request?

This is the LINQ expression I don't want to return the image column in (there are about a dozen includes so explicitly specifying each property is not an option):

Person person = (from per in entities.People.Include(...).Include(..) 
                 where per.ID == id 
                 select per).FirstOrDefault();

The LINQ expression I use to get the image is:

byte[] picture = (from per in entities.People 
                  where Per.ID == id 
                  select per.Picture).FirstOrDefault();

Do you have control over your database schema? – Jon Limjap Mar 31 '11 at 04:56 — Jon Limjap, Mar 31 '11 at 04:56
Yes but it's in production and will be painful to change. – sipsorcery Mar 31 '11 at 05:37 — sipsorcery, Mar 31 '11 at 05:37

score 3 · Accepted Answer · answered Mar 31 '11 at 07:42

3

You must use table splitting. The point of table splitting is that you don't touch your database but you map multiple entities to single table.

Your Person entity will not contain Image anymore but instead it will contain navigation property to PersonImage entity. The new entity will contain only Id (same as related person => one-to-one relation) and Image. A navigation property can be loaded only if needed. You can't avoid loading a scalar property unless you use a projection or query view (which you probably don't want to use).

answered Mar 31 '11 at 07:42

Ladislav Mrnka

360,892
59
660
670

I have done this for a very similar case as sipwiz's and it works great. – Fabian Nicollier Mar 31 '11 at 08:30
Perfect. Exactly what I was after. – sipsorcery Mar 31 '11 at 23:00
table splitting link is dead. This could do with the content being summarised in the answer. – Amicable Oct 29 '15 at 16:46

score 1 · Answer 2 · answered Mar 31 '11 at 04:56

Given that there are plenty of reasons to split a table when you have one large but rarely used section of it, why solve this on the code side? Your best option is probably to break the images off in a separate table which is linked to the first. Your sql server will be happier and your code will be cleaner.

score 1 · Answer 3 · answered Mar 31 '11 at 05:49

Depending on how you're using EF4 (Database-first? Model-first?), you may be able to make a view (and another entity) that doesn't contain your image. Or you can use a defining query on your Model side: http://blogs.msdn.com/b/davidebb/archive/2010/10/01/the-easy-way-to-create-an-entity-framework-defining-query.aspx

score 0 · Answer 4 · answered Mar 31 '11 at 04:51

0

var smallPerson = from per in entities.People.Include(...).Include(..) 
                  where per.ID == id 
                  select new Person() {
                    .Name = per.Name,
                    .Surname = per.Surname
                  }).FirstOrDefault();

Alternatively just return an anonymous type using this similar method. This object will be read only.

var smallPerson = from per in entities.People.Include(...).Include(..) 
                  where per.ID == id 
                  select new {
                    .Name = per.Name,
                    .Surname = per.Surname
                  }).FirstOrDefault();

answered Mar 31 '11 at 04:51

Slappy

4,042
2
29
41

That's what I don't want to do. I've got over a dozen includes that build up a fairly nasty object graph and I don't want to build it by hand in the select. – sipsorcery Mar 31 '11 at 04:54
Nope, you can't. Think of the capabilities of SQL. You cant do a query specifying "Except these columns". EF Has to match SQL capabilities. Your other options will be reflection or something – Slappy Mar 31 '11 at 04:56
this will still result in the image column being part of the SELECT statement. – Fabian Nicollier Mar 31 '11 at 08:32
Nope, it only brings back the specified ones. http://stackoverflow.com/questions/1531934/entity-framework-only-get-specific-columns – Slappy Mar 31 '11 at 09:57

Entity Framework avoid retrieving column unless explicitly requested

4 Answers4