GroupBy with Rank on Owned Entity property not working

[Santehnikua]

GroupBy with Rank on Owned Entity property not working

I have entities where owned entity is used to store dates in my custom format. They are configured through Model Builder and configuration class (сonfiguration with [NotMapped] annotation behaves similarly), and produce in database two fields: varchar - SomeName_Date and integer SomeName_DateAccuracy. Simple OrderBy without GroupBy working well, but when i trying to get only last row per grouping field and use GroupBy(x=> x.SomeField) .Select(x => x.OrderByDescending(y => y.StartDate.Date).First()) - i got translation failed. Same code with group, select and order by simple properties works well.

Example

For a reproducible example, i modified GetStarted a bit from the documentation to show exactly what is not working and what is working. GetStarted.zip

Code

This GroupBy will not work and produce Translation failed:

var brokenGroupByBlogId = db.Posts.GroupBy(x => x.BlogId)
    .Select(x => x.OrderByDescending(y => y.StartDate.Date).First());

This GroupBy is working without problems.

var workingGroupByBlogId = db.Posts.GroupBy(x => x.BlogId)
    .Select(x => x.OrderByDescending(y => y.PostId).First());

Stack trace

Translation of 'EF.Property<CustomDate>(EntityShaperExpression: 
    Post
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
, "StartDate")' failed. Either the query source is not an entity type, or the specified property does not exist on the entity type.

Provider and version information

EF Core version: 7.0.5 (and 8.0.0-preview.4) Database provider: Pomelo.EntityFrameworkCore.MySql 7.0.0 / Microsoft.EntityFrameworkCore.Sqlite 7.0.5 Target framework: NET 7.0 Operating system: Windows 11, Ubuntu 22.04.2 IDE: Visual Studio 2022 17.5.4

[ajcvickers]

Note for triage: this is a regression in EF7. SQL generated by EF Core 6:

      SELECT "t0"."PostId", "t0"."BlogId", "t0"."Content", "t0"."Title", "t0"."StartDate_Date"
      FROM (
          SELECT "p"."BlogId"
          FROM "Posts" AS "p"
          GROUP BY "p"."BlogId"
      ) AS "t"
      LEFT JOIN (
          SELECT "t1"."PostId", "t1"."BlogId", "t1"."Content", "t1"."Title", "t1"."StartDate_Date"
          FROM (
              SELECT "p0"."PostId", "p0"."BlogId", "p0"."Content", "p0"."Title", "p0"."StartDate_Date", ROW_NUMBER() OVER(PARTITION BY "p0"."BlogId" ORDER BY "p0"."StartDate_Date" DESC) AS "row"
              FROM "Posts" AS "p0"
          ) AS "t1"
          WHERE "t1"."row" <= 1
      ) AS "t0" ON "t"."BlogId" = "t0"."BlogId"

Code:

using var db = new BloggingContext();

db.Database.EnsureDeleted();
db.Database.EnsureCreated();

var blog = new Blog
{
    Url = "http://blogs.msdn.com/adonet",
    Posts = { new Post
    {
        Title = "Hello World",
        Content = "I wrote an app using EF Core!",
        StartDate = new CustomDate
        {
            Date = "0014-01-1990"
        }
    },
        new Post
        {
            Title = "Hello World second",
            Content = "I wrote an app using EF Core!",
            StartDate = new CustomDate
            {
                Date = "0017-01-1990"
            }
        }        
    }
};

var secondblog = new Blog
{
    Url = "http://blogs.msdn.com/adonet",
    Posts =
    {
        new Post
        {
            Title = "Hello World third",
            Content = "I wrote an app using EF Core!",
            StartDate = new CustomDate
            {
                Date = "0014-02-1995"
            }
        }
    }
};

db.AddRange(blog, secondblog);
db.SaveChanges();

var brokenGroupByBlogId = db.Posts.GroupBy(x => x.BlogId)
    .Select(x => x.OrderByDescending(y => y.StartDate.Date).First());

foreach (var post in brokenGroupByBlogId)
{
    Console.WriteLine(post.Title + " - " + post.BlogId + " - " + post.StartDate.Date);
}

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options
            .UseSqlite("Data Source=test.db")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; } = new();
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public CustomDate StartDate { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

[Owned]
public class CustomDate
{
    public string Date { get; set; }
}

[TeckTn]

Is there any workaround for this bug ?

[stevendarby]

Regression tag? Servicing consider?

[Santehnikua]

I managed this by using FromSqlRaw and writing the necessary query manually...

[DPDmancul]

@ajcvickers This is still bugged in EF 8 and using raw sql queries could not be considered a workaround for this impacting regression