Efcore: EF core 3 query LastOrDefault on entity with OwnsOne cannot translate expression

[onesoftdk]

In EF Core 3.1: calling LastOrDefault(), Last(), LastOrDefaultAsync() or LastAsync() on entity with OwnsOne throws InvalidOperationException. FirstOrDefault is working.

Steps to reproduce

I have a entity like this, with a property that is mapped as an owned entity:

```C#
public class Foo
{
public int Id { get; set; }

public DateTimeWithTimeZone DoneAt { get; set; }

}

public class DateTimeWithTimeZone
{
public DateTimeOffset Time { get; set; }

public string TimeZone { get; set; }

}

And the database configuration looking like this:
```C#
public void Configure(EntityTypeBuilder<Foo> builder)
{
    builder.HasKey(x => x.Id);

    builder.OwnsOne(x => x.DoneAt, y =>
    {
        y.WithOwner();
        y.Property(z => z.Time).HasColumnName("DoneAt");
        y.Property(z => z.TimeZone).HasColumnName("DoneAtTimeZone");
    });
}

I can insert a Foo entity into the datbase with EF and get the list of Foos back for the database:
```C#
var okList = apiDbContext.Foos.ToList();
var okFoo = apiDbContext.Foos.FirstOrDefault(x => x.PatientId == 1122);

But when I what to query for LastOrDefault:
```C#
var errorFoo = apiDbContext.Foos.LastOrDefault(x => x.Id == 1122);

I'm getting this error:

System.InvalidOperationException: 'The LINQ expression 'DbSet<Prescription>
    .Where(p => p.Id == 1122)
    .Select(p => (IncludeExpression(
        EF.Property<DateTimeWithTimeZone>(p, "DoneAt"), DoneAt)
    ))
    .LastOrDefault()' 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().

Further technical details

EF Core version: 3.1.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer 3.1.0
Target framework: .NET Core 3.1
Operating system: Windows 10
IDE:Visual Studio 2019 16.3.7

[MintPlayer]

Dear OneSoftDk,

Since EF Core 3.0 you cannot use complex expressions in in your LINQ queries applied to a DbSet.
This is done so in order to prevent client-side LINQ execution from remaining undetected, since they have a huge performance hit on a database containing 100.000's or millions of records.

Take your Linq query for instance:

DbSet<Prescription>
    .Where(p => p.Id == 1122)
    .Select(p => (IncludeExpression(
        EF.Property<DateTimeWithTimeZone>(p, "DoneAt"), DoneAt)
    ))
    .LastOrDefault()

The expression Where(p => p.Id == 1122) can be translated to SQL, so no problem there.
The next expression

Select(p => (IncludeExpression(
    EF.Property<DateTimeWithTimeZone>(p, "DoneAt"), DoneAt)
))

cannot be translated to SQL since you are using a .NET method. The translation to SQL stops after the Where clause. In EFCore 2.2 EF would execute the SQL on the database already and perform the remaining part of the LINQ query on the resulting array.

This resulted in developers complaining that their application performed fine in development (Database set with 1000 records), but was slow in a production environment (Database set with 1M records). That's why Microsoft decided to prevent Client-side LINQ queries to go undetected. Therefor, in your case you must now yield the resultset already before executing a LINQ query that cannot be translated to SQL:

DbSet<Prescription>
    .Where(p => p.Id == 1122)
    .ToArray()
    // The remaining part of the LINQ query cannot be translated to SQL
    .Select(p => (IncludeExpression(
        EF.Property<DateTimeWithTimeZone>(p, "DoneAt"), DoneAt)
    ))
    .LastOrDefault();

[onesoftdk]

Dear MintPlayer

Please be aware that my LINQ query isn't looking like this:

DbSet<Prescription>
    .Where(p => p.Id == 1122)
    .ToArray()
    // The remaining part of the LINQ query cannot be translated to SQL
    .Select(p => (IncludeExpression(
        EF.Property<DateTimeWithTimeZone>(p, "DoneAt"), DoneAt)
    ))
    .LastOrDefault();

My LINQ queryes is looking like this:

var okFoo = apiDbContext.Foos.FirstOrDefault(x => x.Id == 1122);

And this

var okFoo = apiDbContext.Foos.LastOrDefault(x => x.Id == 1122);

The first one using FirstOrDefault is working fine; the last one using LastOrDefault is throwing the error.
Currently, I'm using a workaround that is also working fine:

var okFoo = apiDbContext.Foos
         .OrderByDecending(x => x.Id)
         .FirstOrDefault(x => x.Id == 1122);

[ajcvickers]

@onesoftdk Looks like you are hitting #18211.

[MintPlayer]

Sorry I've mistaken the error message for the query. According to what @ajcvickers references, does the following query work?

var okFoo = apiDbContext.Foos.OrderBy(x => x.Id).FirstOrDefault(x => x.PatientId == 1122);