How to Except Property instead of Select in Lambda LINQ

Question

I have a code that fetching a table. Here's my sample code:

public IQueryable<MyItem> MyItems(){
    return context.MyItem;
}

Here are the sample properties of MyItem

public int Id {get;set;}
public byte[] Image {get;set;}
public string Name {get;set;}

Since, byte[] can have multiple characters, I don't want to include then in searching because it will take so long if I have a records like 10,000 items.

Typically, I would Select like this:

public IQueryable<MyItem> MyItems(){
    return context.MyItem.Select(item=>new MyItem{
        Id=item.Id,
        Name=item.Name
    });
}

This is okay for few properties, but what I have a 10-20 properties, it would be hassle to write them one by one.

Is there any way like, I just Except the property Image in lambda for shorter code?

Seems like you need to split your table: https://stackoverflow.com/questions/8973529/retrieve-an-object-from-entityframework-without-one-field Possible help: https://www.c-sharpcorner.com/UploadFile/ff2f08/table-splitting-in-entity-framework-6-code-first-approach/ — Wurd, May 10 '18 at 09:14
Which EF version is this? Both EF core and "classic" support table splitting, but the implementation is slightly different. — Gert Arnold, May 10 '18 at 12:00
@jsonGPPD I posted an answer. Do you have any issue with this or that's not what you want? — arekzyla, May 10 '18 at 15:21

mshwf · Answer 1 · 2022-03-21T12:38:30.350

Create your own extension method SelectExcept:

public static IQueryable<T> SelectExcept<T, TKey>(this IQueryable<T> sequence,
    Expression<Func<T, TKey>> excluder)
{
    List<string> excludedProperties = new List<string>();
    if (excluder.Body is MemberExpression memberExpression)
    {
        excludedProperties.Add(memberExpression.Member.Name);
    }
    else if (excluder.Body is NewExpression anonymousExpression)
    {
        excludedProperties.AddRange(anonymousExpression.Members.Select(m => m.Name));
    }
    var includedProperties = typeof(T).GetProperties()
        .Where(p => !excludedProperties.Contains(p.Name));

    return sequence.Select(x => Selector(x, includedProperties));
}

private static T Selector<T>(T obj, IEnumerable<PropertyInfo> properties)
{
    var instance = Activator.CreateInstance<T>();
    foreach (var property in properties)
        property.SetValue(instance, property.GetValue(obj), null);

    return instance;
}

Usage:

var result = context.MyItem.SelectExcept(x => x.Image);

You can exclude more than one property:

var result = context.MyItem.SelectExcept(x => new { x.Image, x.Name });

This method gives an error. Error is "LINQ to Entities does not recognize the method". — Erman, Sep 25 '20 at 11:32

arekzyla · Answer 2 · 2018-05-10T15:03:33.937

You can use AutoMapper for this. Just create a DTO class with all the properties excluding those which you don't want to query:

public class MyItemDTO
{
    public int Id { get; set; }

    public string Name { get; set; }
}

Then add some mapping:

Mapper.Initialize(cfg => 
{
    cfg.CreateMap<MyItem, MyItemDTO>();
});

Now you can query your entities like this:

context.MyItem
    .OrderBy(m => m.Name)
    .ProjectTo<MyItemDTO>();

This extension is available in AutoMapper.QueryableExtensions.

You can also create a DTO class with all the properties from an entity and then ignore some of them in mapping:

cfg.CreateMap<MyItem, MyItemDTO>().ForMember(d => d.Image, m => m.Ignore());

If you use EF Core you can instantiate entity class inside LINQ query so it's possible to map entity to itself and ignore some properties from it without creating additional DTO classes:

cfg.CreateMap<MyItem, MyItem>().ForMember(d => d.Image, m => m.Ignore());

Then you just use ProjectTo with the entity type which excludes unwanted properties from SQL query:

context.MyItem
    .OrderBy(m => m.Name)
    .ProjectTo<MyItem>();

Note that you may also need to create mappings for each navigation property of an entity.

How to Except Property instead of Select in Lambda LINQ

2 Answers2

Linked