Files and File Groups in Entity Framework 6

Question

I am trying to split off a read-only, heavily used auditing table from the rest of the database. Putting it in a filegroup and seperate file seems the best solution.

However I cannot figure out how to set this up in entity framework, do I need to manually drop and create the table and target the constraints to the filegroup?

Currently I am using migrations to create the database and tables:

CreateTable(
    "dbo.audi_auditing",
    c => new
        {
            audi_id = c.Int(nullable: false, identity: true),
            audi_id_first = c.String(maxLength: 20),
            audi_id_second = c.String(maxLength: 20),
            audi_data = c.String(storeType: "xml"),
            tent_id = c.Int(),
            audy_id = c.Int(nullable: false),
            audi_created = c.DateTime(nullable: false, precision: 0, storeType: "datetime2"),
            audi_created_by = c.String(nullable: false, maxLength: 50),
        })
    .PrimaryKey(t => t.audi_id)
    .ForeignKey("dbo.tabe_table_entity", t => t.tent_id)
    .ForeignKey("dbo.audy_audit_type", t => t.audy_id, cascadeDelete: true)
    .Index(t => t.audi_id_first)
    .Index(t => t.audi_id_second)
    .Index(t => t.tent_id)
    .Index(t => t.audy_id)
    .Index(t => t.audi_created)
    .Index(t => t.audi_created_by);

Related: How do i move a table to a particular FileGroup in SQL Server 2008

Answer 1

If anyone is interested, I ended up doing this in a migration:

Sql(@"
    IF NOT EXISTS (SELECT * FROM sys.filegroups where name = 'AUDIT') BEGIN
        ALTER DATABASE CURRENT
            ADD FILEGROUP [AUDIT]
    END
", true);

Sql(@"
    IF EXISTS (SELECT * FROM sys.filegroups where name = 'AUDIT') AND NOT EXISTS (SELECT * FROM sys.master_files where name = DB_NAME() + '_audit') BEGIN
        DECLARE @DatabasePath nvarchar(max)
        DECLARE @SQL nvarchar(max)

        SELECT TOP 1 @DatabasePath = physical_name 
        FROM sys.master_files 
        WHERE database_id = DB_ID() AND file_id = 1 AND type_desc = N'ROWS'

        SET @SQL = N'ALTER DATABASE CURRENT
                        ADD FILE (
                            NAME = [' + DB_NAME() + '_audit],
                            FILENAME = N''' + REPLACE(@DatabasePath, N'.mdf', N'_audit.ndf') + ''',
                            SIZE = 10MB,
                            MAXSIZE = UNLIMITED,
                            FILEGROWTH = 10MB
                            )
                        TO FILEGROUP [AUDIT]'
        EXECUTE sp_executesql @SQL
    END
", true);
Sql(@"
    IF EXISTS (SELECT * FROM sys.filegroups where name = 'AUDIT') AND EXISTS (SELECT * FROM sys.master_files where name = DB_NAME() + '_audit') BEGIN
        CREATE UNIQUE CLUSTERED INDEX [PK_dbo.audi_auditing]
            ON [dbo].[audi_auditing](audi_id)
            WITH (DROP_EXISTING = ON) ON [AUDIT]
    END
", true);