Entity Framework double foreign key column from fluent mapping

I have a legacy SQL Server database with legacy entity structures (I can NOT change the database or the entities in this case)

Tables:

  • MS Access holds locks on table rows indefinitely
  • Not able to connect any remote database server after installing Visual studio 2012
  • How to cast datetime to datetimeoffset?
  • How to prevent SQL Server LocalDB auto shutdown?
  • Restrict SQL Server Connection to Specific IP Address
  • SQL - Finding common rows based on a column (intersect)
  • Companies:

       Id (PK, uniqueidentifier, not null)
       Name (varchar(200), not null)
    

    Locations:

       Id (PK, uniqueidentifier, not null)
       CompanyId (FK, uniqueidentifier, not null)
       Name (varchar(200), not null)
    

    Entities:

    public class Company
    {
        public virtual Guid Id { get; set; }
        public virtual string Name { get; set; }
        public virtual ICollection<Location> Locations { get; set; }
    }
    
    public class Location
    {
        public virtual Guid Id { get; set; }
        public virtual string Code { get; set; }
        public virtual string Name { get; set; }
        public virtual Company Company { get; set; }
    }
    

    Model with mapping:

    public partial class CompanyModel : DbContext
    {
        public virtual DbSet<Company> Companies { get; set; }
        public virtual DbSet<Location> Locations { get; set; }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Location>()
                .HasRequired(e => e.Company)
                .WithMany()
                .Map(m => m.MapKey("CompanyId"));
        }
    }
    

    Sample program:

    var guid = Guid.Parse("XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXX");
    
    using (var mod = new CompanyModel())
    {
       var company = mod.Companies.FirstOrDefault(e => e.Id == guid);
       var locations = company.Locations;
    }
    

    If I now run this program the SQL trace produces the following:

    exec sp_executesql N'SELECT 
    1 AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Code] AS [Code], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[CompanyId] AS [CompanyId], 
    [Extent1].[Company_Id1] AS [Company_Id1]
    FROM [dbo].[Locations] AS [Extent1]
    WHERE ([Extent1].[Company_Id1] IS NOT NULL) AND ([Extent1].[Company_Id1] = @EntityKeyValue1)',N'@EntityKeyValue1 uniqueidentifier',@EntityKeyValue1='XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXXXXX'
    

    As you can see the Company_Id1 is included. If I did not map the column in the OnModelCreating, I would have gotten Company_Id and Compan_Id1

    I’m sure the issue lies with my mapping, any assistance there would be appreciated.

  • What are the APIs used by SQL Profiler?
  • Indexed View vs Indexes on Table
  • What is the connection string for localdb for version 11
  • SQL query using fn_Split to find multiple values in column
  • Check who performed insertion in table on MS SQL Server
  • SQL - Find number in a range of numbers
  • 3 Solutions collect form web for “Entity Framework double foreign key column from fluent mapping”

    Best practice is to include the FK property in the Entity model. This also gives you convention-based configuration for the foreign key.

    public class Company
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Location> Locations { get; } = new HashSet<Location>();
    }
    
    public class Location
    {
        public Guid Id { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public Guid CompanyId { get; set; }
        public virtual Company Company { get; set; }
    }
    

    The issue is caused by the following line:

    .WithMany()
    

    Basically you are telling EF something like “I have a relationship between Company and Location, with FK column named LocationId (but no explicit LocationId property), Company reference navigation property in Location entity and no collection navigation property in Company entity”.

    So far so good. But when EF encounters Locations collection navigation property in Company entity, by convention it considers another relationship with no reference navigation property in Location, no FK property and auto generated FK column name. Which is definitely not what was your intention.

    As a rule of thumb, make sure the fluent configuration always reflect the presence/absence of the navigation and FK properties. In your case, simply change the above line to

    .WithMany(e => e.Locations)
    

    and the issue will be solved.

    I would change the company model into this:

    public class Company
    {
         [Key]  // This tells the engine that the following property id the primary key
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)] // this tells the engine that the following property's value is assigned by DB
        public Guid Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Location> Locations { get; set; }
    }
    

    Now, why do you see the Company_Id1 in the query? Because in entity framework, that’s the default naming convention for the primary key. You are using a different name for the PK, so you need to define your primary key in the model using the [Key] data annotation.

    Your inserts and updates should not assign the value of this column (DB should assign the value), that’s why you need to add [DatabaseGenerated(DatabaseGeneratedOption.Identity)] to your model.

    Also, virtual is used for navigation properties (example: foreign keys), that’s why I removed the virtual tag from the first two properties in the company model.

    You need to do the same for the Locations model too.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.