I am now experimenting with EF Core and decided to try out SQLite as a provider. I followed some directions on the internet and also considered this topic from stackoverflow's feed. Somehow I don't get my many to many tables created.
This is how my DbContext file looks like:
public class BloggingContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
public DbSet<User> Users { get; set; }
public DbSet<Group> Groups { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("Filename=./Blogging.db");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<UserGroup>().HasKey(x => new { x.UserId, x.GroupId });
modelBuilder.Entity<UserGroup>()
.HasOne(pc => pc.User)
.WithMany(p => p.UserGroups)
.HasForeignKey(pc => pc.UserId);
modelBuilder.Entity<UserGroup>()
.HasOne(pc => pc.Group)
.WithMany(c => c.UserGroups)
.HasForeignKey(pc => pc.GroupId);
}
}
public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
public List<Post> Posts { get; set; }
}
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int MadeBy { get; set; }
public int BlogId { get; set; }
public Blog Blog { get; set; }
}
public class User
{
[Key]
public int Id { get; set; }
public ICollection<UserGroup> UserGroups { get; set; }
}
public class Group
{
[Key]
public int Id { get; set; }
public ICollection<UserGroup> UserGroups { get; set; }
}
// Needed for many-to-many https://stackoverflow.com/questions/29442493/how-to-create-a-many-to-many-relationship-with-latest-nightly-builds-of-ef-core
public class UserGroup
{
[ForeignKey("User")]
public int UserId { get; set; }
public User User { get; set; }
[ForeignKey("Group")]
public int GroupId { get; set; }
public Group Group { get; set; }
}
for some reason when I start the initial migration it doesn't generated the tables Users, Groups. This the log from the migration process:
$ dotnet ef database update
Project ConsoleApp.SQLiteVS (.NETCoreApp,Version=v1.1) was previously compiled. Skipping compilation.
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
Executed DbCommand (35ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "__EFMigrationsHistory" (
"MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
"ProductVersion" TEXT NOT NULL
);
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
Applying migration '20170117155520_MyFirstMigration'.
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Blogs" (
"BlogId" INTEGER NOT NULL CONSTRAINT "PK_Blogs" PRIMARY KEY AUTOINCREMENT,
"Url" TEXT
);
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Groups" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_Groups" PRIMARY KEY AUTOINCREMENT
);
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Users" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_Users" PRIMARY KEY AUTOINCREMENT
);
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Posts" (
"PostId" INTEGER NOT NULL CONSTRAINT "PK_Posts" PRIMARY KEY AUTOINCREMENT,
"BlogId" INTEGER NOT NULL,
"Content" TEXT,
"MadeBy" INTEGER NOT NULL,
"Title" TEXT,
CONSTRAINT "FK_Posts_Blogs_BlogId" FOREIGN KEY ("BlogId") REFERENCES "Blogs" ("BlogId") ON DELETE CASCADE
);
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "UserGroup" (
"UserId" INTEGER NOT NULL,
"GroupId" INTEGER NOT NULL,
CONSTRAINT "PK_UserGroup" PRIMARY KEY ("UserId", "GroupId"),
CONSTRAINT "FK_UserGroup_Groups_GroupId" FOREIGN KEY ("GroupId") REFERENCES "Groups" ("Id") ON DELETE CASCADE,
CONSTRAINT "FK_UserGroup_Users_UserId" FOREIGN KEY ("UserId") REFERENCES "Users" ("Id") ON DELETE CASCADE
);
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX "IX_Posts_BlogId" ON "Posts" ("BlogId");
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX "IX_UserGroup_GroupId" ON "UserGroup" ("GroupId");
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20170117155520_MyFirstMigration', '1.1.0-rtm-22752');
Done.
As you can see, the tables seem to get created but when I open the db file with Firefox'es DB Manager I can only see Posts and Blogs tables created.
How can I fix that?
