Can't use conditions in 'any' filter

[BehoIder]

Hi, First, thank you for great work! I have issue with a filter query with nested collection. A two DTO's:

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

public string Name { get; set; }

public IEnumerable<DTOEmployeeUserAccount> EmployeeUserAccounts { get; set; }
}

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

public int EmployeeId { get; set; }

public string UserIdentity { get; set; }
}

mapped to following EF Core entities:

public class Employee
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

[Required]
[MaxLength(128)]
public string Name { get; set; }

public virtual ICollection<EmployeeUserAccount> EmployeeUserAccounts { get; set; }
}

public class EmployeeUserAccount : IIdentity
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

[ForeignKey(nameof(Employee))]
public int EmployeeId { get; set; }

[Required]
[MaxLength(64)]
public string UserIdentity { get; set; }

public virtual Employee Employee { get; set; }
}

I querying employee collection with filter applied to nested employeeUserAccounts collection. $filter=employeeUserAccounts/any(user:user/userIdentity eq 'identity') or $filter=employeeUserAccounts/any(user:contains(user/userIdentity,'identity'))

public virtual async Task<IEnumerable<T>> GetODataAsync(DbContext context, ODataQueryOptions<T> options)
{
var queryableSource = context.Set<E>().AsQueryable();
IEnumerable<T> result = await queryableSource.GetAsync(_mapper, options);
return result;
}

throws exception: "The LINQ expression 'DbSet\r\n .Where(e => DbSet\r\n .Where(e0 => EF.Property<Nullable>(e, \"Id\") != null && EF.Property<Nullable>(e, \"Id\") == EF.Property<Nullable>(e0, \"EmployeeId\"))\r\n .Select(e0 => new DTOEmployeeUserAccount{ \r\n Id = e0.Id, \r\n UserIdentity = e0.UserIdentity \r\n }\r\n ) == null ? null : (Nullable)DbSet\r\n .Where(e1 => EF.Property<Nullable>(e, \"Id\") != null && EF.Property<Nullable>(e, \"Id\") == EF.Property<Nullable>(e1, \"EmployeeId\"))\r\n .Any(e1 => (string)e1.UserIdentity == \"identity\") == (Nullable)True)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."

or for 'contains': "The LINQ expression 'DbSet\r\n .Where(e => DbSet\r\n .Where(e0 => EF.Property<Nullable>(e, \"Id\") != null && EF.Property<Nullable>(e, \"Id\") == EF.Property<Nullable>(e0, \"EmployeeId\"))\r\n .Select(e0 => new DTOEmployeeUserAccount{ \r\n Id = e0.Id, \r\n UserIdentity = e0.UserIdentity \r\n }\r\n ) == null ? null : (Nullable)DbSet\r\n .Where(e1 => EF.Property<Nullable>(e, \"Id\") != null && EF.Property<Nullable>(e, \"Id\") == EF.Property<Nullable>(e1, \"EmployeeId\"))\r\n .Any(e1 => (string)e1.UserIdentity == null || False ? null : (Nullable)(string)e1.UserIdentity.Contains(\"identity\") == (Nullable)True) == (Nullable)True)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."

using Automapper 10.1.1 AutoMapper.AspNetCore.OData.EFCore 2.0.2 AutoMapper.Extensions.ExpressionMapping 4.0.2

[BlaiseD]

Maybe a repro will help - looks like EF does not like the query.

We have tests here and here indicating this should work.

Maybe inspect the code here and see the query being created.

To pinpoint the problem source (assuming a manual query works):

• Create the Select manually.
• Compare it with the Select generated here.

[BehoIder]

Thank you for answer. I tried write this query manualy direct to EF such as: var result = await context.Set<Employee>().Where(el => el.EmployeeUserAccounts.Any(a => a.UserIdentity.Contains("identity"))).ProjectTo<DTOEmployee>(_mapper.ConfigurationProvider).ToListAsync(); It works without any problem.
I'll try the compare direct and odata converted queries...

[BlaiseD]

It will be easier to figure out the difference if you post a repro we can run.

Are those null checks in your mapping configuration or is EF Core creating them?

[BehoIder]

I found issue in mapping, sorry for wasting your time. Thank you for you work.