[QUESTION] Why is EFCore adding a random 1 to a column name?

[jzabroski]

Describe what is not working as expected.

I'm trying to configure a one-to-one relationship using database-first modeling, and spuriously get exceptions about column names I do not define in my model.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Full stack trace elided, as it is well after the model building process.

Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while updating the entries. See the inner exception for details.
---- System.Data.SqlClient.SqlException : Invalid column name 'ParentId1'.

Steps to reproduce

I'm not yet sure how to reproduce this, but given that I do not have the phrase "1" anywhere in my code base, it must be generated internally by EF. Related to #15581 , there is more seemingly random/annoying behavior where I can't just tell EFCore "You trust me? ... I do.". I'm hoping @AndriySvyryd knows where in the code and why a magic 1 is getting added. It's super-confusing for me.

Further technical details

EF Core version: 2.2.4
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 Pro
IDE: Visual Studio 2017 15.9.11

[smitpatel]

You get 1 added to column name (or rather property name), when conventions define property with the prefix name and such property already exist in model but is incompatible due to type or nullability and convention would add suffix to uniquify the name. Here is the case when you can use ToDebugString and likely find the property ParentId in model, compare with ParentId1 and figure out why EF had to add additional property rather than re-using. You can also post output here and we can do a quick look to tell you the difference.

[jzabroski]

Hi @smitpatel . Thank you very much! It turns out the 1 was added for three one-to-many collections, and I just had to add an Ignore extension method to those properties on the model for now to verify.

You and @AndriySvyryd need matching pipes, 9 inch magnifying glasses, and houndsooth hats.

I've fixed almost all of he random "1" appendages except for one shadow property (fingers crossed). One pain point discovered using ToDebugString is that the properties are enumerated lexicographically, rather than by the order in which they were configured. So for shadow properties, how do I figure out which shadow property this was caused by? And can a shadow property only be added to a specific EntityType through its own EntityTypeBuilder, or can another type add one through a HasForeignKey?

[jzabroski]

The offending line is:

      ParentId (no field, long) Shadow Required FK Index 16 16 2 1 2
        Annotations: 
          Relational:ColumnName: ParentId1
          Relational:TypeMapping: Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerLongTypeMapping

While I wait for a reply, I'm going to try @ErikEJ EFCore Power Tools to see if its got any fancy debugger visualizer to decrypt the bitmasks output (I assume that is what 16 16 2 1 2 is)

[smitpatel]

Order of configuration is deterministic but not user specified. Especially for shadow properties which are added for FKs, they get removed/added again (when facet changes), when FK is reconstructed. Hence lexicographic order. Also it is just uniquification so numbers may change if you remove some relationships from model. Best way to figure out shadow property use is to see the FK defined on the entityType. HasForeignKey string variant can also add shadow properties.

The bitmask you are seeing (in order) (and they are all calculated for entityTypes hierarchy)

• Index (of the property)
• OriginalValueIndex
• RelationshipIndex
• ShadowIndex
• StoreGenerationIndex

It is internal metadata computed based on model and not affected by model configuration.

[AndriySvyryd]

The shadow properties can be added though FKs from any related entity type, so look at which ones are using it.

[jzabroski]

Order of configuration is deterministic but not user specified. Especially for shadow properties which are added for FKs, they get removed/added again (when facet changes), when FK is reconstructed. Hence lexicographic order. Also it is just uniquification so numbers may change if you remove some relationships from model. Best way to figure out shadow property use is to see the FK defined on the entityType. HasForeignKey string variant can also add shadow properties.

Just curious, as I've given this stuff a lot of thought over the last week and have dreamed about a slightly different Metadata API that is mostly lock-free and 100% thread-safe - why wouldn't you do a snapshot prior to uniquification and provide two separate dumps. Perhaps it is do to the fact you're potentially amortizing the cost of model building by incrementally and iteratively updating things as each model component (EntityTypeBuilder<T>/ModelBuilder<T>) gets registered?

[AndriySvyryd]

Just curious, as I've given this stuff a lot of thought over the last week and have dreamed about a slightly different Metadata API that is mostly lock-free and 100% thread-safe - why wouldn't you do a snapshot prior to uniquification and provide two separate dumps. Perhaps it is do to the fact you're potentially amortizing the cost of model building by incrementally and iteratively updating things as each model component (EntityTypeBuilder/ModelBuilder) gets registered?

Column name uniquification happens in the Finalize step, so if you use modelBuilder.Model.ToDebugString() in OnModelCreating you will see the default names.

[jzabroski]

Thanks, that's very helpful. As some feedback, that's a bit of a painful way to call it. I can't put an xUnit ITestOutputHelper in my DbContext, and even if I could, I'd have no clean way to inject it prior to OnModelCreating being called. Unless you have a way that I'm not thinking of.

What I do after the Finalize step is just do _testOutputHelper.WriteLine(dbContext.Model.ToDebugString()); inside my fixture.

[AndriySvyryd]

@jzabroski The way we use it internally is by putting a breakpoint in OnModelCreating and calling ToDebugString() in the Watch/QuickWatch window.

[jzabroski]

I had a HasForeignKey where I should have had a HasPrincipalKey. Closing. Thanks for all your help. This was extremely hard to debug. Could not have done it without you and Smit.

[CharlesOkwuagwu]

Hi Please i'm seeing exact same issue in EF.core 3.1, the same project was working fine in EF.core 2.2

Please is there a workaround to stop having property names with 1 appended ?

https://stackoverflow.com/questions/60073762/migrating-to-ef-core-3-1-from-ef-core-2-2-breaks-generated-sql

[ajcvickers]

@CharlesOkwuagwu If you think you have it a bug, then please file a new issue and attach a small, runnable project that reproduces the behavior you are seeing.