Declare a clustered key on a many-to-many relationship table in CodeFluent Entities with SQL Server producer

Question

I have the same issue as this question (exactly the same need): How can I declare a Clustered key on a many-to-many relationship table in CodeFluent Entities with Sql Server Producer

I tried to use the aspect but I am not sure how I should set up the configuration.

Should I add cfps:hint="CLUSTERED" for both of my tables ? I tried but I still get the same error

SQL80001: Incorrect syntax near 'CLUSTERED'.

Or maybe my aspect is not correctly set up. (It's the first time I use an aspect).

Maybe a good things to include in the product itself.

=> Update => I still have the same issue. Key is not clustered on the relation table.. It's not exactly the same kind of relation.

Here is my model (extract) :

<cf:entity name="Hotel" namespace="Marmara">
  <cf:property name="ProductCode" cfps:hint="CLUSTERED" key="true" typeName="string" />
  <cf:property name="Hotels" typeName="Marmara.BookingCollection" />
  <cf:property name="Name" />
  <cf:property name="Rating" typeName="decimal" />
</cf:entity>

<cf:entity name="Fly" namespace="Marmara">
  <cf:property name="TakeOffDate" cfps:hint="CLUSTERED" key="true" typeName="date" />
  <cf:property name="Bookings" typeName="Marmara.BookingCollection" />
</cf:entity>

<cf:entity name="Booking" setType="List" trackingModes="None" namespace="Marmara">
  <cf:property name="Hotel" key="true" typeName="Marmara.Hotel" />
  <cf:property name="Fly" key="true" typeName="Marmara.Fly" />
  <cf:property name="Price" typeName="int" />
  <cf:property name="AvailableSeat" typeName="int" />
  <cf:property name="RunTimeDate" key="true" typeName="Marmara.RunTimeDate" />
</cf:entity>

<cf:entity name="RunTimeDate" namespace="Marmara">
  <cf:property name="Date" cfps:hint="CLUSTERED" key="true" typeName="datetime" />
  <cf:property name="RunTimeDates" typeName="Marmara.BookingCollection" />
</cf:entity>

Here is the set up of my aspect :

<cf:pattern path="SampleAspect.cfp" step="start" name="Sample Aspect" />

Answer 1

Here's a sample model:

<cf:pattern path="ClusteredIndexAspect.xml" step="Start" name="ClusteredIndex" />

<cf:entity name="Student">
  <cf:property name="Id" cfps:hint="CLUSTERED" key="true" />
  <cf:property name="Name" />
  <cf:property name="Courses" typeName="{0}.CourseCollection" relationPropertyName="Students" />
</cf:entity>

<cf:entity name="Course">
  <cf:property name="Id" cfps:hint="CLUSTERED" key="true" />
  <cf:property name="Name" />
  <cf:property name="Students" typeName="{0}.StudentCollection" relationPropertyName="Courses" />
</cf:entity>

The ClusteredIndexAspect.xml file (I just add the cf:pattern tag):

<cf:project xmlns:cf="http://www.softfluent.com/codefluent/2005/1" defaultNamespace="ClusteredHint">
    <cf:pattern name="ClusteredIndex" namespaceUri="http://www.softfluent.com/samples/clustered-index" preferredPrefix="_ci" step="Start">
        <cf:message class="_doc">
            Clustered Index Aspect
        </cf:message>
    </cf:pattern>

    <!-- 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)
        {
            if(project == null)
              throw new ArgumentNullException("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 == null || table.RelationRelatedKeyColumns == null || 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;
                }

                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>

The generated tables have clustered index:

CREATE TABLE [dbo].[Course] (
    [Course_Id] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_Cou_Cou_Cou] PRIMARY KEY CLUSTERED (...)
)
CREATE TABLE [dbo].[Student] (
    [Student_Id] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_Stu_Stu_Stu] PRIMARY KEY CLUSTERED (...) 
)
CREATE TABLE [dbo].[Course_Students_Student_Courses] (
 [Course_Id] [uniqueidentifier] NOT NULL,
 [Student_Id] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Cor_Cou_Stu_Cor] PRIMARY KEY clustered(...)
)

EDIT: To add a clustered index to the booking entity, you have to set cfps:clustered="true"

<cf:entity name="Booking" setType="List" trackingModes="None" namespace="Marmara">
  <cf:property name="Hotel" cfps:clustered="true" key="true" typeName="Marmara.Hotel" />
  <cf:property name="Fly" cfps:clustered="true" key="true" typeName="Marmara.Fly" />
  <cf:property name="Price" typeName="int" />
  <cf:property name="AvailableSeat" typeName="int" />
  <cf:property name="RunTimeDate" cfps:clustered="true" key="true" typeName="Marmara.RunTimeDate" />
</cf:entity>

This will generate the following SQL statement:

CREATE CLUSTERED INDEX [CL_dbo_Booking] ON [dbo].[Booking] ( [Booking_Hotel_ProductCode], [Booking_Fly_TakeOffDate], [Booking_RunTimeDate_Date]);