c# - Entity Framework 6 中的文件和文件组

Question

我正试图从数据库的其余部分中分离出一个只读的、频繁使用的审计表。将它放在文件组和单独的文件中似乎是最好的解决方案。

但是我不知道如何在 Entity Framework 中设置它，我是否需要手动删除和创建表并将约束定位到文件组？

目前我正在使用迁移来创建数据库和表:

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);

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

Answer 1

如果有人感兴趣，我最终在迁移中做了这个:

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);