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'
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With