EF generates incorrect SQL

[CurlerRoo]

I'm using ASP.NET Core with Npgsql 3.2.0 and Npgsql.EntityFrameworkCore.PostgreSQL 1.1.0.

SQL schema:

CREATE TABLE posts (
    id    SERIAL PRIMARY KEY,
    name    TEXT
);

CREATE TABLE tags (
    id    SERIAL PRIMARY KEY,
    name    TEXT
);

CREATE TABLE posts_tags (
    post_id    INT NOT NULL REFERENCES posts(id),
    tag_id    INT NOT NULL REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

These below codes throw exception:

    [Table("tags")]
    public class Tag
    {
        [Column("id")]
        public int Id { get; set; }

        [Column("name")]
        public string Name { get; set; }

        public virtual List<PostTag> PostsTags { get; set; }
    }

    [Table("posts_tags")]
    public class PostTag
    {
        [Column("post_id")]
        public int PostId { get; set; }

        [Column("tag_id")]
        public int TagId { get; set; }

        public virtual Post Post { get; set; }

        public virtual Tag Tag { get; set; }
    }

    [Table("posts")]
    public class Post
    {
        [Column("id")]
        public int Id { get; set; }

        [Column("name")]
        public string Name { get; set; }

        public virtual List<PostTag> PostsTags { get; set; }
    }

    public class Context : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql("CONNECTION_STRING");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Post>(m =>
            {
                m.HasMany(x => x.PostsTags)
                .WithOne(x => x.Post)
                .HasForeignKey(x => x.PostId);
            });

            modelBuilder.Entity<Tag>(m =>
            {
                m.HasMany(x => x.PostsTags)
                .WithOne(x => x.Tag)
                .HasForeignKey(x => x.TagId);
            });

            modelBuilder.Entity<PostTag>(m =>
            {
                m.HasOne(x => x.Post)
                .WithMany(x => x.PostsTags)
                .HasForeignKey(x => x.PostId);

                m.HasOne(x => x.Tag)
                .WithMany(x => x.PostsTags)
                .HasForeignKey(x => x.TagId);

                m.HasKey(x => new { x.PostId, x.TagId });
            });
        }

        public virtual DbSet<Post> Posts { get; set; }

        public virtual DbSet<Tag> Tags { get; set; }

        public virtual DbSet<PostTag> PostsTags { get; set; }
    }

    public class Program
    {
        public static void Main(string[] args)
        {
            var db = new Context();
            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();
        }
    }

SQL generated by EF (incorrect)

        SELECT "m"."id", "m"."name"

	FROM "posts" AS "m"

	WHERE "m"."id" IN (

	    SELECT "m.Tag"."post_id"

	    FROM "posts_tags" AS "m1"

	    INNER JOIN "tags" AS "m.Tag" ON "m1"."tag_id" = "m.Tag"."id"

	    WHERE "m.Tag"."name" = 'www'

	)

SQL expected:

        SELECT "m"."id", "m"."name"

	FROM "posts" AS "m"

	WHERE "m"."id" IN (

	    SELECT "m.Tag"."id"

	    FROM "posts_tags" AS "m1"

	    INNER JOIN "tags" AS "m.Tag" ON "m1"."tag_id" = "m.Tag"."id"

	    WHERE "m.Tag"."name" = 'www'

	)

[roji]

@Pc-lover, this looks like a general EF Core question, i.e. not Npgsql-related. These belong in the EF Core repo rather than here.

A quick look shows this is an attempt to do many-to-many. You may want to follow the guidelines in the EF Core docs.

If you suspect this is an Npgsql-specific issue please post back here.

[CurlerRoo]

@roji, I think this is a bug. Not sure if it belongs to EF Core or Npgsql, though.

var db = new Context();
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();

If I remove Include(m => m.Post) from the above code. Then EF would generate SQL correctly. Otherwise, the generated SQL is syntaxcally wrong.

For example (it works fine):

var db = new Context();
var postIds = db.PostsTags.Where(m => m.Tag.Name == "www").Select(m => m.PostId);
var posts = db.Posts.Where(m => postIds.Contains(m.Id)).ToList();