ef core 的 include 如何生成 inner join的语句

Question

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

        public List<Post> Posts { get; set; }
    }
    
    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public int Rating { get; set; }

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

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>()
                .HasMany(b => b.Posts)
                .WithOne(p => p.Blog)
                .HasForeignKey(p => p.BlogId)
                .OnDelete(DeleteBehavior.NoAction).IsRequired();
}

using (var context = new BloggingContext())
{
        var blogs = context.Blogs
              .Include(blog => blog.Posts.Where(blog => blog.Content == "xxxx"))
              .ToList();
}

生成的SQL：

      SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [t].[PostId], [t].[AuthorId], [t].[BlogId], [t].[Content], [t].[Rating], [t].[Title]
      FROM [Blogs] AS [b]
      LEFT JOIN (
          SELECT [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
          FROM [Posts] AS [p]
          WHERE [p].[Content] = N'xxxx'
      ) AS [t] ON [b].[BlogId] = [t].[BlogId]
      ORDER BY [b].[BlogId], [t].[PostId]

希望生成的SQL：

      SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [t].[PostId], [t].[AuthorId], [t].[BlogId], [t].[Content], [t].[Rating], [t].[Title]
      FROM [Blogs] AS [b]
      INNER JOIN (
          SELECT [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
          FROM [Posts] AS [p]
          WHERE [p].[Content] = N'xxxx'
      ) AS [t] ON [b].[BlogId] = [t].[BlogId]
      ORDER BY [b].[BlogId], [t].[PostId]

Answer 1

var blogs = context.Blogs.Where(blog => blog.Content == "xxxx");
    blogs.Include(p => p.Posts).SelectMany(d => d.Posts).Load();