Efcore: Where property on IndexAttribute for Unique constraints

[rockstardev]

Say that I have Attribute like this on my User object/table:

[Index("UNIQ_Nick", IsUnique = true)]
public string Nick { get; set; }

If I don't make Nick required (say I just want emails to register user) constraint will prevent future registrations because in SQL Server IsUnique constraint prevents rows with multiple

This is well described on:
http://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls
http://stackoverflow.com/questions/24361518/ef-6-1-unique-nullable-index

Do you have any plans to support something like:
[Index("UNIQ_Nick", IsUnique = true, Where="Nick IS NOT NULL")]

??

Would you accept pull request if I implemented this?

[rowanmiller]

Hey,

This isn't something we'd want to add to the attribute since it is very specific to relational databases. The best place to do this would be to edit the migration once it is scaffolded. We would consider a pull request to add support for this in the migrations index API.

~Rowan

[weitzhandler]

@rowanmiller I can't seem to find the IndexAttribute attribute at all.
https://github.com/aspnet/EntityFramework/issues/2675

[rowanmiller]

@weitzhandler IndexAttrbiute was never part of DataAnnotations, it was something we had defined in our EF assembly. We may or may not have it in EF7 (there are some issues with the design we had in EF6 so we don't want to carry it over as-is). For the moment, you need to use the Fluent API to add indexes.

[AndriySvyryd]

@weitzhandler The Index method is toplevel on the EntityTypeBuilder: modelBuilder.Entity<MyEntity>().Index(e => e.prop).Unique()

[weitzhandler]

@rowanmiller

I still miss the IndexAttribute anyway. I prefer attributes way over fluent. Especially when the model starts to become complex and the OnModelCreating becomes messy.
BTW, there should be a way to implement an interface in any entity that provides an OnModelCreating called from the context's one, this way, the creation of the entity-specific attributes is kept within the entities.

I understand about the IndexAttribute which is pretty technology-centric, but again, an attribute is just a useless thing on any DTO, so there is no real issue with annotating properties with technology specific attributes, given these attributes are part of any supported framework.

Anyway thanks for your help @AndriySvyryd, anyway I haven't managed to find Order on the fluent API, is it around there?

[weitzhandler]

How do I define the index order via fluent API?

[rowanmiller]

@weitzhandler currently not possible. Feel free to open an issue to request it :smile:

[weitzhandler]

@rowanmiller
But when creating a multi-key index, does it take care of the order automatically? By what precedence?

[rowanmiller]

@weitzhandler oh do you mean the order of the columns rather than ASC/DESC? The order of columns is the order that you specify them in the API call to configure the index.

[yukozh]

+1 for IndexAttribute

[applenele]

+1 for IndexAttribute

[weitzhandler]

Yeah. Still hoping to see it back, I think it's not SQL-specific.
Perhaps the attribute should be renamed to UniqueAttribute, which applies to ANY type of DB, even NoSql, and does resemble the concept of uniqueness rather than an Index which is tied up to specific vendor.

[yukozh]

I don't think so. We often make a property as an index like a time field, They are able to be duplicated. Although we can make index and unique in modelbuilding, To implement an IndexAttribute is good for migrate from EF6 or earlier, and makes modeling easily.

[yukozh]

Unique must be related to Index, because UniqueKey must be indexed.

[ghost]

@divega, @rowanmiller, wouldn't it make sense to keep this issue opened and add a label up for grabs. This looks pretty blunt in a wrong way to close the issue when clearly many consumers are asking for IndexAttribute feature. At least keep the discussion open to avoid duplicate issues (currently there are 5 closed issues on which people are +1'ing the same feature.. https://github.com/aspnet/EntityFramework/issues/107#issuecomment-162855173).

[rowanmiller]

@jasonwilliams200OK this issue was specifically about adding the ability to specify a 'WHERE' clause on an index via attributes. Agreed we have got a lot of feedback wanting an [Index] attribute, and we may introduce one based on that feedback. Looks like we don't have an issue tracking it yet, feel free to open one.

[ghost]

@rowanmiller, should https://github.com/aspnet/EntityFramework/issues/2675 be reopened?

[rowanmiller]

Added https://github.com/aspnet/EntityFramework/issues/4050 to the backlog

[SimonOrdo]

@AndriySvyryd

Is this still true, as of RC1?

"The Index method is toplevel on the EntityTypeBuilder:
modelBuilder.Entity().Index(e => e.prop).Unique()"

I can't find the Index method on EntityTypeBuilder. Is it an extension method in another namespace?

[rowanmiller]

HasIndex is the method you are after - http://docs.efproject.net/en/latest/modeling/relational/indexes.html.

[joshcomley]

Is there any way in EF7 to add a unique constraint via migrations?

[joshcomley]

Figured it out:

        migrationBuilder.CreateIndex(
            name: ...,
            table: ...,
            column: ...,
            unique: true);

[joshcomley]

Or even better:

        migrationBuilder.AddUniqueConstraint(
            name: ...,
            table: ...,
            column: ...);

[ccpu]

+1 for IndexAttribute

[tancevsasa]

+1 for IndexAttribute

[Int32Overflow]

+1 for IndexAttribute

[zbrad]

+1 for IndexAttribute

[JD-Robbs]

Adding my +1 for IndexAttribute.

[OronDF343]

+1 for IndexAttribute

[AndriySvyryd]

This issue is closed and will not be considered for planning purposes. Please direct all IndexAttribute feedback to https://github.com/aspnet/EntityFrameworkCore/issues/4050

[davidrevoledo]

+1 for IndexAttribute

[kanadaj]

This isn't something we'd want to add to the attribute since it is very specific to relational databases.

Given that there _is_ a shared package just for relational databases, _that_ seems like a very weak excuse not to do it.

[tidusjar]

Is there an update for this?

[ajcvickers]

@tidusjar This issue is closed in favor of #4050, which is on the backlog.