Database seeding only works for one model, not a second

Question

I'm seeding test data into two models, Author and Post. The seeding works for Author, but seems to fail silently for Post.

Edit: I think I've found the source of the issue. I've caught the exception, and the message is

Cannot insert explicit value for identity column in table Post when IDENTITY_INSERT is set to OFF.

There may be a workaround for this in this StackOverflow post, but I was wondering if anyone has any ideas why this occurs? Is this because we shouldn't manually set the value of the primary key?

I am loosing following this Microsoft Docs tutorial, but using ASP.NET MVC Core 2.1. The basic strategy is that the Main() method in the Program class calls DbInitializer.Initialize(), which seeds the data if there are no Authors in the context.

I have stepped over the DbInitializer class, and the code goes through the same process for both models, looping through the seeded array, adding it to the context and (apparently) saving changes. However, while the Author database fields are populated with seed data, the Post fields are not.

Edit: While stepping through and inspecting the generated context, sometimes the Author fields have strange ID values and sometimes the Post fields have strange ID values :-2147492647, -2147492646, etc... I have tried to change PostID to ID on the off chance that that caused the issue, but the same issue occurs with that field name.

My second thought is wondering whether this has something to do with the many-to-one relationship between Post and Author. However, I've tried to seed Post data with only the Title field (so excluding the foreign key AuthorID), and that hasn't made a difference. I'm new to ASP.NET so my hope is that I'm missing something obvious. Thanks for any direction.

MyWebsiteContext.cs

using Microsoft.EntityFrameworkCore;
using MyWebsite.Models;

namespace MyWebsite.Models
{
    public class MyWebsiteContext : DbContext
    {
        public MyWebsiteContext (DbContextOptions<MyWebsiteContext> options)
            : base(options)
        {
        }
        public DbSet<MyWebsite.Models.Author> Author { get; set; }

        public DbSet<MyWebsite.Models.Post> Post { get; set; }
    }
}

DbInitializer.cs

using MyWebsite.Models;

namespace MyWebsite.Data
{
    public class DbInitializer
    {
        public static void Initialize(MyWebsiteContext context)
        {
            // Look for any authors; if none, seed DB.
            if (context.Author.Any())
            {
                return; // DB has already been seeded.
            }

            var authors = new Author[]
            {
                new Author{ FirstName="Terry",LastName="Pratchett",SignUpDate=DateTime.Parse("2019-04-01")},
                new Author{ FirstName="Neil",LastName="Gaiman",SignUpDate=DateTime.Parse("2019-03-02")},
            };

            foreach (Author a in authors)
            {
                context.Author.Add(a);
            }
            context.SaveChanges();

            var posts = new Post[]
            {
                new Post{PostID=1,Title="Title of post 1"},
                new Post{PostID=2,Title="Title of post 2"},
                new Post{PostID=3,Title="Title of post 3"},
            };
            foreach (Post p in posts)
            {
                context.Post.Add(p);
            }
            context.SaveChanges();
        }
    }
}

Program.cs

using System;
using Microsoft.AspNetCore;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using MyWebsite.Data;
using MyWebsite.Models;

namespace MyWebsite
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var host = CreateWebHostBuilder(args).Build();

            using (var scope = host.Services.CreateScope())
            {
                var services = scope.ServiceProvider;

                try
                {
                    var context = services.GetRequiredService<MyWebsiteContext>();
                    DbInitializer.Initialize(context);
                }
                catch(Exception ex)
                {
                    var logger = services.GetRequiredService<ILogger<Program>>();
                    logger.LogError(ex, "An error occurred creating the DB.");
                }
            }
            host.Run();
        }
        public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseStartup<Startup>();
    }
}

Post.cs

namespace MyWebsite.Models
{
    public class Post   // Post entity is dependent on Author class
    {
        public int PostID { get; set; } // primary key
        public string Title { get; set; }
        public int AuthorID { get; set; } // foreign key
        public Author Author { get; set; }
    }
}

Author.cs

namespace MyWebsite.Models
{
    public class Author
    {
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime SignUpDate { get; set; }
    }
}

score 1 · Accepted Answer · answered Jul 06 '19 at 12:34

Try this to see if it work. I have this same issue since I working with seeding relational entity. My recommend is try to put try catch in your code to see if it there any exception

In your Program.cs

using (var scope = host.Services.CreateScope())
{
  var services = scope.ServiceProvider;
  SeedData.Initialize(services).Wait();
}

Then seed like this

public static async Task Initialize(IServiceProvider serviceProvider) {
  using (var context = new ApplicationDbContext(
                serviceProvider.GetRequiredService<DbContextOptions<ApplicationDbContext>>()))
            {
                if (!context.Posts.Any())
                {
                    post.Comments = comments;

                    await SeedPost(context);
                }
            }
}

You can see that my Post entity have list of comments so I will also assign list of comment into Post object then seed in once go

You can view my full code here

Hey, thank you very much. I didn't realize that an exception was being generated. (It was being caught with my original code but I didn't notice that happening.) That led me to the answer. Much appreciated. — heds1, Jul 06 '19 at 22:49

Farhad Zamani · Answer 2 · 2019-07-06T12:52:04.663

The first time it performs the SaveChange() operation successfully, but the second time it takes an error to add posts due to the lack of a foreign key of author, and the operation is not performed. Always use one SaveChange!

You must add foreign key AuthorId to Post Model or map Author Object to Post.Author object to create relation

 public static void Initialize(MyWebsiteContext context)
        {
            // Look for any authors; if none, seed DB.
            if (context.Author.Any())
            {
                return; // DB has already been seeded.
            }

            var authors = new Author[]
            {
                new Author{ FirstName="Terry",LastName="Pratchett",SignUpDate=DateTime.Parse("2019-04-01")},
                new Author{ FirstName="Neil",LastName="Gaiman",SignUpDate=DateTime.Parse("2019-03-02")},
            };

            foreach (Author a in authors)
            {
                context.Author.Add(a);
            }

            var posts = new Post[]
            {
                new Post{PostID=1,Title="Title of post 1" ,Author = authors[0] },
                new Post{PostID=2,Title="Title of post 2" ,Author = authors[0]},
                new Post{PostID=3,Title="Title of post 3" ,Author = authors[0]},
            };
            foreach (Post p in posts)
            {
                context.Post.Add(p);
            }
            context.SaveChanges();
        }

Thank you for the comment and showing me how to map `Author` to `Post.Author`! It looks like I had to a) map `Author` to `Post.Author`, and b) remove the `PostID` assignment. — heds1, Jul 06 '19 at 22:48

score 0 · Answer 3 · answered Jul 06 '19 at 23:01

Two things needed to be fixed in the original code:

The definition of PostID during seeding. The two options are either a) remove that definition, or b) explicitly turn on IDENTITY_INSERT in SQL Server. The second method is discussed here, but for my purposes, simply removing the definition worked.
Mapping the Author object to Post.Author. Simply calling new Post{Title="Title of post 1"} was not sufficient; specifying the Author was required.

Relevant DbInitializer segment:

            var posts = new Post[]
            {
                new Post{Title = "Title of post 1", Author = authors[0]},
                new Post{Title = "Title of post 2", Author = authors[0]},
                new Post{Title = "Title of post 3", Author = authors[1]},
            };

Thanks for all the help.

Database seeding only works for one model, not a second

3 Answers3