Unnecessary alternate key generated by convention

[FranklinWhale]

An unnecessary alternate key is created for the UserListMember table in Step 2 of the Steps to Reproduce:

    CONSTRAINT "PK_UserListMembers" PRIMARY KEY ("UserListId", "MemberId"),
    CONSTRAINT "AK_UserListMembers_MemberId_UserListId" UNIQUE ("MemberId", "UserListId"),
    CONSTRAINT "FK_UserListMembers_Users_MemberId" FOREIGN KEY ("MemberId") REFERENCES "Users" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_UserListMembers_UserLists_UserListId" FOREIGN KEY ("UserListId") REFERENCES "UserLists" ("Id") ON DELETE CASCADE

That alternate key is not generated when the word User is removed from the table name (Step 5):

    CONSTRAINT "PK_ListMembers" PRIMARY KEY ("ListId", "MemberId"),
    CONSTRAINT "FK_ListMembers_Lists_ListId" FOREIGN KEY ("ListId") REFERENCES "Lists" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_ListMembers_Users_MemberId" FOREIGN KEY ("MemberId") REFERENCES "Users" ("Id") ON DELETE CASCADE

The same behaviour is also reproducible using the Npgsql provider.

Steps to Reproduce

1. Create a new console project
2. Create a .cs file with content below:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

namespace EFCoreTest
{
    public class User
    {
        [Key]
        public string Id { get; set; }

        public ICollection<UserList> UserLists { get; set; }

        public ICollection<UserListMember> UserListMembers { get; set; }
    }

    public class UserList
    {
        [Key]
        public string Id { get; set; }

        [Required]
        public string OwnerId { get; set; }

        public User Owner { get; set; }

        public ICollection<UserListMember> UserListMembers { get; set; }
    }

    public class UserListMember
    {
        [Key]
        public string UserListId { get; set; }

        [Key]
        public string MemberId { get; set; }

        public UserList UserList { get; set; }

        public User Member { get; set; }
    }

    public class UserDbContext : DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<UserList> UserLists { get; set; }
        public DbSet<UserListMember> UserListMembers { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=EFCoreTest-ExtraAK.db");
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<UserListMember>(userListMember =>
            {
                userListMember.HasKey(ulm => new { ulm.UserListId, ulm.MemberId });
            });
        }
    }
}

3. Run the following commands:

dotnet build
dotnet ef migrations add initial
dotnet ef database update

4. Delete the Migrations folder
5. Replace the content in the .cs file created in Step 2 with:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

namespace EFCoreTest
{
    public class User
    {
        [Key]
        public string Id { get; set; }

        public ICollection<List> Lists { get; set; }

        public ICollection<ListMember> ListMembers { get; set; }
    }

    public class List
    {
        [Key]
        public string Id { get; set; }

        [Required]
        public string OwnerId { get; set; }

        public User Owner { get; set; }

        public ICollection<ListMember> ListMembers { get; set; }
    }

    public class ListMember
    {
        [Key]
        public string ListId { get; set; }

        [Key]
        public string MemberId { get; set; }

        public List List { get; set; }

        public User Member { get; set; }
    }

    public class UserDbContext : DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<List> Lists { get; set; }
        public DbSet<ListMember> ListMembers { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=EFCoreTest.db");
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<ListMember>(listMember =>
            {
                listMember.HasKey(ulm => new { ulm.ListId, ulm.MemberId });
            });
        }
    }
}

6. Repeat the commands in Step 3
7. Compare the constraints of UserListMembers in EFCoreTest-ExtraAK.db and those of ListMembers in EFCoreTest.db

Further technical details

EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.Sqlite, Npgsql.EntityFrameworkCore.PostgreSQL
Operating system: Windows 10 1803
IDE: Visual Studio Code 1.28.1

[AndriySvyryd]

Probably a duplicate of #12414