Databases
StackOverflow2013
Postsdbo
POEF 4.1 RC Code First - Mapping to existing database & specifying foreign key name
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEF 4.1 RC Code First - Mapping to existing database & specifying foreign key name
    text
    copied!<p>I have two classes. A Company has a County set against it:</p> <pre><code>public class Company { public int Id { get; set; } public string CompanyName { get; set; } public Country HomeCountry { get; set; } } public class Country { public int Id { get; set; } public string Code { get; set; } public string Name { get; set; } } </code></pre> <p>I am trying to map to an existing database where the Company table contains the foreign key of the Country record. So I presumably need to tell code first the name of the foreign key column.</p> <p>Below is the complete code example. It's currently failing with different exceptions based on different things that I try. There's seems to be a lack of cohesive documentation on this as yet.</p> <p>So using Code First Fluent API how do I define the name of the foreign key column?</p> <p><strong>Test app:</strong></p> <p>Create database as follows: CREATE DATABASE CodeFirst; GO</p> <pre><code>Use CodeFirst create table Companies ( Id int identity(1,1) not null, HomeCountryId int not null, Name varchar(20) not null, constraint PK_Companies primary key clustered (Id) ) create table Countries ( Id int identity(1,1) not null , Code varchar(4) not null , Name varchar(20) not null , constraint PK_Countries primary key clustered (Id) ) alter table Companies add constraint FK_Company_HomeCountry foreign key (HomeCountryId) references Countries (Id) on delete no action </code></pre> <p>Now run the following C# app:</p> <pre><code>using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Entity.ModelConfiguration; using System.ComponentModel.DataAnnotations; using System.Data.Entity; using System.Data; namespace CodeFirstExistingDatabase { class Program { private const string ConnectionString = @"Server=.\sql2005;Database=CodeFirst;integrated security=SSPI;"; static void Main(string[] args) { // Firstly, add a country record, this works fine. Country country = new Country(); country.Code = "UK"; country.Name = "United Kingdom"; MyContext myContext = new MyContext(ConnectionString); myContext.Countries.Add(country); myContext.Entry(country).State = EntityState.Added; myContext.SaveChanges(); Console.WriteLine("Saved Country"); // Now insert a Company record Company company = new Company(); company.CompanyName = "AccessUK"; company.HomeCountry = myContext.Countries.First(e =&gt; e.Code == "UK"); myContext.Companies.Add(company); myContext.Entry(company).State = EntityState.Added; myContext.Entry(country).State = EntityState.Unchanged; myContext.SaveChanges(); Console.WriteLine("Saved Company"); // If I can get here I'd he happy! } } public class MyContext : DbContext { public DbSet&lt;Company&gt; Companies { get; set; } public DbSet&lt;Country&gt; Countries { get; set; } public MyContext(string connectionString) : base(connectionString) { Database.SetInitializer&lt;MyContext&gt;(null); Configuration.LazyLoadingEnabled = false; Configuration.ProxyCreationEnabled = false; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new CountryConfiguration()); modelBuilder.Configurations.Add(new CompanyConfiguration()); base.OnModelCreating(modelBuilder); } } public class CompanyConfiguration : EntityTypeConfiguration&lt;Company&gt; { public CompanyConfiguration() : base() { HasKey(p =&gt; p.Id); Property(p =&gt; p.Id) .HasColumnName("Id") .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity) .IsRequired(); Property(p =&gt; p.CompanyName) .HasColumnName("Name") .IsRequired(); ToTable("Companies"); } } public class CountryConfiguration : EntityTypeConfiguration&lt;Country&gt; { /// &lt;summary&gt; /// Initializes a new instance of the &lt;see cref="CountryConfiguration"/&gt; class. /// &lt;/summary&gt; public CountryConfiguration() : base() { HasKey(p =&gt; p.Id); Property(p =&gt; p.Id) .HasColumnName("Id") .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity) .IsRequired(); Property(p =&gt; p.Code) .HasColumnName("Code") .IsRequired(); Property(p =&gt; p.Name) .HasColumnName("Name") .IsRequired(); ToTable("Countries"); } } public class Company { public int Id { get; set; } public string CompanyName { get; set; } public Country HomeCountry { get; set; } } public class Country { public int Id { get; set; } public string Code { get; set; } public string Name { get; set; } } } </code></pre> <p>The above fails with the following when saving the country: Invalid column name 'HomeCountry_Id</p> <p>Any help would be very much appreciated!!</p> <p>Thanks, Paul.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload