Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Only get specific columns

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'

like image 207
Brian Avatar asked Oct 07 '09 14:10

Brian


People also ask

How do I display only certain columns in SQL?

To select columns, choose one of the following options: Type SELECT , followed by the names of the columns in the order that you want them to appear on the report. Use commas to separate the column names.

How do I select only few columns?

Either specify * , and get all columns, or specify each one you want, from col5 to col1000. (1000 columns indicates a poorly designed database.) If you could tag which particular DBMS are you using, you might find a solution involving dynamic SQL with a metadata query that builds your SQL for you.


Video Answer


1 Answers

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.

like image 62
Craig Stuntz Avatar answered Sep 21 '22 11:09

Craig Stuntz