CreateUpdateDDLScript Function creates different SQL than dialog

[Janko]

hi there, 

i trying to get programmatic creation and updating the database to work, for this i orientate me on this forum thread.

the "update database from model" dialog generates different sql than the CreateUpdateDDLScript function.

It seems that it forgets the one-to-one associations?

I tried to set the ShouldUpdateSchema property on the assiciation if it is a MetaForeignKeyAssociation, but there is not property like this.

what i am missing?

the schema is here.

my update logic code is 

public static void UpdateOrCreateDb()
    {
        using (eaccomDb dbContext = new eaccomDb())
        {

            MetadataContainer metadata = dbContext.Metadata;
            foreach (MetaPersistentType type in metadata.PersistentTypes)
            {
                // This will create all entity tables in model, but not tables for joins
                type.ShouldUpdateSchema = true;

                // Now handle table joins for associations
                foreach (MetaMember member in type.Members)
                {
                    MetaNavigationMember navMember = member as MetaNavigationMember;
                    if (navMember != null)
                    {
                        MetaJoinTableAssociation joinAssociation = navMember.Association as MetaJoinTableAssociation;
                        if (joinAssociation != null)
                        {
                            joinAssociation.ShouldUpdateSchema = true; // Only do if there is a join association
                        }
                    }
                }
            }

            BackendConfiguration config = eaccomDb.GetBackendConfiguration();

            using (OpenAccessContext update_context = new OpenAccessContext("DbConnection", config, metadata))
            {
                Telerik.OpenAccess.ISchemaHandler schemaHandler = update_context.GetSchemaHandler();
                string script = null;
                if (schemaHandler.DatabaseExists())
                {
                    script = schemaHandler.CreateUpdateDDLScript(null);
                }
                else
                {
                    schemaHandler.CreateDatabase();
                    script = schemaHandler.CreateDDLScript();
                }
                if (!string.IsNullOrEmpty(script))
                {
                    schemaHandler.ExecuteDDLScript(script);
                }
            }
        }
    }

the sql generated by the dialog

-- eaccom.db.DbLogin
CREATE TABLE [DbLogin] (
    [CreateDate] datetime NOT NULL,         -- _createDate
    [Id] int NOT NULL,                      -- _id
    [IsActive] tinyint NOT NULL,            -- _isActive
    [LastAccess] datetime NOT NULL,         -- _lastAccess
    [Login] nvarchar(255) NULL,             -- _login
    [PasswortHash] nvarchar(255) NULL,      -- _passwortHash
    [PasswortSalt] nvarchar(255) NULL,      -- _passwortSalt
    CONSTRAINT [pk_DbLogin] PRIMARY KEY ([Id])
)

go

-- eaccom.db.DbNewsFeedItem
CREATE TABLE [DbNewsFeedItem] (
    [CreateDate] datetime NOT NULL,         -- _createDate
    [FromDbUserId] int NOT NULL,            -- _dbUser
    [Id] int NOT NULL,                      -- _id
    [txt] nvarchar(255) NULL,               -- _text
    CONSTRAINT [pk_DbNewsFeedItem] PRIMARY KEY ([Id])
)

go

-- eaccom.db.DbRoles
CREATE TABLE [DbRoles] (
    [Id] int NOT NULL,                      -- _id
    [RoleName] nvarchar(255) NULL,          -- _roleName
    CONSTRAINT [pk_DbRoles] PRIMARY KEY ([Id])
)

go

-- System.Collections.Generic.IList`1 eaccom.db.DbRoles._dbUsers
CREATE TABLE [DbRoles_DbUser] (
    [Id] int NOT NULL,
    [Id2] int NOT NULL,
    CONSTRAINT [pk_DbRoles_DbUser] PRIMARY KEY ([Id], [Id2])
)

go

-- eaccom.db.DbTag
CREATE TABLE [DbTag] (
    [Id] int NOT NULL,                      -- _id
    CONSTRAINT [pk_DbTag] PRIMARY KEY ([Id])
)

go

-- System.Collections.Generic.IList`1 eaccom.db.DbTag._dbNewsFeedItems
CREATE TABLE [DbTag_DbNewsFeedItem] (
    [Id] int NOT NULL,
    [Id2] int NOT NULL,
    CONSTRAINT [pk_DbTag_DbNewsFeedItem] PRIMARY KEY ([Id], [Id2])
)

go

-- eaccom.db.DbUser
CREATE TABLE [DbUser] (
    [Abbreviation] nvarchar(255) NULL,      -- _abbreviation
    [DbLoginId] int NOT NULL,               -- _dbLoginId
    [Firstname] nvarchar(255) NULL,         -- _firstname
    [Id] int NOT NULL,                      -- _id
    [Lastname] nvarchar(255) NULL,          -- _lastname
    CONSTRAINT [pk_DbUser] PRIMARY KEY ([Id])
)

go

ALTER TABLE [DbNewsFeedItem] ADD CONSTRAINT [ref_DbNewsFeedItem_DbUser] FOREIGN KEY ([FromDbUserId]) REFERENCES [DbUser]([Id])

go

ALTER TABLE [DbRoles_DbUser] ADD CONSTRAINT [ref_DbRoles_DbUser_DbRoles] FOREIGN KEY ([Id]) REFERENCES [DbRoles]([Id])

go

ALTER TABLE [DbRoles_DbUser] ADD CONSTRAINT [ref_DbRoles_DbUser_DbUser] FOREIGN KEY ([Id2]) REFERENCES [DbUser]([Id])

go

ALTER TABLE [DbTag_DbNewsFeedItem] ADD CONSTRAINT [ref_DbTag_DbNewsFeedItem_DbTag] FOREIGN KEY ([Id]) REFERENCES [DbTag]([Id])

go

ALTER TABLE [DbTag_DbNewsFeedItem] ADD CONSTRAINT [ref_DbTg_DbNwsFdItm_DbNwsFdItm] FOREIGN KEY ([Id2]) REFERENCES [DbNewsFeedItem]([Id])

go

ALTER TABLE [DbUser] ADD CONSTRAINT [ref_DbUser_DbLogin] FOREIGN KEY ([Id]) REFERENCES [DbLogin]([Id])

go

-- Index 'idx_DbNwsFeedItem_FromDbUserId' was not detected in the database. It will be created
CREATE INDEX [idx_DbNwsFeedItem_FromDbUserId] ON [DbNewsFeedItem]([FromDbUserId])

go

-- Index 'idx_DbRoles_DbUser_Id2' was not detected in the database. It will be created
CREATE INDEX [idx_DbRoles_DbUser_Id2] ON [DbRoles_DbUser]([Id2])

go

-- Index 'idx_DbTag_DbNewsFeedItem_Id2' was not detected in the database. It will be created
CREATE INDEX [idx_DbTag_DbNewsFeedItem_Id2] ON [DbTag_DbNewsFeedItem]([Id2])

go

the CreateUpdateDDLScript sql is:

-- eaccom.db.DbLogin
CREATE TABLE [DbLogin] (
    [CreateDate] datetime NOT NULL,         -- _createDate
    [Id] int NOT NULL,                      -- _id
    [IsActive] tinyint NOT NULL,            -- _isActive
    [LastAccess] datetime NOT NULL,         -- _lastAccess
    [Login] nvarchar(255) NULL,             -- _login
    [PasswortHash] nvarchar(255) NULL,      -- _passwortHash
    [PasswortSalt] nvarchar(255) NULL,      -- _passwortSalt
    CONSTRAINT [pk_DbLogin] PRIMARY KEY ([Id])
)

go

-- eaccom.db.DbNewsFeedItem
CREATE TABLE [DbNewsFeedItem] (
    [CreateDate] datetime NOT NULL,         -- _createDate
    [FromDbUserId] int NOT NULL,            -- _dbUser
    [Id] int NOT NULL,                      -- _id
    [txt] nvarchar(255) NULL,               -- _text
    CONSTRAINT [pk_DbNewsFeedItem] PRIMARY KEY ([Id])
)

go

-- eaccom.db.DbRoles
CREATE TABLE [DbRoles] (
    [Id] int NOT NULL,                      -- _id
    [RoleName] nvarchar(255) NULL,          -- _roleName
    CONSTRAINT [pk_DbRoles] PRIMARY KEY ([Id])
)

go

-- System.Collections.Generic.IList`1[[eaccom.db.DbUser, eaccom.db, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]] eaccom.db.DbRoles._dbUsers
CREATE TABLE [DbRoles_DbUser] (
    [Id] int NOT NULL,
    [Id2] int NOT NULL,
    CONSTRAINT [pk_DbRoles_DbUser] PRIMARY KEY ([Id], [Id2])
)

go

-- eaccom.db.DbTag
CREATE TABLE [DbTag] (
    [Id] int NOT NULL,                      -- _id
    CONSTRAINT [pk_DbTag] PRIMARY KEY ([Id])
)

go

-- System.Collections.Generic.IList`1[[eaccom.db.DbNewsFeedItem, eaccom.db, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]] eaccom.db.DbTag._dbNewsFeedItems
CREATE TABLE [DbTag_DbNewsFeedItem] (
    [Id] int NOT NULL,
    [Id2] int NOT NULL,
    CONSTRAINT [pk_DbTag_DbNewsFeedItem] PRIMARY KEY ([Id], [Id2])
)

go

-- eaccom.db.DbUser
CREATE TABLE [DbUser] (
    [Abbreviation] nvarchar(255) NULL,      -- _abbreviation
    [DbLoginId] int NOT NULL,               -- _dbLoginId
    [Firstname] nvarchar(255) NULL,         -- _firstname
    [Id] int NOT NULL,                      -- _id
    [Lastname] nvarchar(255) NULL,          -- _lastname
    CONSTRAINT [pk_DbUser] PRIMARY KEY ([Id])
)

go

ALTER TABLE [DbRoles_DbUser] ADD CONSTRAINT [ref_DbRoles_DbUser_DbRoles] FOREIGN KEY ([Id]) REFERENCES [DbRoles]([Id])

go

ALTER TABLE [DbRoles_DbUser] ADD CONSTRAINT [ref_DbRoles_DbUser_DbUser] FOREIGN KEY ([Id2]) REFERENCES [DbUser]([Id])

go

ALTER TABLE [DbTag_DbNewsFeedItem] ADD CONSTRAINT [ref_DbTag_DbNewsFeedItem_DbTag] FOREIGN KEY ([Id]) REFERENCES [DbTag]([Id])

go

ALTER TABLE [DbTag_DbNewsFeedItem] ADD CONSTRAINT [ref_DbTg_DbNwsFdItm_DbNwsFdItm] FOREIGN KEY ([Id2]) REFERENCES [DbNewsFeedItem]([Id])

go

-- Index 'idx_DbNwsFeedItem_FromDbUserId' was not detected in the database. It will be created
CREATE INDEX [idx_DbNwsFeedItem_FromDbUserId] ON [DbNewsFeedItem]([FromDbUserId])

go

-- Index 'idx_DbRoles_DbUser_Id2' was not detected in the database. It will be created
CREATE INDEX [idx_DbRoles_DbUser_Id2] ON [DbRoles_DbUser]([Id2])

go

-- Index 'idx_DbTag_DbNewsFeedItem_Id2' was not detected in the database. It will be created
CREATE INDEX [idx_DbTag_DbNewsFeedItem_Id2] ON [DbTag_DbNewsFeedItem]([Id2])

go

thank for any help,

Janko

[Janko]

i found the solution:

metadata.DefaultMapping.NullForeignKey = true; 

was missing.

public static void UpdateOrCreateDb()
       {
           using (eaccomDb dbContext = new eaccomDb())
           {
               MetadataContainer metadata = dbContext.Metadata;

               /* ---> this was missing */
               metadata.DefaultMapping.NullForeignKey = true;

               foreach (MetaPersistentType type in metadata.PersistentTypes)
               {
                   // This will create all entity tables in model, but not tables for joins
                   type.ShouldUpdateSchema = true;

                   // Now handle table joins for associations
                   foreach (MetaMember member in type.Members)
                   {
                       MetaNavigationMember navMember = member as MetaNavigationMember;
                       if (navMember != null)
                       {
                           MetaJoinTableAssociation joinAssociation = navMember.Association as MetaJoinTableAssociation;
                           if (joinAssociation != null)
                           {
                               joinAssociation.ShouldUpdateSchema = true; // Only do if there is a join association
                           }
                       }
                   }
               }

               BackendConfiguration config = eaccomDb.GetBackendConfiguration();

               using (OpenAccessContext update_context = new OpenAccessContext("DbConnection", config, metadata))
               {
                   Telerik.OpenAccess.ISchemaHandler schemaHandler = update_context.GetSchemaHandler();
                   string script = null;
                   if (schemaHandler.DatabaseExists())
                   {
                       script = schemaHandler.CreateUpdateDDLScript(null);
                   }
                   else
                   {
                       schemaHandler.CreateDatabase();
                       script = schemaHandler.CreateDDLScript();
                   }
                   if (!string.IsNullOrEmpty(script))
                   {
                       schemaHandler.ExecuteDDLScript(script);
                   }
               }
           }
       }

[Zsolt]

Thank you so much! I have been looking for this solution for months.