Cannot create a unique index / alternate key - and wrong error message is given

[mrpmorris]

I am trying to avoid duplicate entries in a link table.

When I use either HasIndex or HasAlternateKey using a navigation property, not only does EF migrations disallow it, but it gives me a wrong error message (which has led me on a wild goose chase for most of the day).

Source code

Domain classes

public class Parent
{
	public Guid Id { get; private set; } = Guid.NewGuid();
	public virtual IEnumerable<Child> Children { get; private set; } = new List<Child>();
}

public class Child
{
	public Guid Id { get; private set; } = Guid.NewGuid();
	public virtual Parent Parent { get; set; } = null!;
	public string Name { get; set; } = "";
}

ApplicationDbContext

internal class ApplicationDbContext : DbContext
{
	public DbSet<Child> Children { get; set; } = null!;
	public DbSet<Parent> Parents { get; set; } = null!;


	protected override void OnConfiguring(DbContextOptionsBuilder options)
	{
		base.OnConfiguring(options);
		options.UseSqlServer(
			 connectionString: @"Server=.\SQLExpress;Database=StevenTCramerSmells;Trusted_Connection=Yes;",
			 x => x.EnableRetryOnFailure());
	}

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		base.OnModelCreating(modelBuilder);
		modelBuilder.Entity<Parent>(x =>
		{
			x.HasMany(x => x.Children).WithOne(x => x.Parent);
		});

		modelBuilder.Entity<Child>(x =>
		{
// See notes for why these lines are commented out.
//			x.Property(x => x.Parent)
//				.HasColumnName(nameof(Child.Parent))
//				.IsRequired()
//				.HasColumnType("uniqueidentifier");
			x.HasIndex(x => new { x.Parent, x.Name }).IsUnique();
		});
	}
}

csproj file

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.8">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Proxies" Version="6.0.8" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.8" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.8">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
  </ItemGroup>

</Project>

The error message I see is

'Parent' cannot be used as a property on entity type 'Child' because it is configured as a navigation.

This has led me to believe my problem is the C# property Parent on the Child class - but it is nothing to do with that. It is the Parent shadow property that is being added by the index.

So my ticket has two parts

1: Could the message be changed to something like "Failed to add shadow property 'Parent' on entity type 'Child' when creating an index, because a shadow property named Parent has already been defined as a navigation property"

2: How do I ensure uniqueness in my link table by combining the Parent and OtherTable columns into a unique index?

Notes

Please note that I considered whether or not this issue is a duplicate of #11336 or not, but decided it wasn't because of the following reasons.

A: This ticket asks for the error message to be changed to make it clear the problem is not with the Child.Parent property the application source code, but a shadow-property issue, and that the message should say it is the index/alternate key causing the problem (showing the index name would be fantastic)

B: I tried the solution proposed in that ticket and it did not solve the issue in this case. See the commented out lines in ApplicationDbContext above.

[GioviQ]

I suspect it would be better not to use implicit keys to discover your issue.

In your code Parent seems treated like a field, maybe EF handles this but is not clear

I am sure that if you introduce ParentId property as a Guid your troubles will gone, because with explicit keys I have never had such problems.

[mrpmorris]

My real app explicitly states Id as the primary key and it happens in that too.

[GioviQ]

So what about '.HasColumnType("uniqueidentifier")' ? (EF chooses type from your primary key)

[mrpmorris]

Sorry, I don't know where you are suggesting I put that, and I don't understand how specifying a column type will stop it from being used twice.

[GioviQ]

public class Parent
{
	public Guid Id { get; set; } //unless ValueGeneratedNever, it is just autogenerated
	public virtual ICollection<Child> Children { get; set; }
}

public class Child
{
	public Guid Id { get; set; } //unless ValueGeneratedNever, it is just autogenerated
        public Guid ParentId { get; set; }  //Guid? if Parent is not mandatory
	public virtual Parent Parent { get; set; }
	public string Name { get; set; } = "";
}

modelBuilder.Entity<Parent>(x =>
		{
			x.HasMany(x => x.Children).WithOne(x => x.Parent).HasForeignKey(x => x.ParentId); //maybe redundant
		});

modelBuilder.Entity<Child>(x =>
		{
			x.HasIndex(x => new { x.ParentId, x.Name }).IsUnique();
		});

I added ParentId. It should be the same name already present in your db, but hidden in the code.
I also remove the unsual private and default value just correctly managed by EF.
Usually I use ICollection too.

Sometimes previuos migrations are a mess, so it is better to start with a new single one.

[mrpmorris]

Ah I see. But my property is called Parent and I want the DB column to be called Parent.

I don't use the Id suffix.

[GioviQ]

modelBuilder.Entity<Child>(x =>
		{
			x.Property(x => x.ParentId).HasColumnName(nameof(Child.Parent));
		});

[mrpmorris]

I also don't use both X and XId. I use one or the other depending on whether I am within an aggregate or not.

I should be able to create a unique index, surely?

[GioviQ]

You don't use them, but they exists even if EF hides the XIds. So if you want the parents have only one child a index is necessary.

Can exist also parents with the same name? I suppose not, so an unique index for name is also mandatory.

You should read https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#one-to-one

It seems to me you want a one-to-one and not one-to-many because the index realizes that. And EF can create the index under the cover as you can read.

[mrpmorris]

I actually have First 1<--->* LinkTable *---->1 Second and just want to ensure I don't have duplicates in the link table.

I just checked and you are right about LinkTable having an XId column. I need this to just be called X.

[mrpmorris]

@GioviQ I've standardised on XId instead and it works, thank you!

I think the error message needs to be more helpful, though.

[ajcvickers]

@mrpmorris The error message, "'Parent' cannot be used as a property on entity type 'Child' because it is configured as a navigation." is intended to say that in one part of your code the CLR property Parent is being used as a simple property, while in another part of your code it is being used as a navigation. This does appear do be what you are doing. Is, perhaps, the confusion coming from the fact that HasIndex and HasAlternateKey cannot be used on navigations? Maybe we could make this clearer?

[mrpmorris]

@ajcvickers Yes, that is definitely the confusion. I spent hours trying to understand why my navigation property was invalid. It was only when I stripped down the app to provide a repro that it became apparent it was the index that was the problem.

"Parent cannot be used as a property on entity type Child" makes me look at the Child.Parent property class for the problem.

To be honest, even knowing what the problem is, I struggle to understand the error message.

• ModelBuilder was instructed to perform an operation that required the creation of a shadow property named Parent on the Child entity, but the ModelBuilder has already created a shadow property with this name for a navigation property. If you need to define additional information about the Child.Parent relationship (such as a unique index) then explicitly define a property Child.ParentId on your entity class and use that when using ModelBuilder.

This (if I understand correctly) is better because

1. It tells me the problem is with my instructions to ModelBuilder and not my entity code.
2. It tells me it is the shadow properties where the problem is, and not my definition of the Child.Parent (e.g. I have missed virtual).
3. It tells me the ModelBuilder has been asked to do two contradictory things.
4. It tells me that one of the things is creating a shadow property that is for my navigation property (and it's not the navigation property itself that is the problem).
5. If gives an example of what kind of thing causes the problem (indexes).
6. It tells me the correct approach.

Please feel free to ask if ever you need to know how simple minded people can possibly misunderstand words :)

[ajcvickers]

@mrpmorris So we can understand better how you got here, can you provide some details on how you came by writing this?

x.HasIndex(x => new { x.Parent, x.Name }).IsUnique();

For example, did you look at the API documentation? Or search for examples and follow them? Or read about indexes in the EF docs? Find it in a book? Etc.

(We've been discussing lately how to better help people find the information they need, and part of that is understanding how people attempt to find information, and whether or not they are successful in doing so.)

[mrpmorris]

@ajcvickers
Typically I look at docs when something doesn't work as my intuition expects it to.

I don't know if I've read that format in the past, or just intuitively guessed, but I just wrote it out on auto-pilot and didn't look into it because it works.

But I wrote out the entity class and the whole modelBuilder.Entity<T>(x => {......}) in one go, so I had no idea it was the index that was the problem as the error message led me to believe it was the Parent property on my Child class.

[mrpmorris]

@ajcvickers In answer to your question regarding where I saw it. I was just looking this up again and found this

https://learn.microsoft.com/en-us/ef/core/modeling/indexes?tabs=data-annotations

Note that if you call HasIndex more than once on the same set of properties, that continues to configure a single index rather than create a new one:

modelBuilder.Entity<Blog>()
    .HasIndex(b => new { b.FirstName, b.LastName })
    .HasDatabaseName("IX_Names_Ascending");

modelBuilder.Entity<Blog>()
    .HasIndex(b => new { b.FirstName, b.LastName })
    .HasDatabaseName("IX_Names_Descending")
    .IsDescending();