Efcore: [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.

[jzabroski]

@charlesokwuagwu Hello my brother,

From your SO post, it looks like you should start minimizing your repro by removing Contracts, Timesheets and GuardGuardians from your model, and incrementally and interactively making adjustments until the smallest possible repro is found.

In general, with code first data modeling, EFCore tries to infer column names from usage.

In your case, you are exposing both guard_id and virtual Guard Guard without telling it guard_id is the foreign key. So it PROBABLY thinks the second Guard's column name is guard_id1 because guard_id is "already taken".

As one suggestion, consider moving guard_id below virtual Guard Guard property and see if that changes the error. Note, if the error is completely gone, this doesn't mean the issue is gone, so don't stop there.

[jzabroski]

Also, if you look at the xUnit tests I wrote for EntityFrameworkCore, that might be a good "shell" for an interactive environment to quickly find the root cause.

See #15553 where I link to that shell, here:

https://github.com/jzabroski/EntityFrameworkMappingTablePerHierarchy

[CharlesOkwuagwu]

In your case, you are exposing both guard_id and virtual Guard Guard without telling it guard_id is the foreign key. So it thinks the second Guard's column name is guard_id1 because guard_id is "already taken".

@jzabroski Thanks for this reply.

My initial confusion comes from the fact that all this worked perfectly well in EF.core 2.1/ 2.2 . I just upgraded my server to .net core 3.1 and this app broke.

I'm going through it now to understand what needs to be updated for it to keep working in .net core 3.1

Seems EF.core 2.2 code will not work as-is in EF 3+

Thanks for the responses and suggestions

[AndriySvyryd]

Seems EF.core 2.2 code will not work as-is in EF 3+

3.0.0 is the most breaking release of EF Core

[CharlesOkwuagwu]

```
[HttpGet(Name = nameof(GetDeploymentsAsync))]
public IActionResult GetDeploymentsAsync([FromQuery] QueryParams p)
{
var s = db.Deployments
.Include(e => e.Guard)
.Include(e => e.Site)
.AsNoTracking(); // no-tracking-queries-no-longer-perform-identity-resolution?

  if (p.search != null) { s = s.Where(p.search_field, "Contains", p.search); }
  if (p.sortBy != null) { s = s.OrderByMember(p.sortBy, p.descending); }

  var l = new Pager<Deployment>(s, p.page, p.rowsPerPage);

  return Ok(l);
}

https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#no-tracking-queries-no-longer-perform-identity-resolution

I had depended a lot on the pattern of code above. I do not typically need the tracking option.
What i'm trying to achieve is easy: Include the Site and Guard details associated with a Deployment...

public class Deployment
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Int32 deployment_id { get; set; }

public Int32 guard_id { get; set; }
public Int32 site_id { get; set; }
public DateTime? dt_start { get; set; }
public DateTime? dt_end { get; set; }
public int? days_on { get; set; }
public int? days_off { get; set; }

public DateTime? last_modified { get; set; }
public int? last_modified_by { get; set; }

public virtual Guard Guard { get; set; }
public virtual Site Site { get; set; }

}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
//modelBuilder.Entity().HasOne(x => x.Guard).WithMany().HasForeignKey(x => x.guard_id);
//modelBuilder.Entity().HasOne(x => x.Site).WithMany().HasForeignKey(x => x.site_id);
...
}

I added then removed the `HasOne`, `HasForeignKey `, etc above...

At this point it's becoming trial and error.

**logs**:

dbug: Microsoft.EntityFrameworkCore.Model.Validation[10600]
The property 'guard_id1' on entity type 'Deployment' was created in shadow state because there are no eligible CLR members with a matching name.
dbug: Microsoft.EntityFrameworkCore.Model.Validation[10600]
The property 'site_id1' on entity type 'Deployment' was created in shadow state because there are no eligible CLR members with a matching name.
```

[CharlesOkwuagwu]

Finally Got this to work! What i was missing was:

  modelBuilder.Entity<Deployment>().HasOne<Guard>(x => x.Guard).WithMany(x => x.Deployments).HasForeignKey(x => x.guard_id);
  modelBuilder.Entity<Deployment>().HasOne<Site>(x => x.Site).WithMany(x => x.Deployments).HasForeignKey(x => x.site_id);

[jzabroski]

That looks about right.

[sandipkumarnandi]

Its like if you have a collection (one-many) in a class then its not required to define the relationship in EntityConfiguration or Context file

eg. If you are defining a .hasMany relationship its not required.

the below statement is not required, (if kept it will create an additional column and key for that column)
//entityBuilder.HasMany(t => t.StaffProfiles)
// .WithOne().HasForeignKey(t => t.StaffId)
// .OnDelete(Microsoft.EntityFrameworkCore.DeleteBehavior.Restrict)
// .IsRequired(false);