Incorrect results returned when joining a key-less view to an entity

[kevbite]

When trying to join a key-less view to an entity the incorrect results are returned and sometimes the join returns another row's results.

Steps to reproduce

Executing the following code:

using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace EFBug
{
    class Program
    {
        static async Task Main(string[] args)
        {
            using (var context = new BloggingContext())
            {
                await context.Database.EnsureDeletedAsync();
                await context.Database.EnsureCreatedAsync();
            
                var blog1 = new Blog {Url = "a"};
                var blog2 = new Blog {Url = "b"};
                var blog3 = new Blog {Url = "c"};
                var blog4 = new Blog {Url = "d"};
                var blog5 = new Blog {Url = "e"};

                await context.AddAsync(blog1);
                await context.AddAsync(blog2);
                await context.AddAsync(blog3);
                await context.AddAsync(blog4);
                await context.AddAsync(blog5);
                await context.SaveChangesAsync();
            
            
                var blog1post1 = new Post {PostId = 1, BlogId = blog1.BlogId};
                var blog1post2 = new Post {PostId = 2, BlogId = blog1.BlogId};
                var blog1post3 = new Post {PostId = 3, BlogId = blog1.BlogId};
                await context.AddAsync(blog1post1);
                await context.AddAsync(blog1post2);
                await context.AddAsync(blog1post3);

                var blog3post3 = new Post { PostId = 4, BlogId = blog3.BlogId };
                await context.AddAsync(blog3post3);

                await context.SaveChangesAsync();

                await context.Database.ExecuteSqlRawAsync(@"CREATE VIEW [dbo].[BlogView]
AS
SELECT        BlogId, Url
FROM            dbo.Blogs
GO");
            }

            using (var context = new BloggingContext())
            {
                var blogs = context.BlogView.Select(blog => new
                {
                    BlogId = blog.BlogId,
                    BlogUrl = blog.Url,
                    PostIds = context.Posts
                        .Where(post => post.BlogId == blog.BlogId)
                        .Select(post => post.PostId)
                        .ToArray(),
                })
                    .OrderBy(x => x.BlogId)
                    .Take(2) // Only get back 2 blog view items
                    .ToArray();

                Console.WriteLine($@"Total Blog Posts: {blogs.Length}");
                foreach (var blog in blogs)
                {
                    Console.WriteLine($"Blog: {blog.BlogId} / {blog.BlogUrl} (total posts: {blog.PostIds.Length})");
                    foreach (var id in blog.PostIds)
                    {
                        Console.WriteLine($"- {id}");
                    }
                }
            }
        }
    }

    public class BloggingContext : DbContext
    {
        public DbSet<BlogViewItem> BlogView { get; set; }

        public DbSet<Post> Posts { get; set; }
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(
                @"Data Source=localhost;Initial Catalog=TestEF;Integrated Security=true");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Post>(ob =>
            {
                ob.ToTable("Posts");
                ob.HasKey(o => new { o.PostId, o.BlogId });
                ob.Property(o => o.BlogId).IsRequired();
            });

            modelBuilder.Entity<Blog>(ob =>
            {
                ob.ToTable("Blogs");
                ob.HasKey(o => o.BlogId);
                ob.Property(o => o.Url);
            });

            modelBuilder
                .Entity<BlogViewItem>(eb =>
                {
                    eb.HasNoKey();
                    eb.ToView("BlogView");
                });

            base.OnModelCreating(modelBuilder);
        }
    }

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

    }

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

    public class Post
    {
        public int BlogId { get; set; }
        public int PostId { get; set; }
    }
}

Returns the following results.

Profiling SQL Server we get the following executed:

exec sp_executesql N'SELECT [t].[BlogId], [t].[Url], [p].[PostId], [p].[BlogId]
FROM (
    SELECT TOP(@__p_0) [b].[BlogId], [b].[Url]
    FROM [BlogView] AS [b]
    ORDER BY [b].[BlogId]
) AS [t]
LEFT JOIN [Posts] AS [p] ON [t].[BlogId] = [p].[BlogId]
ORDER BY [t].[BlogId], [p].[PostId], [p].[BlogId]',N'@__p_0 int',@__p_0=2

This seems all fine and executing it, it returns the correct results as expected.

If we change the view to be a keyed entity, by configuring the model builder as the following

modelBuilder
    .Entity<BlogViewItem>(eb =>
    {
        eb.HasKey(o => o.BlogId);
        eb.ToView("BlogView");
    });

This generates exactly the same SQL.

exec sp_executesql N'SELECT [t].[BlogId], [t].[Url], [p].[PostId], [p].[BlogId]
FROM (
    SELECT TOP(@__p_0) [b].[BlogId], [b].[Url]
    FROM [BlogView] AS [b]
    ORDER BY [b].[BlogId]
) AS [t]
LEFT JOIN [Posts] AS [p] ON [t].[BlogId] = [p].[BlogId]
ORDER BY [t].[BlogId], [p].[PostId], [p].[BlogId]',N'@__p_0 int',@__p_0=2

However, the output from the console app is the following:

Further technical details

EF Core version: 3.1.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10

[kevbite]

Also, we setup the data as the following:

var blog1 = new Blog {Url = "a"};
var blog2 = new Blog {Url = "b"};
var blog3 = new Blog {Url = "c"};
var blog4 = new Blog {Url = "d"};
var blog5 = new Blog {Url = "e"};

await context.AddAsync(blog1);
await context.AddAsync(blog2);
await context.AddAsync(blog3);
await context.AddAsync(blog4);
await context.AddAsync(blog5);
await context.SaveChangesAsync();


var blog2post1 = new Post {PostId = 1, BlogId = blog2.BlogId};
await context.AddAsync(blog2post1);

var blog3post1 = new Post { PostId = 1, BlogId = blog3.BlogId };
await context.AddAsync(blog3post1);

var blog5post1 = new Post { PostId = 1, BlogId = blog5.BlogId };
await context.AddAsync(blog5post1);

await context.SaveChangesAsync();

and execute the following query:

var blogs = context.BlogView.Select(blog => new
{
    BlogId = blog.BlogId,
    BlogUrl = blog.Url,
    Titles = context.Posts
        .Where(post => post.BlogId == blog.BlogId)
        .Select(post => post.PostId)
        .ToArray(),
})
.OrderBy(x => x.BlogId)
.ToArray();

This returns incorrect lists for the root objects

[ajcvickers]

@smitpatel to follow up.

[smitpatel]

We should block the scenario.
Projecting a collection for keyless entity is not supported. When a collection is used in projection, the outer entities are repeated for each inner entity. And we need to identify outer entities so that we can put all inner entities which are correlated in correct group. Since keyless entities don't have any way to identify when they change in outer results (since there is no unique PK), we cannot determine when the group changes. So in above example we put all the posts in same group and since the last post is null you get 1 result with 3 posts. We should not let collection appear in projection if outer does not have identifier to determine different records.

[kevbite]

@ajcvickers / @smitpatel Thanks for a fast update on the issue 👍.

[maumar]

poaching