Only get specific columns

Question

Can I make my EF objects retrieve only specific columns in the sql executed? If I am executing the below code to retrieve objects, is there something I can do to only get certain columns if wanted?

public IEnumerable<T> GetBy(Expression<Func<T, bool>> exp)
{
    return _ctx.CreateQuery<T>(typeof(T).Name).Where<T>(exp);
}

This would generate a select clause that contains all columns. But, if I have a column that contains a large amount of data that really slows down the query, how can I have my objects exclude that column from the sql generated?

If my table has Id(int), Status(int), Data(blob), how can I make my query be

select Id, Status from TableName

instead of

select Id, Status, Data from TableName

From the suggestion below, my method is

public IEnumerable<T> GetBy(Expression<Func<T, bool>> exp, Expression<Func<T, T>> columns)
{
    return Table.Where<T>(exp).Select<T, T>(columns);
}

And I'm calling it like so

mgr.GetBy(f => f.Id < 10000, n => new {n.Id, n.Status});

However, I'm getting a compile error:

Cannot implicitly convert type 'AnonymousType#1' to 'Entities.BatchRequest'

Craig Stuntz · Accepted Answer · 2009-10-07T20:00:13.743

55

Sure. Projection does this:

var q = from r in Context.TableName
        select new 
        {
            Id = r.Id,
            Status = r.Status
        }

Here's an actual example (obviously, my DB has different tables than yours). I added my EF model to LINQPad and typed the following query:

from at in AddressTypes
select new
{
    Id = at.Id,
    Code = at.Code
}

LINQPad shows me that the generated SQL is:

SELECT 
    1 AS [C1], 
    [Extent1].[AddressTypeId] AS [AddressTypeId], 
    [Extent1].[Code] AS [Code]
FROM 
    [dbo].[AddressType] AS [Extent1]

None of the other fields from the table are included.

Responding to updated question

Your columns argument says it takes a type T and returns the same type. Therefore, the expression you pass must conform to this, or you need to change the type of the argument, i.e.:

public IEnumerable<U> GetBy<U>(Expression<Func<T, bool>> exp, Expression<Func<T, U>> columns)
{
    return Table.Where<T>(exp).Select<T, U>(columns);
}

Now the expression can return any type you care to use.

edited Oct 07 '09 at 20:00

answered Oct 07 '09 at 14:36

Craig Stuntz

125,891
12
252
273

I want to do this in a generic manner so that I can simply add another argument to the GetBy method call that is the columns that should be returned. – Brian Oct 07 '09 at 14:41
You need to pass a select expression along with your where expression, then, i.e.: var foo = GetBy(r => r.Id == someId, r => new { r.Id, R.Status }); – Craig Stuntz Oct 07 '09 at 14:46
Edited my post above with more info – Brian Oct 07 '09 at 15:07
That makes sense, but I don't understand why it's still complaining. In my example, I am querying for type BatchRequest and want to return BatchRequest with a limited number of columns. I believe my GetBy is correct, but it's the call that is reporting the error. – Brian Oct 07 '09 at 15:35
Because r => new { ... } doesn't return an instance of BatchRequest; it returns an instance of an anonymous type. r => new BatchRequest { ... } also won't work **if** BatchRequest is an Entity type, for reasons which won't fit in this comment box. But r => new SomeNonEntityType { ... } will work with the generic method in my answer; you don't have to use an anonymous type. – Craig Stuntz Oct 07 '09 at 16:38
OK, I think what this all means is that I can't really accomplish what I'm attempting. But, shouldn't the GetBy return IEnumerable instead of IENumerable? – Brian Oct 07 '09 at 19:43
1

You're right about IEnumerable, yes. Regarding using the entity type, the short answer is that the EF doesn't return partially materialized instances, for various good reasons. So you need to project onto a different type to return only a portion of the data. – Craig Stuntz Oct 07 '09 at 19:59
It does work with views. But views are trickier to map correctly, as they have no keys. – Craig Stuntz Aug 07 '10 at 12:12
I suspect that the success with views is dependent on your setup. In my case, I have a database first setup against Oracle. Mapping to a view will "guess" the correct keys, but will create a read-only `DefiningQuery` which selects the entire view in the `from` clause. So projection is pointless since the entire view is selected regardless. – Justin Skiles Jun 10 '14 at 13:56
The question was asking how to select specific columns using Entity Framework only. The answer was using LINQ. Althou it works, it should be more accordingly. – doncadavona Aug 10 '15 at 02:47

Only get specific columns

1 Answers1

Linked