如何使用 Fluent API 通过 ASC/DESC 排序在多列上添加索引? [英] How to add an index on multiple columns with ASC/DESC sort using the Fluent API?
问题描述
我有一个使用 Entity Framework 6 - Code First 方法的 MVC ASP.NET 应用程序.
I have a MVC ASP.NET application using Entity Framework 6 - Code First approach.
使用 Fluent API，我如何使用 ASC/DESC 排序在多列上添加索引，每列都不同?
Using the Fluent API, how can I add an index on multiple columns with ASC/DESC sort that is different for each column ?
我见过很多使用多列但无法设置索引中列的排序顺序的示例.
I've seen many examples using multiple columns but no way to set the sort order of the columns in the index.
Table
-----
Id
Type
DateFor
DateCreated
Value
我想要以下列为索引:Type(ASC)、DateFor(Desc)、DateCreated(Desc).
I want an index on the following columns: Type(ASC), DateFor(Desc), DateCreated(Desc).
推荐答案
简答: Entity Framework 6 不允许具有不同排序的多个索引.
Short answer: Entity Framework 6 does not allow multiple indexes with different sorts.
长答案:可能无法直接做到这一点，但可以通过一些调整来实现.经过大量阅读，我发现创建一个继承 IndexAnnotation 并添加 SortOrder 属性的新类确实很复杂.
Long answer: It may not be possible to do it directly but it can be achieved with some tweaking. After a lot of reading, I found that it would be really complicated to create a new class that would inherit IndexAnnotation and add a SortOrder property.
我发现实现这一点的最简单方法是查看我可以调整哪些现有属性以实现多索引排序.使用 Name 属性可以做到这一点，因为它是一个字符串.可以直接在名称中添加排序索引，稍后生成SQL代码时截取.
The easiest way I found to achieve this was to see what existing property I could tweak to achieve the multiple index sort. Using the Name property could do it as it's a string. You can add the sort index directly in the name and intercept it later when generating the SQL code.
假设我需要像这样索引属性:
So let's assume I need to index the properties like this:
- 类型 (ASC)
- DateFor (Desc)
- 创建日期(Desc)
然后我将命名我的索引，后跟分隔符 (:) 和排序顺序.它看起来像这样:
I would then name my index followed by a separator (:) and the sort orders. It would look like this:
var indexName = "IX_Table:ASC,DESC,DESC";
具有多个字段的索引如下所示:
The index with multiple fields would look like this:
this.Property(t => t.Type)
.HasColumnAnnotation(
IndexAnnotation.AnnotationName,
new IndexAnnotation(new[]
{
new IndexAttribute(indexName) { Order = 1 }
}
)
);
this.Property(t => t.DateFor)
.HasColumnAnnotation(
IndexAnnotation.AnnotationName,
new IndexAnnotation(new[]
{
new IndexAttribute(indexName) { Order = 2 }
}
)
);
this.Property(t => t.DateCreated)
.HasColumnAnnotation(
IndexAnnotation.AnnotationName,
new IndexAnnotation(new[]
{
new IndexAttribute(indexName) { Order = 3 }
}
)
);
我们现在必须创建一个自定义 SQL 生成类，以便生成正确的 SQL 代码来解析我们的调整"索引名称:
We must now create a custom SQL generate class in order to generate the right SQL code to parse our "tweaked" index name:
public class CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
protected override void Generate(CreateIndexOperation createIndexOperation)
{
using (var writer = Writer())
{
writer.Write("CREATE ");
if (createIndexOperation.IsUnique)
{
writer.Write("UNIQUE ");
}
if (createIndexOperation.IsClustered)
{
writer.Write("CLUSTERED ");
}
else
{
writer.Write("NONCLUSTERED ");
}
string name = createIndexOperation.Name;
string[] sorts = {};
if (createIndexOperation.Name.Contains(":"))
{
var parts = createIndexOperation.Name.Split(':');
if (parts.Length >= 1)
{
name = parts[0];
}
if (parts.Length >= 2)
{
sorts = parts[1].Split(',');
}
}
writer.Write("INDEX ");
writer.Write(Quote(name));
writer.Write(" ON ");
writer.Write(Name(createIndexOperation.Table));
writer.Write("(");
// Add the columns to the index with their respective sort order
string fields = "";
if (sorts.Length == 0 || sorts.Length == createIndexOperation.Columns.Count)
{
for (int i=0 ; i<createIndexOperation.Columns.Count ; i++)
{
string sort = "ASC";
if (sorts.Length == 0)
{
// Do nothing
}
else if (sorts[i] != "ASC" && sorts[i] != "DESC")
{
throw new Exception(string.Format("Expected sort for {0} is 'ASC' or 'DESC. Received: {1}", name, sorts[i]));
}
else
{
sort = sorts[i];
}
fields = fields + Quote(createIndexOperation.Columns[i]) + " " + sort + ",";
}
fields = fields.Substring(0, fields.Length - 1);
}
else
{
throw new Exception(string.Format("The sort (ASC/DEC) count is not equal to the number of fields in your Index ({0}).", name));
}
writer.Write(fields);
writer.Write(")");
Statement(writer);
}
}
}
最后，您需要通过编辑 Configuration.cs 文件告诉 Entity Framework 使用新的代码生成方法而不是默认方法:
Finally, you need to tell Entity Framework to use your new code generated method instead of the default one by editing your Configuration.cs file:
internal sealed class MyConfiguration : DbMigrationsConfiguration<MyContext>
{
/// <summary>
/// Constructor
/// </summary>
public MyConfiguration()
{
// Other stuff here...
// Index/Unique custom generation (Ascending and Descending)
SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationSqlGenerator());
}
}
就是这样.它可能不是最干净的解决方案，但如果您动态生成实体(就像我一样)，您将节省大量时间并避免忘记运行原始 SQL.
That's it. It may not be the cleanest solution but if you generate your entities on the fly (as I do), you will save a lot of time and avoid forgetting to run your raw SQL.
非常感谢 Rowan Miller 以及他博客上的所有文章.此答案的灵感来自:自定义 Code First 迁移提供程序.
A big thank you to Rowan Miller and all the articles on his blog. This answer was inspired by: Customizing Code First Migrations Provider.
这篇关于如何使用 Fluent API 通过 ASC/DESC 排序在多列上添加索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
