How to retrieve specific columns in Entity Framework

Question

Can I make my EF objects retrieve only specific columns in the sql executed?

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), Name(int), Data(blob), how can I make my query be

select Id, Name from TableName

instead of

select Id, Name, Data from TableName

From the suggestion below, my method is

 public List<T> GetBy<T>(DbContext context,Expression<Func<T, bool>> exp, Expression<Func<T,T>> columns) where T : class
    {

        return dbContext.Set<T>().Where(exp).Select<T,T>(columns).ToList();
    }

And I'm calling it like so

List<CampaignWorkType> list = GetBy<CampaignWorkType>(dbContext, c => c.Active == true, n => new { n.Id, n.Name });

i got an error like below.

Cannot implicitly convert type 'AnonymousType#1' to 'Domain.Campaign.CampaignWorkType'

how i can solve this?

If your tables have so many columns that they cause your query to run noticeably slower, then you should probably refactor your DB tables. Can't you just remove those columns from the designer? — Bob Horn
– Bob Horn, Commented May 16, 2012 at 21:51
By new {...} you're creating a list of anonymous types, not CampaignWorkTypes. The possible duplicate link shows a structural approach. — Gert Arnold
– Gert Arnold, Commented May 17, 2012 at 20:13
@GertArnold thanks but i have already know what you said,however i fixed my problem.... — tobias
– tobias, Commented May 17, 2012 at 22:54

Chad Levy · Accepted Answer · 2012-10-13 00:07:48Z

9

The solution is:

First, define a surrogate type:

public class CampaignWorkTypesSimpleList
{
   public int Id { get; set; }
   public string Name { get; set; }
}

Then change generic method like this:

public List<U> GetBy<T,U>(DbContext context,Expression<Func<T, bool>> exp, Expression<Func<T,U>> columns) 
                where T : class
                where U : class
{

  return dbContext.Set<T>().Where(exp).Select<T, U>(columns).ToList();
}

Finally, execute it.

List<CampaignWorkTypesSimpleList> list = this.GetBy<CampaignWorkType, CampaignWorkTypesSimpleList>(dbContext, c => c.Active == true, n => new CampaignWorkTypesSimpleList { Id = n.Id, Name = n.Name });

edited Oct 13, 2012 at 0:07

Chad Levy

10.1k8 gold badges44 silver badges69 bronze badges

answered May 17, 2012 at 22:52

tobias

1,5883 gold badges25 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

toy Over a year ago

how do you know which table its querying? edit nevermind - it the dbset set by T (written here for other people who might need it)

saille · Accepted Answer · 2012-05-17 03:24:16Z

-2

Assuming you are using the EF designer, remove the [Data] column from the Entity Model using the Entity Model design surface.

answered May 17, 2012 at 3:24

saille

9,2015 gold badges48 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to retrieve specific columns in Entity Framework

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related