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!

[cdavernas]

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?

[rp0m]

So just to confirm, currently, EF Core 3.0 does not support nullable composite foreign keys/alternate keys, correct?

I've got this code in my context, however I still get the foreign key generated as not nullable :(

modelBuilder
    .Entity<EntityOne>()
    .HasOne(x => x.EntityTwo)
    .WithOne(x => x.EntityOne)
    .HasForeignKey<EntityTwo>(x =>
        new
        {
            x.IdOne,
            x.IdTwo
        })
    .IsRequired(false)
    .HasPrincipalKey<EntityOne>(x =>
        new
        {
            x.EntityTwoIdOne,
            x.EntityTwoIdTwo
        });

[ajcvickers]

@rp0m Nullable foreign keys are supported and commonly used. Alternate keys cannot be nullable.

[reinux]

It also seems to be assuming that I have an alternate key when I really don't want it to be an alternate key. Not sure how to fix that.

[dillontsmith]

+1 on requesting this feature. It would be very useful for us.

[jyothi530]

@ajcvickers EF Core v6.0.3: I am facing some weird issue on an entity with Alternate Key. The AlternateKey column is nullable. When I load the entity along with the AlternateKey navigation property using Include(), the query generated is making an inner join on the AlternateKey navigation table. If I don't eager load the alternate key navigation property and load just the entity with the data containing null value for AlternateKey it is throwing SqlNullException (Data is Null).

[Neme12]

@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?

You're right, I don't need it, I'd just prefer it, and if it was possible, I wouldn't need a separate property as a primary key - I could simply use a composite key from 2 columns where one or both are nullable (and where null should be a unique value too).

[rcreynolds53]

+1 on requesting this feature highly useful

[wjax]

+1

[acraven]

+1, I'm currently migrating a system from NHibernate and I have stumbled upon this problem.

[marchy]

Can you please tackle this in EF9 as part of the work to support nullable complex types?

Both these features become nearly useless when they can be only applied to non-nullable types/fields. It's a complete disconnect with real domains in the wild, which in practice tend to have MOST fields be nullable / vary by some related attribute, and there is very little you can guarantee will never be null (ie: it's highly academic to design an ORM and the systems they power against such assumptions).

UPDATE: It seems unique indexes don't work with nullable columns either. This really needs to be tackled as a cross-cutting epic: "Make Nullable Columns Work (everywhere)".

[its-jefe]

Just hit this snag and having nullable alternate keys would be lovely. Looking into workarounds now

[PoteRii]

+1

[SeriaWei]

+1

[enrij]

+1 for @marchy comment on unique indexes with nulls... I really fell off the chair when i discovered the "autofilter" on non null values while creating an UNIQUE index (better explained by @marchy himself on his link, no kudos for me)