How to saturate an ICollection navigation property in a view?

[dharmaturtle]

Given the following, how can I saturate Post_Tag?

    public partial class PostView
    {
        public PostView()
        {
            Post_Tag = new HashSet<Post_Tag>();
        }
        public int Id { get; set; }
        public int AuthorId { get; set; }

        public virtual Author Author { get; set; }
        public virtual ICollection<Post_Tag> Post_Tag { get; set; }
        public virtual ICollection<Author_Tag> Author_Tag { get; set; }
    }

    modelBuilder.Entity<PostView>(entity =>
    {
        entity.HasMany(d => d.Post_Tag)
            .WithOne()
            .HasForeignKey(x => x.PostId);

        entity.ToView("PostView").HasNoKey();
    });

This works:

        dbContext.PostView
            .Include(x => x.Author)
            .First()
            .Author.Name

But this does not:

        context.PostView
            .Include(x => x.Post_Tag)
            .First()

It throws this error:

System.InvalidOperationException: Lambda expression used inside Include is not valid.
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessInclude(NavigationExpansionExpression source, Expression expression, Boolean thenInclude)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Single[TSource](IQueryable`1 source)
   at EFViewAndManyToMany.UnitTest1.Test2() in C:\Code\EFViewAndManyToMany\EFViewAndManyToMany\UnitTest1.cs:line 22

It works if I drop the HasNoKey in the modelBuilder, but this doesn't scale; subsequently attempting to saturate Author_Tag uses PostViewId which doesn't exist. I could solve this by naming a key (e.g. [Key]), but then I would be stuck with one key per view. I could also solve this with query syntax and explicitly naming the JOIN predicate, but for various reasons I want to avoid that.

Here's the code, error, and generated SQL if I drop the HasNoKey

        context.PostView
            .Include(x => x.Author_Tag)
          .Single()

Microsoft.Data.SqlClient.SqlException: Invalid column name 'PostViewId'.

    SELECT [t].[Id], [t].[AuthorId], [a].[AuthorId], [a].[TagId], [a].[PostViewId]
    FROM (
        SELECT TOP(2) [p].[Id], [p].[AuthorId]
        FROM [PostView] AS [p]
    ) AS [t]
    LEFT JOIN [Author_Tag] AS [a] ON [t].[Id] = [a].[PostViewId]
    ORDER BY [t].[Id], [a].[AuthorId], [a].[TagId]

(Lastly, why is there an ORDER BY?)

Thanks!

Further technical details

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

[ajcvickers]

@dharmaturtle Is there a specific database schema that you need this to map to? If so, can you post it?

[dharmaturtle]

I've uploaded a minimum working example here: https://github.com/dharmaturtle/EFViewAndManyToMany

The latest commit's modelBuilder looks like:

entity.HasMany(d => d.Author_Tag)
    .WithOne()
    .HasForeignKey(x => x.AuthorId)
    .HasPrincipalKey(x => x.AuthorId); // newly added

However, it causes this error to occur:

Microsoft.Data.SqlClient.SqlException: Invalid column name 'AuthorId1'.

SELECT [p].[Id], [p].[AuthorId], [p].[AuthorId1], [a].[AuthorId], [a].[TagId]
FROM [PostView] AS [p]
LEFT JOIN [Author_Tag] AS [a] ON [p].[AuthorId] = [a].[AuthorId]
ORDER BY [p].[Id], [a].[AuthorId], [a].[TagId]

[ajcvickers]

@dharmaturtle I've spent some time looking at you code but I'm still not sure what you are trying to achieve. What is the purpose of PostView? Or, more specifically, what is its purpose in relation to Post? Why not use Post instead?

[dharmaturtle]

It's supposed to be a minimal working example. Post is a simple table, and PostView is a simple view of that table. I could create a more complicated example where the view is a JOIN of perhaps 2 tables, but I tried to keep it simple to keep the focus on the issue, which is how to join on a view.

[ajcvickers]

@dharmaturtle EF Core doesn't allow the same database relationships to be mapped to more than one different entity type. In the code you posted, both Post and PostView are configured to use the same relationships (navigation properties and FKs).

This is not something I have seen people try to do before. I will discuss with the team whether we should support this in some way.

[dharmaturtle]

Would a reasonable workaround be to create a new FK between the view and the table I want to JOIN on? (...is that even possible in MSSQL?)

[ajcvickers]

@dharmaturtle I'm still not sure exactly what you are trying to achieve so I'm going to go into more detail in several areas. Some of this may not be relevant.

Querying stored procedures

First, let's assume you have a defined model with no views. To keep it super simple, something like this:

public class Blog
{
    public int Id { get; set; }
    
    public ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

Now let's say I add a PostView to my database that can return Post entities. I can use it like this:

var posts = context.Posts.FromSqlRaw("SELECT * FROM PostView").ToList();

This generates:

SELECT * FROM PostView

I can also compose over this, for example, with Include:

var posts = context.Posts.FromSqlRaw("SELECT * FROM PostView").Include(e => e.Blog).ToList();

SELECT [p].[Id], [p].[BlogId], [b].[Id]
FROM (
    SELECT * FROM PostView
) AS [p]
INNER JOIN [Blogs] AS [b] ON [p].[BlogId] = [b].[Id]

In addition, see #17270 for more work in this area we are doing for EF Core 5.0

The critical thing to notice here is that I did not create a new CLR type for PostView.

Projecting to different types

Leaving views aside for the moment, EF does allow me to project from my entity types to other CLR types. For example:

public class BlogDto
{
    public int Id { get; set; }
}

public class PostDto
{
    public int Id { get; set; }
    
    public int BlogId { get; set; }
    public BlogDto Blog { get; set; }
}

var posts = context.Posts.Select(e => new PostDto
{
    Id = e.Id, 
    BlogId = e.BlogId, 
    Blog = new BlogDto
    {
        Id = e.Blog.Id
    }
});

I can also combine this with using a view:

var posts = context.Posts.FromSqlRaw("SELECT * FROM PostView").Select(e => new PostDto
{
    Id = e.Id, 
    BlogId = e.BlogId, 
    Blog = new BlogDto
    {
        Id = e.Blog.Id
    }
});

SELECT [p].[Id], [p].[BlogId], [b].[Id]
FROM (
    SELECT * FROM PostView
) AS [p]
INNER JOIN [Blogs] AS [b] ON [p].[BlogId] = [b].[Id]

What doesn't work

The thing that doesn't work is trying to use a mix of CLR types in different ways in different places. For example:

public class PostDto
{
    public int Id { get; set; }
    
    public int BlogId { get; set; }
    public Blog Blog { get; set; } // Uses Blog here
}

This results in an ambiguous model where relationships and FKs may have different meanings in different contexts.

Hopefully this makes things a bit clearer.