locked
Migrations on Entity Framework 5 and SqlServerCe.4.0 with GUID RRS feed

  • Question

  • I'm using a GUID in my program:

    [Key]
    public Guid bookingID { get; set; }

    When creating the database (starting the program - non-existing database - EF creates it) this works fine, I can insert rows, etc.

    I'm currently in development however and would like to add database migrations using Code First.
    Whenever I'm trying to add a migration I get informed, that an identity column has to be of bigint or integer and must not be NULL.
    When checking the table "bookings" in the Server Explorer however I correctly see: "uniqueidentifier" as the DataType and the column is also marked as primary key - therefore showing me that indead the identity column does NOT have to be of type bigint or integer.

    I already tried to "reset" the migrations in an attempt to avoid this migration causing the problem and starting off with a "clean" database as described in: http://stackoverflow.com/questions/11679385/reset-entity-framework-migrations

    However whenever I run the "first" migration (Initial) the problem occurs again, because for some reason the migration wants to change the fields again. (Shouldn't the "base" database be created from my data model and the first migration wouldn't contain any changes?)

    Thursday, November 7, 2013 9:24 AM

Answers

  • Sorry being late for the reply.

    >>Where IS the created database?

    With a default installation of SQL Server and no connection string the database will be created in the DATA

    directory of the installation, for example something like:

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA (for 2008 R2 version).

    The name of the database is namespace.contextname, for example: MyNamespace.MyContext.mdf (and .ldf). Under this name you

    can also find them in SQL Server Management Studio.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fred Bao Wednesday, November 20, 2013 8:04 AM
    Monday, November 18, 2013 8:12 AM

All replies

  • Hello,

    It seems strange for that, because I made a test to do the operation which is similar with you.

    My entity class:

    public class Book
    
        {
    
            public Guid BookID { get; set; }
    
            public string BookName { get; set; }
    
        }
    

    It is very simple. And my Context class:

    public class BookContext : DbContext
    
        {
    
            public DbSet<Book> Books { get; set; }
    
    
            public BookContext()
    
                : base("BookContext")
    
            {
    
            }
    
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
    
            {
    
                modelBuilder.Entity<Book>().HasKey(d => d.BookID).Property(d => d.BookID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    
            }
    
        }
    

    And the created class by migration:

    public partial class InitialCreate : DbMigration
    
        {
    
            public override void Up()
    
            {
    
                CreateTable(
    
                    "dbo.Books",
    
                    c => new
    
                        {
    
                            BookID = c.Guid(nullable: false, identity: true),
    
                            BookName = c.String(),
    
                        })
    
                    .PrimaryKey(t => t.BookID);
    
                
    
            }
    
            
    
            public override void Down()
    
            {
    
                DropTable("dbo.Books");
    
            }
    
        }
    

    The only difference is that I used SQLServer 2012.

    And for reset the database, we usually use command:

    Update-Database –TargetMigration: InitialCreate

    This will roll all the way back to an initial database.

    For more information regarding code first migration:

    http://msdn.microsoft.com/en-us/data/jj591621.aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 8, 2013 1:59 AM
  • Thank you for your reply.

    I tried using the setup in the context instead of using the [Key] Annotation, but that didn't work for me.
    I seem to experience some basic problem however - as (if I understand correctly) creating a new initial migration should result in a migration that creates ALL the tables.
    Mine only creates two of them and drops/adds some keys.

    The steps I took to get a "clean" migration were:

    • Delete the Migrations Folder in my "Datamodel" Project
    • Check in the new structure to source control
    • Close Visual Studio 2012 and delete the database I was working with (mydemo.sdf)
    • Reopen Visual Studio 2012 and run "Enable-Migrations -Force"
    • Run "Add-Migration InitialDatabase

    Afterwards the migrations file looks like it has been based on an existing database, but I already deleted the whole bin/debug folder where the program compiles to - therefore I'm a bit puzzled why I don't get an initial migration that just creates all the tables.

    Tuesday, November 12, 2013 9:43 AM
  • I think the steps should be:

    • Reopen Visual Studio 2012
    • Run program to create the Database
    • Run "Enable-Migrations -Force "Add-Migration InitialDatabase

    The migration should be based on an existing database.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 12, 2013 10:02 AM
  • I tried that - but onfortunately without success.
    (I still get a Migration that creates two tables, drops foreign Keys on others and adds columns to certain tables.)

    If I create a new database based on the model - why is there ANY migration in the first place anyways?
    I could understand:

    Version A) The first "migration" just creates all the tables needed
    Version B) The first migration does not include anything because there were no changes after creating the database

    The database itself works without problems by the way (but I don't want to create a new blank database every time I change something that's what migrations are for I thought ;))

    Tuesday, November 12, 2013 10:15 AM
  • >>Version A) The first "migration" just creates all the tables needed

    The migration will only create codes for existing table in database at the first time using the commend:

    Enable-Migrations -ContextTypeName Code_First.S2013_11.BookContext
    public partial class InitialCreate : DbMigration
        {
            public override void Up()
            {
                CreateTable(
                    "dbo.Books",
                    c => new
                        {
                            BookId = c.Int(nullable: false),
                            BookName = c.String(),
                            BookPrice = c.String(),
                        })
                    .PrimaryKey(t => t.BookId);
                
            }
            
            public override void Down()
            {
                DropTable("dbo.Books");
            }
        }

    >>but I don't want to create a new blank database every time I change something that's what migrations are for I thought ;)

    It is strange that why it will create a new blank database, because the migration will not create a database, it is based in an existing database.

    If it is possible, could you please share your program and table structure to the skydirive so that we can fing the reason.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 13, 2013 8:36 AM
  • Sorry I meant to say "empty" database (no data in the tables) not "clean" database (it does have a structure)

    I uploaded my datamodel to https://skydrive.live.com/redir?resid=FD58BF8AEF0E320A!3431&authkey=!AHwmQn0yqKmc2D0 - thank you for your support.

    Wednesday, November 13, 2013 10:01 AM
  • Hello,

    It seems that the codes is ok. I test it and find it ok.

    My test steps:

    Create the database:

    DbContextFactory DbContextFactory = new DbContextFactory();
                using (KassabuchContext db = DbContextFactory.Create())
                {
                    db.Database.Create();
                }

    Delete the existing migration folder, and use command:

     Enable-Migrations 

    The Created Codes:

    namespace KassabuchKOO2013Datenmodell.Migrations
    {
        using System;
        using System.Data.Entity.Migrations;
        
        public partial class InitialCreate : DbMigration
        {
            public override void Up()
            {
                CreateTable(
                    "dbo.bookings",
                    c => new
                        {
                            bookingID = c.Guid(nullable: false, identity: true),
                            Date = c.DateTime(nullable: false),
                            Cashin = c.Decimal(nullable: false, precision: 18, scale: 2),
                            Cashout = c.Decimal(nullable: false, precision: 18, scale: 2),
                            Text = c.String(),
                            Number = c.Int(nullable: false),
                            Waregroup_waregroupID = c.Int(nullable: false),
                            Outlet_outletID = c.Int(nullable: false),
                        })
                    .PrimaryKey(t => t.bookingID)
                    .ForeignKey("dbo.waregroups", t => t.Waregroup_waregroupID, cascadeDelete: true)
                    .ForeignKey("dbo.outlets", t => t.Outlet_outletID, cascadeDelete: true)
                    .Index(t => t.Waregroup_waregroupID)
                    .Index(t => t.Outlet_outletID);
                
                CreateTable(
                    "dbo.waregroups",
                    c => new
                        {
                            waregroupID = c.Int(nullable: false, identity: true),
                            Name = c.String(),
                            Visible = c.Boolean(nullable: false),
                        })
                    .PrimaryKey(t => t.waregroupID);
                
                CreateTable(
                    "dbo.outlets",
                    c => new
                        {
                            outletID = c.Int(nullable: false, identity: true),
                            Name = c.String(nullable: false),
                            Visible = c.Boolean(nullable: false),
                            Outletheader_HeaderID = c.Int(nullable: false),
                        })
                    .PrimaryKey(t => t.outletID)
                    .ForeignKey("dbo.headers", t => t.Outletheader_HeaderID, cascadeDelete: true)
                    .Index(t => t.Outletheader_HeaderID);
                
                CreateTable(
                    "dbo.headers",
                    c => new
                        {
                            HeaderID = c.Int(nullable: false, identity: true),
                            Shortdescription = c.String(nullable: false),
                            Street = c.String(nullable: false),
                            Zip = c.Int(nullable: false),
                            City = c.String(nullable: false),
                            Phonenr = c.String(nullable: false),
                            Faxnr = c.String(),
                        })
                    .PrimaryKey(t => t.HeaderID);
                
                CreateTable(
                    "dbo.outletImages",
                    c => new
                        {
                            OutletID = c.Int(nullable: false),
                            OutletImage = c.Binary(nullable: false),
                        })
                    .PrimaryKey(t => t.OutletID)
                    .ForeignKey("dbo.outlets", t => t.OutletID)
                    .Index(t => t.OutletID);
                
                CreateTable(
                    "dbo.Waregroup_Outlet",
                    c => new
                        {
                            outlet_outletID = c.Int(nullable: false),
                            waregroup_waregroupID = c.Int(nullable: false),
                        })
                    .PrimaryKey(t => new { t.outlet_outletID, t.waregroup_waregroupID })
                    .ForeignKey("dbo.outlets", t => t.outlet_outletID, cascadeDelete: true)
                    .ForeignKey("dbo.waregroups", t => t.waregroup_waregroupID, cascadeDelete: true)
                    .Index(t => t.outlet_outletID)
                    .Index(t => t.waregroup_waregroupID);
                
            }
            
            public override void Down()
            {
                DropIndex("dbo.Waregroup_Outlet", new[] { "waregroup_waregroupID" });
                DropIndex("dbo.Waregroup_Outlet", new[] { "outlet_outletID" });
                DropIndex("dbo.outletImages", new[] { "OutletID" });
                DropIndex("dbo.outlets", new[] { "Outletheader_HeaderID" });
                DropIndex("dbo.bookings", new[] { "Outlet_outletID" });
                DropIndex("dbo.bookings", new[] { "Waregroup_waregroupID" });
                DropForeignKey("dbo.Waregroup_Outlet", "waregroup_waregroupID", "dbo.waregroups");
                DropForeignKey("dbo.Waregroup_Outlet", "outlet_outletID", "dbo.outlets");
                DropForeignKey("dbo.outletImages", "OutletID", "dbo.outlets");
                DropForeignKey("dbo.outlets", "Outletheader_HeaderID", "dbo.headers");
                DropForeignKey("dbo.bookings", "Outlet_outletID", "dbo.outlets");
                DropForeignKey("dbo.bookings", "Waregroup_waregroupID", "dbo.waregroups");
                DropTable("dbo.Waregroup_Outlet");
                DropTable("dbo.outletImages");
                DropTable("dbo.headers");
                DropTable("dbo.outlets");
                DropTable("dbo.waregroups");
                DropTable("dbo.bookings");
            }
        }
    }
    

    This is OK.

    And it will not clean the table data.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 14, 2013 9:46 AM
  • Interesting - thank you very much for testing.
    Perhaps I'm creating the database in a wrong way:

    My program has to work with a set of CompactSQL databases and I want to manage those with the connection strings.

    Therefore I iterate through the connection strings and create a new Service for each of them which gets passed the connection string and initialises the context.

    Main View Model
    foreach (System.Configuration.ConnectionStringSettings css in ConfigurationManager.ConnectionStrings)
    ...
    
    // Data Service
     _kbcontext = new KassabuchContext(_connectionStringSettings.Name);            

    The connection string is defined in App.Config like this at the moment

    <connectionStrings>
        <add name="Kassabuch KÖÖ"
             providerName="System.Data.SqlServerCe.4.0"
             connectionString="Data Source=kassabuchkoodatenbasis.sdf"/>
    </connectionStrings>

    When instructing the service to create the context - the database gets created if it does not exist already.

    If I use this way to create the database (for example by deleting the current one) - I experience the problem described above.

    In addition: should just stating "Enable-Migrations" already create the first migration or did you just not add the command "Add-Migration InitialMigration" in your description? Because just running Enable-Migrations by itself does not create one for me.
    • Edited by diBianco Thursday, November 14, 2013 10:34 AM
    Thursday, November 14, 2013 10:29 AM
  • >>In addition: should just stating "Enable-Migrations" already create the first migration or did you just not add the command "Add-Migration InitialMigration" in your description? Because just running Enable-Migrations by itself does not create one for me.

    If the class library has only one DbContext, using Enable-Migrations only should be ok. If there are multiple DbContext in the libraries, we should specify the DbContext Name like:

    Enable-Migrations -ContextTypeName Code_First.S2013_11.BookContext

    It will create a Configuration class and InitialCreate class.

    And for the -force command, it is used to cover the already exist migration folder.

    For more information, please have a look at the link below:

    http://msdn.microsoft.com/en-us/data/jj591621.aspx 
    It has a detail explain for these commands.

    Regards.

     

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 15, 2013 1:21 AM
  • Thank you for your continuous efforts.
    I found the source of the problem which is that I assumed that the database created is tied to the creation of the project that the class library (the one I shared on skydrive) is included in.

    This doesn't seem to be the case however - if I alter the context in my DbContextFactory to

    return new KassabuchContext("TestDB");

    the migration that gets created actually is fine.

    The last question left for me is: Where IS the created database? The article you linked (thank you) suggests SQL Express which is not running on my machine and localdb ((LocalDb)\v11.0) which I checked but does not contain an according database.

    Friday, November 15, 2013 10:24 AM
  • Sorry being late for the reply.

    >>Where IS the created database?

    With a default installation of SQL Server and no connection string the database will be created in the DATA

    directory of the installation, for example something like:

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA (for 2008 R2 version).

    The name of the database is namespace.contextname, for example: MyNamespace.MyContext.mdf (and .ldf). Under this name you

    can also find them in SQL Server Management Studio.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fred Bao Wednesday, November 20, 2013 8:04 AM
    Monday, November 18, 2013 8:12 AM
  • Thank you (also for marking the answer, sorry I was a bit late)
    Wednesday, November 20, 2013 8:17 AM