Efcore: Optional alternate key properties

[BradBarnich]

All of the major relational databases allow null values in unique key constraints.

As a workaround I could use unique indexes instead of alternate keys, but I would appreciate the semantics of a unique key constraint.

Something like this got me around the issue: https://github.com/BradBarnich/EntityFramework/commit/c23988e23f998c4fb3b2a449633c5b97e588366b

I can fix it up and submit a PR if you agree this should be fixed.

[rowanmiller]

Agreed this would be good. Currently EF is built on the assumption that an alternate key is always populated, but we could definitely relax this. Putting on backlog since we won't be doing this for 1.0.0.

[divega]

Keywords: nullable alternante keys.

[ajcvickers]

Note: allowing alternate keys to be nullable is potentially not difficult--from an EF perspective, if an entity has a null alternate key, then it's the same as that entity not existing for any dependents. Note, however that making alternate keys read-write is much more involved.

[keatkeat87]

@ajcvickers
Today i get this error.
"Unable to track an entity of type 'Demo' because alternate key property 'TestValue' is null. If the alternate key is not used in a relationship, then consider using a unique index instead. Unique indexes may contain nulls, while alternate keys must not."

how if i need to use in a relationship, is it possible or not the right way ?
can give a clear direction for me ?

[buttch]

Hi,
is there any plan to make it possible to have nullable properties as alternate key ?
I work with legacy system with database which has a lot of strange ideas implemented :(
Suddenly it turned out that in columns which I use for relation are null in same cases.
I need to know if I need to implement workaround or it will be implemented in EF Core.

[ajcvickers]

@buttch Currently EF can't handle rows with a null principal key value. One workaround is to write your queries such that they filter out any principal entities with null values. That is, manually add a Where clause to do the filtering.

[buttch]

@ajcvickers Thanks for answer. Unfortunately your workaround it not good for me. This null principal (alternate) key value is in dependent of dependent of dependent of my main entity and I need those entities anyway I cannot filter them out. I think in such case the only way is to load them in separate query and add to entities manually. What do you think ?

[ajcvickers]

@buttch First, make sure you really need an alternate key in EF terms. You said, "it turned out that in columns which I use for relation are null in same case" which I read to mean that these columns are referenced at the principal end of a relationship. If that's not the case, then configure a unique constraint instead of an alternate key as mentioned in the tip here.

Beyond that, I'm afraid EF can't track entities with null alternate key values. Unfortunately, I can't think of any other workarounds.

[mguinness]

Please thumbs up the original post so that it has a better chance of being considered in the Plan for Entity Framework Core 5.0.

Your feedback on planning is important. The best way to indicate the importance of an issue is to vote (thumbs-up 👍) for that issue on GitHub. This data will then feed into the planning process for the next release.

[goforgold]

Hi @BradBarnich

Can you please suggest how exactly you did the workaround?

I have something like this.

builder.Entity<DsOrder>()
        .HasIndex(m => new { m.OrderId, m.ClientId }).IsUnique();

builder.Entity<DsOrderState>()
        .HasOne(m => m.Order).WithMany(m => m.OrderStates).HasForeignKey(m => new { m.ClientId, m.OrderId }).HasPrincipalKey(m => new { m.ClientId, m.OrderId });

My Order entity has OrderId as int? and ClientId + OrderId together are unique. I've not used IsRequired() but still can't get rid of nullable: false in migration. It is always making OrderId in my Order table as not nullable.

Please help!

[Neuroglia]

Hi @BradBarnich,

Just like @goforgold, I'd very much like to know you workaround, if any.

I tried to create a composite index like the following, where OrderType is a nullable enum:

builder.Entity<ProductTypeVat>().HasIndex(ptv => new { ptv.ProductTypeId, ptv.RegionId, ptv.OrderType }).IsUnique();

If I add two ProductTypeVats with the same ProductTypeId, RegionId and OrderType, it just works, EF does not throw any exception as I'd expect.

Using a composite index with a nullable property doesn't seem to do the trick. Or is there something I did not understand?

Please help!

Thanks in advance

[BradBarnich]

At the time, this was a hardcoded behavior that I had to fork EF Core to get around.

I'm not sure what the current code looks like. You can see the change in the commit I link in the root post.

I haven't upgraded to 3.x because #18022 makes 3.x unworkable for us, and we use WCF so 5.x is similarly unworkable because of the .net core requirement. 🙃

[Neme12]

Why is this limitation there? 😔 I simply want a unique constraint where a few columns are nullable, but that's OK. It should behave the same way as a unique index without a filter, which it would do if this worked.

[Neme12]

@buttch First, make sure you really need an alternate key in EF terms. You said, "it turned out that in columns which I use for relation are null in same case" which I read to mean that these columns are referenced at the principal end of a relationship. If that's not the case, then configure a unique constraint instead of an alternate key as mentioned in the tip here.

Beyond that, I'm afraid EF can't track entities with null alternate key values. Unfortunately, I can't think of any other workarounds.

@ajcvickers But is there a way to create a unique constraint without an alternate key (as opposed to a unique index)? The documentation you pointed to doesn't show anything like that.

[ajcvickers]

@Neme12 No. Can you explain why you need it to be a constraint instead of an index? Are you aware of a database that implements a unique constraint and a unique index differently?