EF 6.1独特可疑指数 [英] EF 6.1 Unique Nullable Index

问题描述

在EF 6.1中，使用Code First，您可以使用实体中的属性创建索引，或者使用流畅的API：

In EF 6.1 using Code First you can create Indexes using Attributes in your Entities or using the fluent API along the lines of:

 Property(x => x.PropertyName)
                .IsOptional()
                .HasMaxLength(450)
                .HasColumnAnnotation("Index",
                    new IndexAnnotation(new IndexAttribute("IX_IndexName") {IsUnique = true,  }));

有没有办法说支架 WHERE PropertyName IS NOT NULL 以与SQL Server本身相同的方式（请参阅： https://stackoverflow.com/a/767702/52026 ）？

Is there any way to say scaffold WHERE PropertyName IS NOT NULL in the same way you would in SQL Server natively (see: https://stackoverflow.com/a/767702/52026)?

推荐答案

我没有找到一种方法来告诉EF使用这个where子句，但这里是一些解决方法。检查是否符合您的情况。

I didn't find a way to tell EF to use this where clause but here is some workaround. Check if it fit in your case.

1. 安装实体框架，在App.config等中定义您的DbContext，实体，conn字符串。 li>
   
2. 启用迁移 - 在包管理器控制台-EnableMigration中运行


3. 创建DbMigration - 在包管理器控制台添加迁移迁移名称中运行


4. 在ovverided 中创建的DbMigration类中，方法运行您的sql来创建唯一的可空索引。

1. Install Entity Framework, Define your DbContext, entities, conn string in app.config etc.
2. Enable Migration - run in Package Manager Console '-EnableMigration'
3. Create DbMigration - run in Package Manager Console 'Add-Migration MigrationName'
4. In the created DbMigration class in ovverided Up method run your sql for creating of unique nullable index.

代码：

// Add unique nullable index 
string indexName = "IX_UQ_UniqueColumn";
string tableName = "dbo.ExampleClasses";
string columnName = "UniqueColumn";

Sql(string.Format(@"
    CREATE UNIQUE NONCLUSTERED INDEX {0}
    ON {1}({2}) 
    WHERE {2} IS NOT NULL;",
    indexName, tableName, columnName));

注意：不要忘记创建降级。 Ovveride Down 方法，并使用 DropIndex 方法：

Note: don't forget to create a downgrade, too. Ovveride Down method and use DropIndex method inside:

DropIndex(tableName, indexName);

如果您的数据库中已有数据可能与唯一的数据冲突，您可能还需要一些其他代码索引约束。

Also you may need some additional code if there is already data in your database which can conflict with the unique index constraint.

注意：在这里可以使用CreateIndex方法，但是我无法使用它创建正确的索引。 EF只是忽略我的匿名参数，或者我写错了。你可以自己尝试一下，并在这里写下你的结果。语法如下：

NOTE: Here you can use the CreateIndex method but I couldn't manage to create the correct index with it. EF just ignore my anonymousArguments or I write them wrong. You can try it yourself and write here with your result. The syntax is as follow:

CreateIndex(
    table: "dbo.ExampleClasses",
    columns: new string[] { "UniqueColumn" },
    unique: true,
    name: "IX_UniqueColumn",
    clustered: false,
    anonymousArguments: new
    {
        Include = new string[] { "UniqueColumn" },
        Where = "UniqueColumn IS NOT NULL"
    });

5尝试为唯一列添加带有空值的两个etries和其他相等的值。

5 Try to add two etries with null values for the unique column and other equal values.

这是我的演示代码 - Pastebin

Here is my demo code - Pastebin

这篇关于EF 6.1独特可疑指数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！