How to return an empty column with iQueryable

Question

I've a method what obtains a list of Users filtering by an specific field. This method is useful for a lot of functionalities of my application, but in other cases is too slow.

The user's table have a field what contains data of pictures, so it's a heavy field. Now i'm developing a functionality what not need this field and i'm searching the way to don't return it, or return it as empty for streamline the process

I'm working with c#, obtaining a filteredList of Users from UnitOfWork repository with "GetByFilter" function.

UserController.cs

/// <summary>
/// Get by Filter
/// </summary>
/// <param name="filter">user filters</param>
/// <returns></returns>
[Route("functionRoute")]
[HttpPost]

public IHttpActionResult GetUsersByFilter([FromBody] UserFilter filter)
{
            try
            {
                UserService service= new UserService ();
                List<User> list = service.GetByFilter(filter).ToList();

                List<UserCE> listCE = Mapper.Map<List<UserCE>>(list);

                return Ok(listCE);
            }
            catch (Exception ex)
            {
                TraceManager.LogError(ex);
                return InternalServerError(ex);
            }
}

UserService.cs

public List<User> GetByFilter(UserFilter filter)
        {
            return _unitOfWork.UserRepository.GetByFilter(filter).ToList();
        }

UserRepository.cs

public IQueryable<User> GetByFilter(UserFilter filter)
        {
            return Get_internal(filter);
        }

private IQueryable<User> Get_internal(UserFilter filter)
        {
            IQueryable<User> users = _context.Users;

            if (filter.Deleted != null)
            {
                users = users.Where(u => u.Deleted == filter.Deleted );
            }            

            return users;
        }

I try to clear column later, but the process continues being too heavy. How i can streamline this process?

Possible duplicate of [this](https://stackoverflow.com/questions/19536064/select-multiple-columns-using-entity-framework) SO question. — Jeroen Heier, Aug 20 '19 at 08:19

score 1 · Answer 1 · answered Aug 20 '19 at 08:17

I would change your Get_internal() function like so: (have not tested this, I wrote the code purely from memory, but it should probably work with a few minor tweaks)

private IQueryable<User> Get_internal(UserFilter filter)
        {
            IQueryable<User> users = _context.Users;

            if (filter.Deleted != null)
            {
                users = users.Where(u => u.Deleted == filter.Deleted );
            }            

            return users.Select(x => new User() {
                Id = x.Id,
                Name = x.Name,
                //every property except your image property
                });
        }

This will return a new User object for every row, but will only fill the properties you are selecting explicitly. So if you don't select your image property, this will not get selected.

If you check the generated SQL, you should also see that this column is never selected.

score 1 · Answer 2 · answered Aug 20 '19 at 08:18

By convention, all public properties with a getter and a setter will be included in the model.

To exclude properties you will need to either use Data Annotations or the Fluent API

Data Annotations:

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

    [NotMapped]
    public string IgnoredField { get; set; }
}

Fluent API:

public class MyContext : DbContext
{
    public DbSet<User> Users{ get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .Ignore(b => b.IgnoredField);
    }
}

@Excali 2 did my answer help you out your issue in the end? – andyb952 Aug 29 '19 at 14:58 — andyb952, Aug 29 '19 at 14:58

score 0 · Accepted Answer · answered Aug 21 '19 at 05:39

Finnaly after try some things, I decided to apply the solution proposed by Steven, but with few minor tweaks. It gave problems in "Get_Internal", so I decided (although it's not the best option) do it in the controller.

the code looks (more or less) like this:

/// <summary>
/// Get by Filter
/// </summary>
/// <param name="filter">user filters</param>
/// <returns></returns>
[Route("functionRoute")]
[HttpPost]

public IHttpActionResult GetUsersByFilter([FromBody] UserFilter filter)
{
            try
            {
                UserService service= new UserService ();
                List<User> list = service.GetByFilter(filter).Select(x => new User() {
                Id = x.Id,
                Name = x.Name,
                //every property except your image property
                }).ToList();

                List<UserCE> listCE = Mapper.Map<List<UserCE>>(list);

                return Ok(listCE);
            }
            catch (Exception ex)
            {
                TraceManager.LogError(ex);
                return InternalServerError(ex);
            }
}

How to return an empty column with iQueryable

UserController.cs

UserService.cs

UserRepository.cs

3 Answers3