Code First Entity Framework creates List<> property wrong

Question

So, I have a code first model which looks like this:

public class SeciovniContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Permission> Permissions { get; set; }
}

---

public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int UserID { get; set; }

    [StringLength(30)]
    [Required]
    public string FirstName { get; set; }

    [StringLength(30)]
    [Required]
    public string LastName { get; set; }

    [StringLength(50)]
    [EmailAddress]
    [Required]
    public string Email { get; set; }

    public List<Permission> Permisions { get; set; }
}

---

public class Permission
{
    [Key]
    // TODO: [Index(IsUnique = true)]
    public string PermissionType { get; set; }

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

And, in my seed, I have this:

var permissions = new List<Permission> {
    new Permission() { PermissionType = "Admin", Description = "Admin" },
    new Permission() { PermissionType = "Editor", Description = "Editor" }
};

context.Permissions.AddRange(permissions);
context.SaveChanges();

var user1 = new User()
{
    Email = "[email protected]",
    FirstName = "David",
    LastName = "Jones",
    Permisions = permissions,
};

var user2 = new User()
{
    Email = "[email protected]",
    FirstName = "John",
    LastName = "Doe",
    Permisions = permissions.Where(p => p.PermissionType == "Admin").ToList()
};

context.Users.Add(user1);
context.Users.Add(user2);

context.SaveChanges();

I would expect this to somehow link a single user with N permissions. And, thus that David would have permissions "Admin" and "Edit" whereas John would just have "Edit".

However, when the database is made, it looks like this:

-----------------------------------------
| User Table                            |
-----------------------------------------
| UserID | Email | FirstName | LastName |
-----------------------------------------


-----------------------------------------
| Permissions Table                     |
-----------------------------------------
| PermissionType | Description | UserID |
-----------------------------------------

Thus, David has the Admin permission (but no edit) and John has the edit. Aside form that, doing db.Users.FirstOrDefault(/* query */).Permissions is turning up empty as well.

Since I'm expecting there to be a finite number of Permissions, I'm really expecting something more like the below, which has a "middle" table which links the two together. But I don't want to have to manually maintain that link table. Is there a way to do what I'm trying to do: have a finite number of permissions (that don't duplicate) with an infinite number of users?

Here's more what I would expect if this database were created by hand:

-----------------------------------------
| User Table                            |
-----------------------------------------
| UserID | Email | FirstName | LastName |
-----------------------------------------


--------------------------------
| Permissions Table            |
--------------------------------
| PermissionType | Description |
--------------------------------


---------------------------
| UserPermissions Table   |
---------------------------
| PermissionType | UserId |
---------------------------

Answer 1

What are you asking is known as many-to-many relationship with auto junction table. It was supported in EF6, but not supported yet in EF Core, though there is a plan to be added in some future version.

So currently you have no other choice than maintaining explicit join entity as explained in the documentation.