Only get specific columns

entity-framework

Question

May I limit the columns that my EF objects in the performed sql can retrieve? Is there a way to just receive certain columns if I want them when I run the code below to get objects?

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

This would produce a select clause with all columns in it. But how can I have my objects exclude that column from the sql created if I have a column with a lot of data in it that actually slows down the query?

How can I make my query be if my table contains Id(int), Status(int), and Data(blob)?

select Id, Status from TableName

in place of

select Id, Status, Data from TableName

Using the advice provided below, my approach 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 saying it that way.

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

But I'm receiving a compilation error:

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

1
40
1/30/2017 9:25:30 AM

Accepted Answer

Sure. Using projection, you can:

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

Here is a real-world example (obviously, my DB has different tables than yours). I entered the following expression after adding my EF model to LINQPad:

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

I can see from LINQPad that the produced SQL is:

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

None of the other table fields are included.

Answering the revised question

Your columns According to the argument, it accepts type T and returns the same type. As a result, the expression you provide must comply with this, or you must modify the argument's type, 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);
}

The phrase may now return any type that you want.

54
10/7/2009 8:00:13 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow