locked
EF 4.1: Code First, Seed Data Creation Order and Foreign Key RRS feed

  • Question

  • Using DropCreateDatabaseAlways<AnyContext>, can someone confirm:

    1. that the order of table creation is alphabetical and not related to the order in which they are written in your Initilizer?
    2. and if there is any option to set the order in which they are created using EF code First?

    Using EF 4.1 Code First and MVC3 Scaffolding, I have no difficulty creating and seeding unrelated tables. Nor is there any problem creating both related and unrelated tables. However, creating and seeding related tables continually fails in instances where the first letter in the name of the Foreign Key table follows the first letter of the table in question.

    In the instant case, I created a reference class called "UnitOfMeasure" and a class that used that reference called "Product". Each time the database is dropped and an attempt is made to recreate, it fails, indicating the a foreign key violation in the Product_UnitOfMeasure constraint. This failure dissappears when you rename the reference table to "AUnitOfMeasure" or "MyMeasure" or any other name that would precede "Product" alphabetically.

    Again, the tables can be generated without problem. This is only an issue for using the Seed function inside the initializer.

     


    • Edited by gahayden Monday, August 29, 2011 2:42 PM spelling
    Monday, August 29, 2011 2:41 PM

All replies

  • Hi,

    It could be better to show us the minimal amount of code needed to repro the issue (such those two tables with the pk, fk columns) if enough to reproduce the issue. Also what happens if you do do the same thing outside of the Seed function ? It would be strange that EF can order inserts the right way everywhere else except here...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Monday, August 29, 2011 3:15 PM
  • Sure. Here are 3 classes, UnitOfMeasure, Measure and Product. By alternating use between UnitOfMeasure and Measure, you can reproduce the issue.

    With the class Measure uncommented and UnitOfMeasure commented out, access index.cshtml for either Product or Measure in the running app. This will drop and recreate the db properly and work fine. Change this by commenting out Measure and using UnitOfMeasure and the process will fail.

     

    namespace SeedOrder
    {
        public class UnitOfMeasure
        {
            [Key]
            public int UnitOfMeasureId { getset; }
            public string Name { getset; }
        }
     
        //public class Measure
        //{
        //    [Key]
        //    public int MeasureId { get; set; }
        //    public string Name { get; set; }
        //}
        
        public class Product
        {
            [Key]
            public int ProductId { getset; }
            public string Name { getset; }
            
            public int UnitOfMeasureId { getset; }
            [ForeignKey("UnitOfMeasureId")]
            public virtual UnitOfMeasure UnitOfMeasure { getset; }
     
            //public Guid MeasureId { get; set; }
            //[ForeignKey("MeasureId")]
            //public virtual Measure Measure { get; set; }    
        }
    }

     

    My assertion is that regardless of the order that the DbSet ocurrs in your DbContext, the order in which the tables are created in SQL Server appears to be alphabetical.

    Wednesday, August 31, 2011 2:51 PM
  • As to your question "Also what happens if you do do the same thing outside of the Seed function ?", when revert to using TSQL scripts to seed the same data, everything works fine. Of course it would. You run the scripts after all of the tables are created.
    Wednesday, August 31, 2011 2:54 PM
  • I'm a bit confused. Sometimes you tells that "tables can be generated, this is an issue in Seed" and at other times "the order in which tables are created in SQL Server appears to be alphabetical" so I'm not 100 % sure if the issue is :
    - when creating tables
    - when trying to populate them with initial data (for now it seems rather that ?)

    I gave this a try but for now I'm unable to repro this issue (using EF 4.1 update 1). IMO this is likely an issue in your code as such a major problem would have been very strongly reported (basically it makes EF unusable).

    At this point, your best bet is likely to post the shortest compilable console app that would show this issue so that others can have a look at your code.

    If the code is correct, for now, the only reason I could think of would be to use still a buggy much earlier CTP release rather than the RTM product ??

    BTW could it be some misunderstanding about the DropCreateDatabaseAlways strategy ? What if you delete the db by hand and use the CreateDatabaseIfNotExists strategy instead ? could it be just that as the db is drop/recreated on each postback you get some inconsistencies as you are webbrowing your MVC app pages or is this a kind of "unit test page" ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Edited by Patrice ScribeMVP Wednesday, August 31, 2011 6:40 PM DropCreateDatabase misunderstanding ?
    Wednesday, August 31, 2011 6:28 PM
  • The version of EntityFramework is 4.1.10715.0 and the issue is solely related to the seed data. When I run the application and navigate to either unitofmeasures/index.chtml or products/index.chtml, an error occurs on the page

    An exception occurred while initializing the database. See the InnerException for details

    Where the inner exception indicates:

    "The INSERT statement conflicted with the FOREIGN KEY constraint \"Product_UnitOfMeasure\". The conflict occurred in database \"SeedTestDB\", table \"dbo.UnitOfMeasures\", column 'UnitOfMeasureId'.\r\nThe statement has been terminated."

    To me, this suggests that lazy-loading is attempting to load the seed data into Products ahead of UnitsOfMeasure. Otherwise, if the INSERT was performed when the tables where created, then a failure at that point would be reflected prior to presenting the UI.

    The offending code is simply:

    public ViewResult Index() {
        return View(context.UnitOfMeasures.ToList());
    }

    Can you confirm that this is not the behavior you are getting?

     

    Wednesday, August 31, 2011 7:38 PM
  • The strategy you are using ALWAYS drop/create the db (including seeding data) when you use the context for the first time. So the issue is actually in the Seed function. Put a breakpoint there.

    So this is perhaps not the strategy you want in an MVC app (I would expect this rather in a unit test to always start from known data ?). That said it should still order correctly all DELETE/INSERT/UPDATE operations that are sent to the db (as it happens when using SaveChanges).

    You could perhaps use SQL Server Profiler to see the statement that fails including with the exact value used for each column. This is more likely the Seed method that you should show...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Thursday, September 1, 2011 7:57 AM
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 6, 2011 12:11 PM
  • Hi Alan,

    We were using SQL Profiler and were aware of the order in which tabeles were created and populated before I started the thread. All I was looking for was confirmation of what occurs in EF.

    We've basically just abandoned any seeding in code first and reverted back to using TSQL scripts to seed the data. Since the order that we enter data in scripts is subject to foreign key relationships I have to suspect that this is also the case in Code-First. However, while we are able to order the insertions in scripts, I have yet to see any method to do so using seeding in EF.

    If on the other hand EF is supposed to recognize reference tables and seed them first, then we must have another problem. So I'm still asking the original question - can anyone confirm if the seeding order is alphabetical?

    Tuesday, September 6, 2011 1:43 PM
  • No,

    What I have seen until now is that EF orders the update/insert/delete commands based on the table relations and I never see someone reporting an issue on that (actually I see this one time when creating cycles in the DB such as A referencing B that references A ).

    Seeding works fine here when tables are named Product and UnitOfMeasure.

    Furthermore If Seeding doesn't get the right order then DbContext.SaveChanges should fail as well as it is 99% sure this is just exactly the same thing except that Seeding is done only when needed by calling the appropriate method.

    IMO it would be a problem in your Seed method.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Tuesday, September 6, 2011 3:08 PM
  • Well it certainly makes sense that 'SaveChanges' would function similarly whether performing a normal insert or during seeding.

    Furthermore, there may be A-B-A relationships as you indicate since we used

    modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>()
    

    to resolve other issues our design for tables used for handling enums.

    In any case, we'll reinvestigate the issue, since it is looking like one of design. However, for us this issue appeared when we have only the 2 tables (Product and UnitofMeasure) in a single application created soley for testing the issue.

     

     


    • Edited by gahayden Tuesday, September 6, 2011 5:15 PM additional thoughts
    Tuesday, September 6, 2011 5:11 PM
  • As I said earlier when an issue doesn't have an obvious cause, it's likely best to post the minimal amount of code needed to repro the issue (my test had about 80 lines) so that others can confirm they do see the same behavior and possibly find its root cause. Else only those who actually had the exact same issue for the same reason could help. If you post some code everybody seeing the code could try to help.

    Sometimes  there is some kind of IP concern. But we don't care reading the real code. Actually we are not willing to read your actual code. Just what is needed to repro the issue with faked data, renamed tables or whatever. The less lines it has the better it is. We don't care about whatever lines are not needed to repro the issue. Wondering as I suggested several times to post some code...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Edited by Patrice ScribeMVP Tuesday, September 6, 2011 6:07 PM Intellectual property concerns ?
    Tuesday, September 6, 2011 6:04 PM
  • Ok, here are the two classes UnitOfMeasure and Products, the scaffolded UnitOfMeasuresController, the ProductsController, the DbContext the SeedOrderContext and the Global.asax including my initilizer SeedOrderInitilizer and revised Application_Start.

    Basically, I have two classes. I scaffold the controllers for each and then modify Application_Start to use my Initilizer and override Seed() to populate the tables. When I run the app, both the HomeControler actions Index and About function but when I navigate to Products (http://localhost:52601/products) the process fails in the ProductsController inside the ViewResult Index() method: 

    public ViewResult Index()
            {
                return View(context.Products.Include(product => product.UnitOfMeasure).ToList());
            }

    with the error shown as:

    {"The INSERT statement conflicted with the FOREIGN KEY constraint \"Product_UnitOfMeasure\". The conflict occurred in database \"SeedTestDB\", table \"dbo.UnitOfMeasures\", column 'UnitOfMeasureId'.\r\nThe statement has been terminated."}

    Here are the classes:

     

    namespace SeedOrder
    {
        public class UnitOfMeasure
        {
            [Key]
            public int UnitOfMeasureId { getset; }
            public string Name { getset; }
        }
     
        
        public class Product
        {
            [Key]
            public int ProductId { getset; }
            public string Name { getset; }
            
            public int UnitOfMeasureId { getset; }
            [ForeignKey("UnitOfMeasureId")]
            public virtual UnitOfMeasure UnitOfMeasure { getset; }
        }
    }
     
    Here is the controller created throuth :Scaffold Controller UnitOfMeasure
     
    namespace SeedOrder.Controllers
    {   
        public class UnitOfMeasuresController : Controller
        {
            private SeedOrderContext context = new SeedOrderContext();
     
            //
            // GET: /UnitOfMeasures/
     
            public ViewResult Index()
            {
                return View(context.UnitOfMeasures.ToList());
            }
     
            //
            // GET: /UnitOfMeasures/Details/5
     
            public ViewResult Details(int id)
            {
                UnitOfMeasure unitofmeasure = context.UnitOfMeasures.Single(x => x.UnitOfMeasureId == id);
                return View(unitofmeasure);
            }
     
            //
            // GET: /UnitOfMeasures/Create
     
            public ActionResult Create()
            {
                return View();
            } 
     
            //
            // POST: /UnitOfMeasures/Create
     
            [HttpPost]
            public ActionResult Create(UnitOfMeasure unitofmeasure)
            {
                if (ModelState.IsValid)
                {
                    context.UnitOfMeasures.Add(unitofmeasure);
                    context.SaveChanges();
                    return RedirectToAction("Index");  
                }
     
                return View(unitofmeasure);
            }
            
            //
            // GET: /UnitOfMeasures/Edit/5
     
            public ActionResult Edit(int id)
            {
                UnitOfMeasure unitofmeasure = context.UnitOfMeasures.Single(x => x.UnitOfMeasureId == id);
                return View(unitofmeasure);
            }
     
            //
            // POST: /UnitOfMeasures/Edit/5
     
            [HttpPost]
            public ActionResult Edit(UnitOfMeasure unitofmeasure)
            {
                if (ModelState.IsValid)
                {
                    context.Entry(unitofmeasure).State = EntityState.Modified;
                    context.SaveChanges();
                    return RedirectToAction("Index");
                }
                return View(unitofmeasure);
            }
     
            //
            // GET: /UnitOfMeasures/Delete/5
     
            public ActionResult Delete(int id)
            {
                UnitOfMeasure unitofmeasure = context.UnitOfMeasures.Single(x => x.UnitOfMeasureId == id);
                return View(unitofmeasure);
            }
     
            //
            // POST: /UnitOfMeasures/Delete/5
     
            [HttpPost, ActionName("Delete")]
            public ActionResult DeleteConfirmed(int id)
            {
                UnitOfMeasure unitofmeasure = context.UnitOfMeasures.Single(x => x.UnitOfMeasureId == id);
                context.UnitOfMeasures.Remove(unitofmeasure);
                context.SaveChanges();
                return RedirectToAction("Index");
            }
        }
    }
     
    Here is the controller created through "Scaffold Controller Products" 
     
    namespace SeedOrder.Controllers
    {   
        public class ProductsController : Controller
        {
            private SeedOrderContext context = new SeedOrderContext();
     
            //
            // GET: /Products/
     
            public ViewResult Index()
            {
                return View(context.Products.Include(product => product.UnitOfMeasure).ToList());
            }
     
            //
            // GET: /Products/Details/5
     
            public ViewResult Details(int id)
            {
                Product product = context.Products.Single(x => x.ProductId == id);
                return View(product);
            }
     
            //
            // GET: /Products/Create
     
            public ActionResult Create()
            {
                ViewBag.PossibleUnitOfMeasures = context.UnitOfMeasures;
                return View();
            } 
     
            //
            // POST: /Products/Create
     
            [HttpPost]
            public ActionResult Create(Product product)
            {
                if (ModelState.IsValid)
                {
                    context.Products.Add(product);
                    context.SaveChanges();
                    return RedirectToAction("Index");  
                }
     
                ViewBag.PossibleUnitOfMeasures = context.UnitOfMeasures;
                return View(product);
            }
            
            //
            // GET: /Products/Edit/5
     
            public ActionResult Edit(int id)
            {
                Product product = context.Products.Single(x => x.ProductId == id);
                ViewBag.PossibleUnitOfMeasures = context.UnitOfMeasures;
                return View(product);
            }
     
            //
            // POST: /Products/Edit/5
     
            [HttpPost]
            public ActionResult Edit(Product product)
            {
                if (ModelState.IsValid)
                {
                    context.Entry(product).State = EntityState.Modified;
                    context.SaveChanges();
                    return RedirectToAction("Index");
                }
                ViewBag.PossibleUnitOfMeasures = context.UnitOfMeasures;
                return View(product);
            }
     
            //
            // GET: /Products/Delete/5
     
            public ActionResult Delete(int id)
            {
                Product product = context.Products.Single(x => x.ProductId == id);
                return View(product);
            }
     
            //
            // POST: /Products/Delete/5
     
            [HttpPost, ActionName("Delete")]
            public ActionResult DeleteConfirmed(int id)
            {
                Product product = context.Products.Single(x => x.ProductId == id);
                context.Products.Remove(product);
                context.SaveChanges();
                return RedirectToAction("Index");
            }
        }
    }
     
    Here is the minimalist DbContext:
     
    namespace SeedOrder.Models
    {
        public class SeedOrderContext : DbContext
        {
            public DbSet<SeedOrder.UnitOfMeasure> UnitOfMeasures { getset; }
            public DbSet<SeedOrder.Product> Products { getset; }
        }
    }
     
    Here is the app start and my seedInitilizer from Global.asax        
     
    protected void Application_Start() {
        AreaRegistration.RegisterAllAreas();
        RegisterGlobalFilters(GlobalFilters.Filters);
        RegisterRoutes(RouteTable.Routes);
        System.Data.Entity.Database.SetInitializer(new SeedOrderInitilizer());
    }
     
    private class SeedOrderInitilizer : System.Data.Entity.DropCreateDatabaseAlways<SeedOrderContext> {
        protected override void Seed(SeedOrderContext context) {
            var uoms = new[] {
                new UnitOfMeasure { Name = "each" },
                new UnitOfMeasure { Name = "lbs" },
                new UnitOfMeasure { Name = "kgs" },
                new UnitOfMeasure { Name = "box" },
                new UnitOfMeasure { Name = "pkg" }
            };
            foreach (var uom in uoms)
                context.UnitOfMeasures.Add(uom);
                    
            var products = new[] {
                new Product { Name = "1000", UnitOfMeasureId = 3 }, 
                new Product { Name = "1001", UnitOfMeasureId = 3 }, 
                new Product { Name = "1002", UnitOfMeasureId = 3 }, 
                new Product { Name = "1003", UnitOfMeasureId = 3 } 
            };
            foreach (var product in products)
                context.Products.Add(product);
        }
    }
    Wednesday, September 7, 2011 2:39 PM
  • I'm about to leave but noticed that I was using the navigation property when you are using the id. So I had the same issue. It disappears if doing :

                    
            var products = new[] {
                new Product { Name = "1000", UnitOfMeasure = uoms[2] }, 
    
                new Product { Name = "1001", UnitOfMeasure =uoms[2] }, 
    
                new Product { Name = "1002", UnitOfMeasure =uoms[2] }, 
                new Product { Name = "1003", UnitOfMeasure = uoms[2]} 
            };
    

    This is why I missed your issue.

    If using the default convention (that is UnitOfMeasure_ID it still works when using the id). If using the FK attribute it doesn't even if tweaking to my taste. Hopefully I still miss something but at least it seems a step in the right direction (it seems a problme in using the attribute even if it seems I doubled/triplec checked it's good).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".


    • Edited by Patrice ScribeMVP Wednesday, September 7, 2011 5:22 PM Corrected the silly code formatting issue
    Wednesday, September 7, 2011 5:20 PM
  • Well, I believe I began to understood what happens on the way back to home...

    Before leaving I noticed  that my code (using the references) gives "kgs" id 1 (despite being not the first in the array). It made me think that EF inserts first related entities in the right order and then take care of the other unrelated ones (rather than looking at the general table structure it seems to look at the REAL object graph).

    Obviously it works the usual way (i.e if you insert a new product and affect an id that is in the database it will works even if the product is not inserted before unrelated new unitMeasures you could add at the same time).

    So I begin to think that the issue is that EF has no way to know how the instances you are trying to insert are related. As they seem unrelated the last criteria is likely the alphabetical order and then succcess depends on the table name.

    It would work if EF could *assume* (which is what you implicitely do) what the IDs would be once the insert is done (which would work also only if nothing is currently stored in the db so this is really something that wouldn't make sense to implement).

    So to fix this you could likely :
    - either give explicit an id to the UnitOfMeasure instances. Then EF will be able to find out how those instances are related
    - or use references (as I've done) and again EF will be able to find out how those instances are related and sort the corresponding operations as needed.

    Basically you can't assume what the id value WILL be for an element that is not yet saved. You have either to give the id an explicit value or use the navigation properties so that EF can look at the CURRENT relation beetween objects rather than to ASSUME what they should be (not sure if I'm clear).

     Simply said EF uses the id or the navigation property to use the relation between those entities and sort the database updates accordingly. As the objets have no relation at the time they are saved (no match on ids, navigation property not used), EF sees them as unrelated objects and saves them in whatever the default order is (it would work, if you already had an id 3 saved in the db).

     

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Edited by Patrice ScribeMVP Thursday, September 8, 2011 8:46 AM Simply said
    • Proposed as answer by MaxiF29 Wednesday, March 25, 2020 5:22 AM
    Wednesday, September 7, 2011 6:33 PM
  • Brilliant, thank you for this. Solved my problem!

    I just defined the Id of each record to be seeded to the DB.

    Friday, June 13, 2014 12:22 AM
  • Hello, I read all the thread and your contribution was the one that solved my problem !!

    What puzzled me was that since I created each entity's id as incremental auto, I didn't suspect it was going to let me set it. In fact, when I tried to enter a record by hand in the DB (before I decided to implement the Seed method), it did not allow me to assign it an id, instead the field was protected and after entering the other data of the fields from the table, it was automatically assigned.

    THANK YOU VERY MUCH FOR THE SOLUTION!!!!!! A TOTAL SUCCESS !!!!
    Wednesday, March 25, 2020 5:28 AM
  • Hi Patrice,
    This only raises a question for me, I am setting id = 1 in different entities, but when I create them in the DB it does them with the value id = 2. And I removed the previous DB completely, with which I suppose not it may be saving the previous id and therefore put the number that follows it.
    Any ideas about what I can do to make the ids remain with the value that I put in the Seed method?

    Thank you!!
    Wednesday, March 25, 2020 6:32 AM