Odata $filter and $orderby not working with Select to new DTO for one to one relationship

[cap7ainjack]

Since I update to .NET Core $orderby and $filter does not work in this specific case, which was perfectly working before.

The DTO models

public class AddressDto
{
        public Guid Id { get; set; }
        public string ZipCode{ get; set; }
        public IdAndName State { get; set; }
}

public class IdAndName 
{
        public Guid Value { get; set; }
        public string Text{ get; set; }
}


// The DB model
public class AdressEntity {
            public Guid Id { get; set; }
        public string ZipCode{ get; set; }
        public Guid StateId {get;set;}
        public State State {get;set;}
}

// The SELECT:

query.Select(x =>
    new AddressDto()
    {
        Id = x.Id,
        ZipCode = x.ZipCode,
        State = x.StateId.HasValue ? new IdAndName() { Text = x.State.Name, Value= x.StateId.Value } : null,
    });

And the request

api/AddressDto?$count=true&$top=20&$skip=0&$orderby=state/text%20desc&$filter=(state/value%20eq%20be78ab1e-2ae3-4754-956e-7e099ea82242)

Which was wokring perfectly in the .NET Framework now thorws and error:

... .Where(t => t.Outer.Active == __TypedProperty_1 && t.Outer.StateId.HasValue ? new IdAndName { \r\n Text = t.Inner.Name, \r\n Value = t.Outer.StateId.Value \r\n }\r\n : null.Value == __TypedProperty_2)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

The problem is obvious. Somehow Odata sends all the null check expression in the query...

I`m not using AutoMapper, I do all converts like this with select. And with the nested collection properties everything is fine.... but with the single ones which should be easies it is NOT...

When I change the null check to be like this:

 query.Select(x =>
new AddressDto()
{
    Id = x.Id,
    ZipCode = x.ZipCode,
    State = x.State != null ? new IdAndName() { Text = x.State.Name, Value= x.StateId.Value } : null,
}); 

I get another error:

"The operands for operator 'Equal' do not match the parameters of method 'op_Equality'."

I do not think there is other way to perform null check...

[maumar]

full repro without odata:

        [ConditionalFact]
        public virtual void Repro23877()
        {
            using var ctx = new MyContext();
            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();
            var baseQuery = ctx.Addresses.Select(x => new AddressDto
            {
                Id = x.Id,
                ZipCode = x.ZipCode,
                State = x.StateId != null ? new IdAndName { Value = x.StateId.Value, Text = x.State.Name } : null
            });

            var guid = Guid.NewGuid();
            var query = baseQuery.Where(x => x.State.Value != guid).ToList();
        }

        public class AddressDto
        {
            public Guid Id { get; set; }
            public string ZipCode { get; set; }
            public IdAndName State { get; set; }
        }
        public class IdAndName
        {
            public Guid Value { get; set; }
            public string Text { get; set; }
        }

        public class AddressEntity
        {
            public Guid Id { get; set; }
            public string ZipCode { get; set; }
            public Guid? StateId { get; set; }
            public State State { get; set; }
        }

        public class State
        {
            public Guid Id { get; set; }
            public string Name { get; set; }
        }

        public class MyContext : DbContext
        {
            public DbSet<AddressEntity> Addresses { get; set; }
            public DbSet<State> States { get; set; }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
            }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro23877;Trusted_Connection=True;MultipleActiveResultSets=true");
            }
        }

[maumar]

in comparison, when we compare x.State.Value (or rather, IIF((a.Outer.StateId != null), new IdAndName() {Value = a.Outer.StateId.Value, Text = a.Inner.Name}, null).Value) to guid they are initially typed as Guid. However, during query optimizer, in TryOptimizeMemberAccessOverConditional we optimize left side it and type as Guid?. However we don't compensate for the type difference so the exception is thrown

[anranruye]

In my memory, it seems that ef(core) never support this kind of projection.

[maumar]

@cap7ainjack you can workaround the issue by making Value of the IdAndName a nullable guid and use:

 query.Select(x =>
new AddressDto()
{
    Id = x.Id,
    ZipCode = x.ZipCode,
    State = x.State != null ? new IdAndName() { Text = x.State.Name, Value= x.StateId.Value } : null,
}); 

[cap7ainjack]

That fixes is it. Thanks. I didnt get the Guid exception until now. I mean the previous error

"The operands for operator 'Equal' do not match the parameters of method 'op_Equality'."

is kind of replaced with Guid and nullable Guid comparison error. However, thanks again. This solves the issue.

[maumar]

there is still a bug here, reopening the issue so that we can track & fix it.

[Erythnul]

Glad to see that this is being fixed. Could the fix be considered for cherry picking into a servicing release?
This is a serious blocker for our planned upgrade path.
Thanks in advance!

[maumar]

@Erythnul sorry for the late response - the bugfix is quite risky, as it involves modifying type of the expression tree and has potential to break existing queries if we missed a case. Also, there is a relatively straightforward workaround - adding conversion to nullable. So sadly this fix won't be ported into the patch release.