EF Core 3.1 - how to do filtering based on parameters?

Question

In EF Core 2.2 I could do the following:

var query = _dbContext.BusinessUnits
                      .Include(a => a.Parent)
                      .Include(a => a.Region)
                      .AsNoTracking()
                      .Select(x => new BUViewModel(x));

if (!string.IsNullOrWhiteSpace(dto.Name))
{
    query = query.Where(x => x.Name.ToLower().Contains(dto.Name.ToLower()));
}

if (dto.Level != null)
{
    query = query.Where(x => x.Level == dto.Level);
}

if (dto.ParentId != null)
{
    query = query.Where(x => x.ParentId == dto.ParentId);
}

Now, seems like 3.1 can't translate this to SQL (I assume you can't add Where after Select).

If I try to add Select after the filtering, compiler tells me that he can't convert IQueryable<BusinessUnit> to IQueryable<BuViewModel>.

If I try to explicitly declare IQueryable<BuViewModel> query = ..., I have to write Select before Where clauses(and it won't work).

What is the best approach here?

Answer 1

The problem starts with this line. IQueryable constructs an SQL query so this:

   .Select(x => new BUViewModel(x))

Iqueryable can't call constructors. So to fix that do it manually.

.Select(x=> new BUViewModel(){
               Id = x.Id,
               Name = x.Name // etc

            })

But why is the constructor not working? Because you can write whatever code you wish in the constructor. EF won't be able to translate to it SQL Query. Let's say you can write a constructor like this.

public BUViewModel(int id){
    var apiToken = _serviceCallApi(id);
}

The above for example calls an api when it constructs the object (it's just an example). It's impossible to translate this logic to a query. There are tools like automapper which can automatically map the entities.

My guess is simply you weren't dealing with an IQueryable but with IEnumerable. Which is incorrect because you want the SQL to filter the data for you. Not to load everything in memory.