Webapi: $filter on a sub entity field doesn't work

[magneticcore]

It is impossible, during a GET, to use $filter to filter on a sub entity field, odata returning an exception:
$filter=entity/id eq 20

Assemblies affected

• Microsoft.AspNetCore.OData v7.4.0
• Microsoft.EntityFrameworkCore v3.1.3

Reproduce steps

PersonAddress is a class that contains Person field, Person being also a class. Both contain several fields, including id.

Example of url that raises an exception:
http://localhost/api/personaddresses/?$expand=Person&$filter=Person/Id%20eq%2020

Expected result

Must return a PersonAddress collection whose Person.Id field is equal to 20

Actual result

The following exception is raised:
An unhandled exception occurred while processing the request.
InvalidOperationException: The LINQ expression 'DbSet
.Join(
outer: DbSet,
inner: p => EF.Property>(p, "PersonId"),
outerKeySelector: p0 => EF.Property>(p0, "Id"),
innerKeySelector: (o, i) => new TransparentIdentifier(
Outer = o,
Inner = i
))
.Where(p => EF.Property>(p.Inner, "Id") == null ? null : new PersonDto{
BirthDate = p.Inner.BirthDate,
ComplementName = !(string.IsNullOrEmpty(p.Inner.ComplementName)) ? p.Inner.ComplementName.Trim() : null,
DateInsert = p.Inner.DateInsert,
DateUpdate = p.Inner.DateUpdate,
DeathDate = p.Inner.DeathDate,
Employee = p.Inner.Employee,
ExternalId = !(string.IsNullOrEmpty(p.Inner.ExternalId)) ? p.Inner.ExternalId.Trim() : null,
FirstName = !(string.IsNullOrEmpty(p.Inner.FirstName)) ? p.Inner.FirstName.Trim() : null,
GenderId = p.Inner.GenderId,
Id = p.Inner.Id,
IdeNumber = !(string.IsNullOrEmpty(p.Inner.IdeNumber)) ? p.Inner.IdeNumber.Trim() : null,
LastName = !(string.IsNullOrEmpty(p.Inner.LastName)) ? p.Inner.LastName.Trim() : null,
LegalFormId = p.Inner.LegalFormId,
LiquidationDate = p.Inner.LiquidationDate,
Name = !(string.IsNullOrEmpty(p.Inner.Name)) ? p.Inner.Name.Trim() : null,
PersonTypeId = p.Inner.PersonTypeId,
SingleName = !(string.IsNullOrEmpty(p.Inner.SingleName)) ? p.Inner.SingleName.Trim() : null,
UserInsert = !(string.IsNullOrEmpty(p.Inner.UserInsert)) ? p.Inner.UserInsert.Trim() : null,
UserUpdate = !(string.IsNullOrEmpty(p.Inner.UserUpdate)) ? p.Inner.UserUpdate.Trim() : null
}
.Id == __TypedProperty_0)' 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.

[xuzhg]

@magneticcore Would you please try it use OData 7.3 version and EF core 2.x version and let us know the result?

[magneticcore]

For information, I tested with OData 7.4.1 and .Net 5.0 Preview 4.20220.10, still the same problem.

[Mhirji]

I am also experiencing this with 7.3, 7.4, and 7.41 on EF core 2.x. On 7.3 only, it seems like it works IF the sub-entity's key field is marked as nullable in its class. I believe that the following issues are all related, but am not 100% sure . . .

https://github.com/OData/WebApi/issues/2135
https://github.com/OData/WebApi/issues/2138

There is a workaround mentioned in the latter, but again, that only worked for me with 7.3 if the key field on the sub-entity was nullable, which I would rather not do. I have noticed that it is only if I set the key field to nullable in the class, define it as a key field in the model builder, that the service metadata document exposes the metadata of the property as not nullable. If in the class it is a key and not nullable, the service metadata document exposes the property as nullable, which seems a bit backwards to me.

[Metalick]

I am also experiencing this with 7.3, 7.4, and 7.41 on EF core 2.x. On 7.3 only, it seems like it works IF the sub-entity's key field is marked as nullable in its class. I believe that the following issues are all related, but am not 100% sure . . .

2135

2138

There is a workaround mentioned in the latter, but again, that only worked for me with 7.3 if the key field on the sub-entity was nullable, which I would rather not do. I have noticed that it is only if I set the key field to nullable in the class, define it as a key field in the model builder, that the service metadata document exposes the metadata of the property as not nullable. If in the class it is a key and not nullable, the service metadata document exposes the property as nullable, which seems a bit backwards to me.

Got the same here.

[xuzhg]

Who can share a simple repo for me to dig more?

[magneticcore]

Hi,

After passing to Microsoft.AspNetCore.OData v7.5.2 and dotnet (core) 5, this described problem is partially resolved. Note that we are using AutoMapper which has been updated to version 10.1.1.

It is possible to filter on a sub entity field if this field type is string or nullable numeric (int?, double?, etc), but not numeric not nullable. In this case, an error is raised. Check the new ticket to see this error: https://github.com/OData/WebApi/issues/2371