Query: Subquery.contains generates invalid SQL

[CurlerRoo]

Exception:

System.Data.SqlClient.SqlException: Invalid column name 'PostId'.

Steps to reproduce

    public class Post
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual List<PostTag> PostsTags { get; set; }
    }

    public class Tag
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual List<PostTag> PostsTags { get; set; }

    }

    public class PostTag
    {
        public int PostId { get; set; }
        public int TagId { get; set; }
        public virtual Post Post { get; set; }
        public virtual Tag Tag { get; set; }
    }

    public class ApplicationDbContext : DbContext
    {
        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            builder.Entity<PostTag>().HasOne(m => m.Post).WithMany(m => m.PostsTags).HasForeignKey(m => m.PostId);
            builder.Entity<PostTag>().HasOne(m => m.Tag).WithMany(m => m.PostsTags).HasForeignKey(m => m.TagId);
            builder.Entity<PostTag>().HasKey(m => new { m.PostId, m.TagId });
        }

        protected override void OnConfiguring(DbContextOptionsBuilder builder)
        {
            builder.UseSqlServer(@"TODO: ADD CONNECTION STRING");
        }

        public DbSet<Post> Posts { get; set; }
        public DbSet<Tag> Tags { get; set; }
        public DbSet<PostTag> PostsTags { get; set; }
    }

    public static class DbInitializer
    {
        public static void Initialize(ApplicationDbContext context)
        {
            context.Database.EnsureCreated();
        }
    }

    public class Program
    {
        public static void Main(string[] args)
        {
            var db = new ApplicationDbContext();
            DbInitializer.Initialize(db);
            
            var postIds = db.PostsTags.Include(m => m.Post).Where(m => m.Tag.Name == "www").Select(m => m.PostId);
            var posts = db.Posts.Where(m => postIds.Contains(m.Id)).ToList();

            db.SaveChanges();
        }
    }

Further technical details

EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2015

[rowanmiller]

Could you post the SQL that is getting sent to the database?

[CurlerRoo]

@rowanmiller
The generated SQL:

SELECT [m].[Id], [m].[Name]
      FROM [Posts] AS [m]
      WHERE [m].[Id] IN (
          SELECT [m.Tag].[PostId] -- Expects [m.Tag].[Id] instead of [m.Tag].[PostId]
          FROM [PostsTags] AS [m1]
          INNER JOIN [Tags] AS [m.Tag] ON [m1].[TagId] = [m.Tag].[Id]
          WHERE [m.Tag].[Name] = N'www'
      )

Expected:

SELECT [m].[Id], [m].[Name]
      FROM [Posts] AS [m]
      WHERE [m].[Id] IN (
          SELECT [m.Tag].[Id]
          FROM [PostsTags] AS [m1]
          INNER JOIN [Tags] AS [m.Tag] ON [m1].[TagId] = [m.Tag].[Id]
          WHERE [m.Tag].[Name] = N'www'
      )

[smitpatel]

Shouldn't expected be

SELECT [m].[Id], [m].[Name]
      FROM [Posts] AS [m]
      WHERE [m].[Id] IN (
          SELECT [m1].[PostId]
          FROM [PostsTags] AS [m1]
          INNER JOIN [Tags] AS [m.Tag] ON [m1].[TagId] = [m.Tag].[Id]
          WHERE [m.Tag].[Name] = N'www'
      )

[CurlerRoo]

@smitpatel You are right. Thanks for correcting me.

[smitpatel]

#7691 fixes this. 😌