Exclude certain column from Entity Framework select statment

Question

I have an image column in the product table in a SQL Server database. The image column is used to save the images as bytes.

I know it is better to make a separate table for images, but I did not do that, so is there any way to exclude the image column when I am trying to list the products only without the images?

`yourDbSet.Select(x => new YourType { DesiredProperty = x.DesiredProperty, ... })` — haim770, Aug 24 '17 at 10:22
It would be great if there was an rxJS-like map operator, which you could use to null out a property before the query executes. — Sean, Aug 23 '20 at 08:45

trashr0x · Accepted Answer · 2021-05-05T22:20:12.103

Create a DTO with all the properties you need except the image property:

public class YourDTO
{
    public string YourProperty1 { get; set; }
    public string YourProperty2 { get; set; }
    // etc
}

You can then do:

var productDto = context.Products
                        .Where(x => x.Id == productId)
                        .Select(x => new YourDTO {
                            YourProperty1 = x.DbProperty1,
                            YourProperty2 = x.DbProperty2        
                            // etc, don't include the image column
                        });

Update:

If you don't want to map the results to YourDTO, you can project into an anonymous type:

var product = context.Products
                     .Where(x => x.Id == productId)
                     .Select(x => new {
                         x.DbProperty1,
                         x.DbProperty2        
                         // etc, don't include the image column
                     });

...and if you want to provide a custom name for each of the properties of the anonymous type:

var product = context.Products
                     .Where(x => x.Id == productId)
                     .Select(x => new {
                         YourProperty1 = x.DbProperty1,
                         YourProperty2 = x.DbProperty2        
                         // etc, don't include the image column
                     });

All of the above approaches would be functionally equivalent to the following SQL:

SELECT p.DbProperty1, p.DbProperty2 
FROM products p
WHERE p.Id = WhateverId;

Why would it be "not sufficient enough"? The query would be translated on the SQL side as `SELECT dbProperty1, dbProperty2 FROM Product`. You can also project to an anonymous type instead - see my update. — trashr0x, Aug 24 '17 at 10:40
That's what I meant. "It will be translated to the SQL" so you could only make a DTO and map it accordingly. From what I know EF has automated this process enough to just create that DTO and map it. — mrogal.ski, Aug 24 '17 at 10:44
Can this work with a grandparent `.Include()`? If you have a navigational property to a grandparent entity, the fields get lost from the query... — Sean, Aug 23 '20 at 08:56
The anonymous type solution (last code example) was exactly what I was looking for. I have some columns with lots of data in them which I want to exclude from the select. This is a small and elegant solution for this. — gridr, May 05 '21 at 08:43

score -4 · Answer 2 · answered Aug 24 '17 at 10:22

-4

product.Entity<Product>().Exclude(e => e.image);

or

product.Entity<Product>().Ignore(e => e.image);

answered Aug 24 '17 at 10:22

Vaibhav Bhatia

528
1
4
12

i am using code first approach and at the same time , i cant find the except and ignore , here down my statement ctx.Products.Where(p => p.isActive == true).OrderBy(p => p.Name).ToList().Select(p => ProductInformation.Create(p)); where ProductInformation is a typescript class – Laila Aug 24 '17 at 10:33
2

@Laila You've called `ToList()` which executes the whole query and dumps it into the `List<>` object. – mrogal.ski Aug 24 '17 at 10:37
1

where do i find either Exclude or Ignore is this an ef core thing? – Seabizkit Mar 30 '19 at 14:10
@Seabizkit you need to add System.Data.Entity reference – Vaibhav Bhatia Apr 06 '19 at 22:02
18

You haven't provided any context at all, that's why I gave you a -1. I guess your suggestion is based on the `OnModelCreating` method of the `DbContext` class? And so it isnt a way to ignore on select, but ignore for the complete `DbContext`. – kipusoep Jul 11 '19 at 10:44
These `Exclude` and `Ignore` methods look ideal, but they appear not to exist. – Richard Barraclough Aug 05 '22 at 08:05

Exclude certain column from Entity Framework select statment

2 Answers2

Linked