sql-serverdatabaset-sql.net-corefluent-migrator

Convert SQL expression to create index using Fluentmigrator


I have an application in .Net Core 3.1 using a SQL Server DB, where I have this SQL expression that I want to "convert" to a fluent migrator expression, but I'm not sure about how to do it.

CREATE NONCLUSTERED INDEX [nci_wi_XXXXXX] ON [dbo].[MyTable]
([MyColumn1], [MyColumn2]) INCLUDE ([MyColumn3], [MyColumn4], [MyColumn5],
[MyColumn6], [MyColumn7], [MyColumn8], [MyColumn9]) WITH (ONLINE = ON)

I want to have that in a expression similar to this one:

Create.Index("nci_wi_XXXXXX")
   .OnTable("MyTable")
   .OnColumn("MyColumn1").Unique()
   .OnColumn("MyColumn2").Unique()
   .WithOptions().NonClustered()......

But I'm not sure about how to add "included columns" when creating and Index. How can I do that?

Thanks in advance.


Solution

  • Sounds like this PR have already included this feature: https://github.com/fluentmigrator/fluentmigrator/pull/409

    You can do it as following:

     Create.Index("nci_wi_XXXXXX")
                 .OnTable(nameof(MyTable))
                 .OnColumn(nameof(MyTable.MyColumn1)).Ascending()
                 .OnColumn(nameof(MyTable.MyColumn2)).Ascending()
                 .WithOptions()
                 .Include(nameof(MyTable.MyColumn3))
                 .Include(nameof(MyTable.MyColumn4))
                 .Include(nameof(MyTable.MyColumn5))
                 .Include(nameof(MyTable.MyColumn6))
                 .Include(nameof(MyTable.MyColumn7))
                 .Include(nameof(MyTable.MyColumn8))
                 .Include(nameof(MyTable.MyColumn9))
                 .Online()
                 .NonClustered();