Multiple indexes possible using HasColumnAnnotation?

c# ef-code-first ef-fluent-api entity-framework

Question

It looks like in Entity Framework 6.1 they added the ability to create table indexes via the new HasColumnAnnotation method. I created a few helper extensions to speed up the process:

public static class MappingExtensions
{
    public static StringPropertyConfiguration HasIndex(this StringPropertyConfiguration config, bool isUnique = false)
    {
        return config.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute() { IsUnique = isUnique }));
    }
    public static StringPropertyConfiguration HasIndex(this StringPropertyConfiguration config, string name, int order = 1, bool isUnique = false)
    {
        return config.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute(name, order) { IsUnique = isUnique }));
    }
    public static PrimitivePropertyConfiguration HasIndex(this PrimitivePropertyConfiguration config, bool isUnique = false)
    {
        return config.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute() { IsUnique = isUnique }));
    }
    public static PrimitivePropertyConfiguration HasIndex(this PrimitivePropertyConfiguration config, string name, int order = 1, bool isUnique = false)
    {
        return config.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute(name, order) { IsUnique = isUnique }));
    }
}

This works fantastic...until I try to create a second index that contains a column already used in another index. Whatever I add last overwrites the original. Does anyone know if it is currently possible to add multiple indexes to the same column via the new HasColumnAnnotation available on the StringPropertyConfiguration and PrimitivePropertyConfiguration?

I can work around this like I always have by manually adding indexes in the Migration scripts, but it would be most excellent to be able to configure this in the EntityTypeConfiguration mappings so I can have it all in one spot.


After Gerts feedback, this is what I ended up doing:

public static class MappingExtensions
{
    public static StringPropertyConfiguration HasIndex(this StringPropertyConfiguration config, params IndexAttribute[] indexes)
    {
        return config.HasColumnAnnotation("Index", new IndexAnnotation(indexes));
    }

    public static PrimitivePropertyConfiguration HasIndex(this PrimitivePropertyConfiguration config, params IndexAttribute[] indexes)
    {
        return config.HasColumnAnnotation("Index", new IndexAnnotation(indexes));
    }
}

And here is the new usage:

Property(x => x.Name).IsRequired().HasMaxLength(65).HasIndex(new IndexAttribute("IX_Countries_Name") { IsUnique = true }, new IndexAttribute("IX_Countries_Published", 2))
1
17
2/21/2015 10:42:36 PM

Accepted Answer

This is because each of your extension methods assign a new annotation to a property and overwrite the previous one. Let me show that by using your methods in an example.

Say we have this (useless) class

public class Client
{
    public int ClientId { get; set; }
    public int CompanyId { get; set; }
    public int AddressId { get; set; }
}

And apply your index definitions (skipping the part modelBuilder.Entity<Client>()):

.Property(c => c.ClientId).HasIndex("ClientCompanyIndex");
.Property(c => c.CompanyId).HasIndex("ClientCompanyIndex", 2);
.Property(c => c.ClientId).HasIndex("ClientAddressIndex");
.Property(c => c.AddressId).HasIndex("ClientAddressIndex", 2);

Inlining the extension methods (thank God for Resharper) this leads to

.Property(c => c.ClientId).HasColumnAnnotation("Index",
    new IndexAnnotation(new IndexAttribute("ClientCompanyIndex", 1));
.Property(c => c.CompanyId).HasColumnAnnotation("Index",
     new IndexAnnotation(new IndexAttribute("ClientCompanyIndex", 2));
.Property(c => c.ClientId).HasColumnAnnotation("Index",
    new IndexAnnotation(new IndexAttribute("ClientAddressIndex", 1));
.Property(c => c.AddressId).HasColumnAnnotation("Index",
     new IndexAnnotation(new IndexAttribute("ClientAddressIndex", 2));

This is the same as writing

[Index("ClientCompanyIndex", Order = 1)]
public int ClientId { get; set; }

and then replacing it by

[Index("ClientAddressIndex", Order = 1)]
public int ClientId { get; set; }

To reproduce the correct annotation...

[Index("ClientAddressIndex", IsUnique = true, Order = 1)]
[Index("ClientCompanyIndex", IsUnique = true, Order = 1)]
public int ClientId { get; set; }
[Index("ClientCompanyIndex", IsUnique = true, Order = 2)]
public int CompanyId { get; set; }
[Index("ClientAddressIndex", IsUnique = true, Order = 2)]
public int AddressId { get; set; }

...the configuration of the ClientId property should look like

.Property(c => c.ClientId).HasColumnAnnotation("Index",
    new IndexAnnotation(new[]
        {
            new IndexAttribute("ClientCompanyIndex", 1),
            new IndexAttribute("ClientAddressIndex", 1)
        }));

Now suddenly creating extension methods is far less appealing. It's hardly worth the effort to create one for this combined annotation. But for single-use columns your methods are an improvement.

Of course it is clear why you're trying this. The current fluent syntax is clunky to say the least. The EF team knows this perfectly well and they're hoping for some contributor to grab this issue soon. Maybe something for you?

30
6/21/2014 10:48:04 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow