Foreign key with backing field producing invalid sql

[dmcweeney]

Hi,

I'm getting the following error when trying to define a backing field for a foreign key field.
{System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'RegionName1'.

Entity def is simple - RegionName is FK to table Regions and Region in included as a navigation property.
Note the following works when not using backing fields.

public class Service
{
	private Guid m_serviceId ;
	private string m_regionName ;

	public Service()
	{
	}

	public Guid ServiceId 
	{ 
		get { return this.m_serviceId ; }
	}

	public string RegionName
	{
		get { return this.m_regionName; }
	}

	// Navigation properties

	public Region Region { get; set; }
}

The modelbuilder customization is as follows:

modelBuilder.Entity<Service>( b =>
{
	b.ToTable( "Services" );
	b.HasKey( table => table.ServiceId );
	// configure foreign key to Region
	b.HasOne<Region>( table => table.Region )
		.WithMany()
		.HasForeignKey( table => table.RegionName );
	b.Property( table => table.ServiceId )
		.IsRequired()
		.UsePropertyAccessMode( PropertyAccessMode.FieldDuringConstruction );
	b.Property( table => table.RegionName )
		.IsRequired()
		.HasMaxLength( 50 )
		.UsePropertyAccessMode( PropertyAccessMode.FieldDuringConstruction );

And query includes the Region reference to get it loaded.

await this.m_dbContext.ServiceAppInstances
	.Include( service => service.Region )
	.SingleOrDefaultAsync( service => service.ServiceId == serviceId )
	.ConfigureAwait( false );

Results in the following sql being generated:
Note incorrect inclusion of column [RegionName1].

SELECT TOP(2) [service].[ServiceId], [service].[RegionName], [service].[RegionName1]
FROM [Services] AS [service]
INNER JOIN [Regions] AS [r] ON [service].[RegionName] = [r].[Name]
WHERE [service].[ServiceId] = @__serviceId_0

Is this a bug or am I doing something stupid.
Using EF 1.1

Thanks

Donal

[ajcvickers]

@dmcweeney Can you post the code for the Region class?

[dmcweeney]

@ajcvickers I discovered a workaround by adding an empty setter from the Service.RegionName property. With this EFC generates the correct sql.

public string RegionName
{
	get { return this.m_regionName; }
	set 
	{
		// added setter to keep EF happy with the sql it generates
		throw new InvalidOperationException() ;
	}
}

For the record the following is the code for the Region class

public class Region
{
	private string m_name ;

	public Region()
	{
	}

	public Region( string name )
	{
		this.m_name = name ;
	}

	public string Name
	{
		get { return this.m_name ; }
	}

	public string DisplayName { get; set; }
}


modelBuilder.Entity<Region>( b =>
{
	b.ToTable( "Regions" );
	b.HasKey( table => table.Name );
	b.Property( table => table.Name )
		.IsRequired()
		.HasMaxLength( 50 )
		.UsePropertyAccessMode( PropertyAccessMode.FieldDuringConstruction );
	b.Property( table => table.DisplayName )
		.IsRequired()
		.HasMaxLength( 256 );
} );

[ajcvickers]

Note for triage: This looks like an issue in model building. The model has a shadow FK created, presumably because the RegionName property is not mapped at the time the HasOne call is made. This gets replaced by the correct FK once the ForeignKey call is made later in the chain, but the FK and shadow property are left dangling rather than being removed.

Model: 
  EntityType: Region
    Properties: 
      Name (m_name, string) Required PK ReadOnlyAfterSave RequiresValueGenerator ValueGenerated.OnAdd MaxLength50 PropertyAccessMode.FieldDuringConstruction 0 0 0 -1 0
        Annotations: 
          MaxLength: 50
          PropertyAccessMode: FieldDuringConstruction
      DisplayName (string) Required MaxLength256 1 1 -1 -1 -1
        Annotations: 
          MaxLength: 256
    Keys: 
      Name PK
    Annotations: 
      Relational:TableName: Regions
      RelationshipDiscoveryConvention:NavigationCandidates: System.Collections.Immutable.ImmutableSortedDictionary`2[System.Reflection.PropertyInfo,System.Type]
  EntityType: Service
    Properties: 
      ServiceId (m_serviceId, Guid) Required PK ReadOnlyAfterSave RequiresValueGenerator ValueGenerated.OnAdd PropertyAccessMode.FieldDuringConstruction 0 0 0 -1 0
        Annotations: 
          PropertyAccessMode: FieldDuringConstruction
      RegionName (m_regionName, string) Required FK Index MaxLength50 PropertyAccessMode.FieldDuringConstruction 1 1 1 -1 1
        Annotations: 
          MaxLength: 50
          PropertyAccessMode: FieldDuringConstruction
      RegionName1 (no field, string) Shadow FK Index 2 2 2 0 2
    Navigations: 
      Region (<Region>k__BackingField, Region) ToPrincipal Region 0 -1 3 -1 -1
    Keys: 
      ServiceId PK
    Foreign keys: 
      RegionName -> Region.Name ToPrincipal: Region
      RegionName1 -> Region.Name
    Annotations: 
      Relational:TableName: Services
      RelationshipDiscoveryConvention:NavigationCandidates: System.Collections.Immutable.ImmutableSortedDictionary`2[System.Reflection.PropertyInfo,System.Type]
Annotations: 
  ProductVersion: 1.1.0-rtm-22752
  SqlServer:ValueGenerationStrategy: IdentityColumn

[AndriySvyryd]

This is probably already fixed in 2.0.0

[AndriySvyryd]

Confirmed fixed