How can I declare a Clustered key on a many-to-many relationship table in CodeFluent Entities with Sql Server Producer

Question

I have a CodeFluent Entities Model such as:

<cf:project defaultNamespace="S5T" xmlns:cf="http://www.softfluent.com/codefluent/2005/1" xmlns:cfx="http://www.softfluent.com/codefluent/modeler/2008/1" xmlns:cfmy="http://www.softfluent.com/codefluent/producers.mysql/2012/1" xmlns:cfom="http://www.softfluent.com/codefluent/producers.model/2005/1" xmlns:cfasp="http://www.softfluent.com/codefluent/producers.aspnet/2011/1" xmlns:cfaz="http://www.softfluent.com/codefluent/producers.sqlazure/2011/1" xmlns:cfps="http://www.softfluent.com/codefluent/producers.sqlserver/2005/1" defaultKeyPropertyTypeName="long" maxParameterNameLength="62" defaultConcurrencyMode="None" persistencePropertyNameFormat="{1}" defaultMethodAllowDynamicSort="false" defaultProducerProductionFlags="Default, Overwrite, RemoveDates" defaultMethodDistinct="false" createDefaultMethodForms="true" createDefaultApplication="false" createDefaultHints="false" productionFlags="Default, Overwrite, RemoveDates">
  <cf:import path="Default.Surface.cfp" />
  <cf:producer name="SQL Server" typeName="CodeFluent.Producers.SqlServer.SqlServerProducer, CodeFluent.Producers.SqlServer">
    <cf:configuration produceViews="true" targetDirectory="..\Model7Bom\Persistence" connectionString="Server=MY-MACHINE\SQLEXPRESS;Database=model7;Integrated Security=true;Application Name=S5T;Password=MyPassword;User ID=MyUser" cfx:targetProject="..\Model7Bom\Model7Bom.vbproj" cfx:targetProjectLayout="Update, DontRemove" />
  </cf:producer>
  <cf:entity name="User" namespace="S5T">
    <cf:property name="Id" key="true" cfps:hint="CLUSTERED" />
    <cf:property name="Name" />
    <cf:property name="Roles" typeName="{0}.RoleCollection" relationPropertyName="Users" />
  </cf:entity>
  <cf:entity name="Role" namespace="S5T">
    <cf:property name="Id" key="true" cfps:hint="CLUSTERED" />
    <cf:property name="Name" />
    <cf:property name="Users" typeName="{0}.UserCollection" relationPropertyName="Roles" />
  </cf:entity>
</cf:project>

I could sucessfully decorate the cf:property name="Id" on both entities with cfps:hint="CLUSTERED". This got me Sql Server producer to correctly output

CONSTRAINT [PK_Use_Id_Use] PRIMARY KEY CLUSTERED
CONSTRAINT [PK_Rol_Id_Rol] PRIMARY KEY CLUSTERED

as opposed to default NONCLUSTERED.

How can I accomplish that with the THIRD TABLE generated by the model, to accomodate the many to many relationship?

By default, the table creation generated snippet is such as:

CREATE TABLE [dbo].[Role_Users_User_Roles] (
 [Id] [bigint] NOT NULL,
 [Id2] [bigint] NOT NULL,
 CONSTRAINT [PK_Roe_Id_Id2_Roe] PRIMARY KEY NONCLUSTERED
 (

  [Id],
  [Id2]
 ) ON [PRIMARY]
)

However, if I decorate both properties with cfps:hint="CLUSTERED" as in:

cf:property name="Roles" typeName="{0}.RoleCollection" relationPropertyName="Users" cfps:hint="CLUSTERED" /

cf:property name="Users" typeName="{0}.UserCollection" relationPropertyName="Roles" cfps:hint="CLUSTERED" /

I get a snippet generated with PRIMARY KEY CLUSTERED for the PK in TABLE [dbo].[Role_Users_User_Roles], BUT, in addition, I get an UNDESIRED effect of having an incorrect script generated for adding relations (generated filename ...relations_add.sql), such as:

ALTER TABLE [dbo].[Role_Users_User_Roles] WITH NOCHECK ADD CONSTRAINT [FK_Roe_Id_Id_Rol] FOREIGN KEY (
 [Id]
) REFERENCES [dbo].[Role](

 [Id]
) CLUSTERED

Along with the error from Sql Server:

Error 3 SQL80001: Incorrect syntax near 'CLUSTERED'.

And CodeFluent Producer Error:

CodeFluentRuntimeDatabaseException: CF4116: Execution of file ...path..._relations_add.sql statement at line 2

I need all three PKs CLUSTERED in the three tables generated, but not the side effect of syntax error for generating the relations.

Answer 1

This is not supported out-of-the-box. The hint declared on the a relation is rarely used, more meant as a Foreign Key hint than a column hint. There are several options you can use to do this.

The easiest is to use a post-generation .SQL script to add the clustered setting manually. This is described here: How to: Execute custom T-SQL scripts with the Microsoft SQL Server producer.

You could also use the Patch Producer to remove the CLUSTERED word from the file once it has been created : Patch Producer

Otherwise, here is another solution that involves an aspect I've written as a sample here. You can save the following piece of XML as a file, and reference it as an aspect in your model.

This aspect will add the CLUSTERED hint to primary keys of all Many To Many tables inferred from entities that have CLUSTERED keys. It will add the hint before the table scripts are created and ran, and will remove it after (so it won't end up in the relations_add script).

<cf:project xmlns:cf="http://www.softfluent.com/codefluent/2005/1">
    <!-- assembly references -->
    <?code @reference name="CodeFluent.Producers.SqlServer.dll" ?>
    <?code @reference name="CodeFluent.Runtime.Database.dll" ?>

    <!-- namespace includes -->
    <?code @namespace name="System" ?>
    <?code @namespace name="System.Collections.Generic" ?>
    <?code @namespace name="CodeFluent.Model.Code" ?>
    <?code @namespace name="CodeFluent.Model.Persistence" ?>
    <?code @namespace name="CodeFluent.Model.Code" ?>

    <!-- add global code to listen to inference steps -->
    <?code 
        Project.StepChanging += (sender1, e1) =>
        {
            if (e1.Step == ImportStep.End) // hook before production begins (end of inference pipeline)
            {
                var modifiedTables = ProjectHandler.AddClusteredHint(Project);
                // get sql server producer and hook on production events
                var sqlProducer = Project.Producers.GetProducerInstance<CodeFluent.Producers.SqlServer.SqlServerProducer>();
                sqlProducer.Production += (sender, e) =>
                {
                    // determine what SQL file has been created
                    // we want to remove hints once the table_diffs has been created, before relations_add is created
                    string script = e.GetDictionaryValue("filetype", null);
                    if (script == "TablesDiffsScript")
                    {
                        ProjectHandler.RemoveClusteredHint(modifiedTables);
                    }
                };
            }
        };
    ?>

    <!-- add member code to handle inference modification -->
    <?code @member
    public class ProjectHandler
    {
        public static IList<Table> AddClusteredHint(Project project)
        {
            var list = new List<Table>();
            foreach (var table in project.Database.Tables)
            {
                // we're only interested by tables inferred from M:M relations
                if (table.Relation == null || table.Relation.RelationType != RelationType.ManyToMany)
                    continue;

                // check this table definition is ok for us
                if (table.RelationKeyColumns.Count < 1 || table.RelationRelatedKeyColumns.Count < 1)
                    continue;

                // check clustered is declared on both sides
                string keyHint = GetSqlServerProducerHint(table.RelationKeyColumns[0].Property);
                string relatedKeyHint = GetSqlServerProducerHint(table.RelationKeyColumns[0].Property);
                if (keyHint.IndexOf("clustered", StringComparison.OrdinalIgnoreCase) < 0 ||
                    relatedKeyHint.IndexOf("clustered", StringComparison.OrdinalIgnoreCase) < 0)
                    continue;

                // force hint now, we only need to do this on one of the keys, not all
                table.PrimaryKey.Elements[0].SetAttribute("hint", CodeFluent.Producers.SqlServer.Constants.SqlServerProducerNamespaceUri, "clustered");

                // remember this table
                list.Add(table);
            }
            return list;
        }

        public static void RemoveClusteredHint(IEnumerable<Table> list)
        {
            foreach (var table in list)
            {
                table.PrimaryKey.Elements[0].RemoveAttribute("hint", CodeFluent.Producers.SqlServer.Constants.SqlServerProducerNamespaceUri);
            }
        }

        // helper method to read XML element's hint attribute in the SQL Server Producer namespace
        private static string GetSqlServerProducerHint(Node node)
        {
            if (node == null)
                return null;

            return node.GetAttributeValue<string>("hint", CodeFluent.Producers.SqlServer.Constants.SqlServerProducerNamespaceUri, null);
        }
    } 
    ?>

</cf:project>