Unique Value that Allows multiple NULL ( ASP.NET CORE 2.2 )

marcasmar94

I have a column that is unique, but it should also allow Multiple unique values.

In EF Core that is achievable by doing the following:

builder.Entity<Customer>().HasIndex(e => e.InstagramID).IsUnique();

This will create a filtered index that allows multiple null values on a unique column

I have tested manually in the database it self and it works as it should be.

But when trying to add this using Datatables editor i get the following:

Cannot insert duplicate key row in object 'dbo.Customer' with unique index 'IX_Customer_FacebookID'. The duplicate key value is (). The statement has been terminated.

Any workaround ?

Thank you

allan

Multiple unique values

I'm afraid I don't really understand that. Could you show me some example data that would match that?

Thanks,
Allan

marcasmar94

Lets say you have a website and users login into your website.

Now these users, they may have Instagram profiles (InstagramID) as well that you want to save.

Some users do not have an Instagram profile. But if a user has an Instagram profile, his Instagram ID is UNIQUE.

So that translates into the InstagramID field in the database to be UNIQUE But it also supports having NULL value(s).

Multiple users may not have an InstagramID so that means multiple NULL values should be allowed on the UNIQUE field InstagramID.

In EF Core

builder.Entity<Customer>().HasIndex(e => e.InstagramID).IsUnique();

The above line satisfies the condition of having a UNIQUE field that supports MULTIPLE NULL values.

But when using the Editor i got the error:

Cannot insert duplicate key row in object 'dbo.Customer' with unique index 'IX_Customer_FacebookID'. The duplicate key value is (). The statement has been terminated.

allan

I'm with you now - multiple nulls! This appears to have been a design decision in SQLServer (the error is coming from SQLServer rather than Editor). This post on SO discusses how you can handle it with a constraint that uses a WHERE condition.

Allan