1
Vote

Designer: DDL Generation Template for SQL CE should seperate each statemenet with GO

description

Currently this is not the case for all statements, and it would be consistent and convenient if this was the case.

comments

moozzyk wrote Jan 28 at 2:32 AM

Please provide more details on benefits of having this?
DDL is created by a T4 template that can be modified or you can provide your own T4 template (can be created based on the one that is shipped with VS) to generate DDL the way you want it.
The template I am talking about is located here:
c:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\SSDLToSQL10.tt

ErikEJ wrote Jan 28 at 7:04 AM

The benefit would be that the script is consistent and reliable. It is only foreign key constraints that lack the GO, all other statements have it. I will blog about the required change.
<#
    foreach (AssociationSet associationSet in Store.GetAllAssociationSets())
    {
        ReferentialConstraint constraint = associationSet.ElementType.ReferentialConstraints.Single();
        AssociationSetEnd dependentSetEnd = associationSet.AssociationSetEnds.Where(ase => ase.CorrespondingAssociationEndMember == constraint.ToRole).Single();
        AssociationSetEnd principalSetEnd = associationSet.AssociationSetEnds.Where(ase => ase.CorrespondingAssociationEndMember == constraint.FromRole).Single();
        string schemaName = Id(dependentSetEnd.EntitySet.GetSchemaName());
        string dependentTableName = Id(dependentSetEnd.EntitySet.GetTableName());
        string principalTableName = Id(principalSetEnd.EntitySet.GetTableName());
#>
-- Creating foreign key on <#=WriteColumns(constraint.ToProperties, ',')#> in table '<#=dependentTableName#>'
ALTER TABLE <#if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=dependentTableName#>]
ADD CONSTRAINT [<#=WriteFKConstraintName(constraint)#>]
    FOREIGN KEY (<#=WriteColumns(constraint.ToProperties, ',')#>)
    REFERENCES <# if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=principalTableName#>]
        (<#=WriteColumns(constraint.FromProperties, ',')#>)
    ON DELETE <#=GetDeleteAction(constraint)#> ON UPDATE NO ACTION;
GO
<#      
        // if the foreign keys are part of the primary key on the dependent end, then we should not add a constraint.
        if (!dependentSetEnd.EntitySet.ElementType.GetKeyProperties().Take(constraint.ToProperties.Count()).OrderBy(r => r.Name).SequenceEqual(constraint.ToProperties.OrderBy(r => r.Name)))
        {
#>

-- Creating non-clustered index for FOREIGN KEY '<#=WriteFKConstraintName(constraint)#>'
CREATE INDEX [IX_<#=WriteFKConstraintName(constraint)#>]
ON <#if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=dependentTableName#>]
    (<#=WriteColumns(constraint.ToProperties, ',')#>);
GO
<#      
        }
#>

<#
    }
#>
-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------

RoMiller wrote Jan 31 at 10:30 PM

EF Team Triage: We are only taking minimal changes to the designer in EF6 because we are still converting the code base to open source. We'll consider this issue for the next release.

ErikEJ wrote Feb 1 at 8:02 AM

Thanks for the update, I will just blog about my proposed update and make it available for download

moozzyk wrote Feb 1 at 4:18 PM

@Erikj: Reply we are working on making the designer open source. Once it is published you will be welcome to contribute your change back to the product.

ErikEJ wrote Feb 1 at 4:55 PM

Thanks, I will look forward to that, in the meantime I blogged about my fix here: http://erikej.blogspot.dk/2013/02/fixing-entity-framework-designer.html