EF4 One to many to one with Db foreign key

Question

I am trying to use code first with an existing db. So far it went well, but now I am failing at a one to many to one relationship.

There is a table customer and a table address in the db where address does NOT have any customerid but a foreign one to many key FK_Address_Customer.

The auto created classes from the edmx look like

Customer:

public int CustomerID (PK)
public Address Address

Address:

public int AddressID (PK)
public HashSet<Customer> Customers

Whatever I do in the fluent API either fails with invalid column Address_AddressID or Multiplicity conflicts with the referential constraint error.

I assumed:

//Customer has one address, address can have multiple customers<br/>
pModelBuilder.Entity<Customer>().HasRequired(m => m.Address).WithMany(x => x.Customers);


//Address has multiple customers, customer has one address<br/>
pModelBuilder.Entity<Address>().HasMany(m => m.Customers).WithRequired();

I got this correct on other tables where there where NO foreign keys in the db by using HasForeignKey, but in the above scenario it does not work. I also tried via MapKey passing the foreign key name also with no luck.

Ho do I get this simple relationship going ?

Thanks

Can you post your existing database? You say that there's a foreign one to many key on Address to Customer, but it's not in the classes above. Does your customer object have an AddressId, or are you using a linked table? — Mark Oreta, Aug 17 '12 at 23:32

score 2 · Accepted Answer · edited May 23 '17 at 12:12

You must tell EF the name of FK in Customer table otherwise it will use default Address_AddressID. HasForeignKey will do the magic.

pModelBuilder.Entity<Customer>()
             .HasRequired(c => c.Address)
             .WithMany(a => a.Customers)
             .HasForeignKey(c => c.AddressID);

Where AddressID is FK property in your Customer entity. If you don't have FK property in the Customer entity you need to use Map:

pModelBuilder.Entity<Customer>()
             .HasRequired(c => c.Address)
             .WithMany(a => a.Customers)
             .Map(m => m.MapKey("AddressID"));

Where AddressID is a name of FK column in Customer table. You always need that column to have one-to-many relation.

The difference between having and not having FK property in your entity make difference between two types of associations supported by EF.

I already tried your both suggestions above and it failed. But stupid me, I did not look into the details of the foreign key ! Although the name is FK_Customer_Address, the underlying column is PhysicalAddressID and not AddressID. So I just used the wrong key and now it works with foreign key being the PhysicalAddressID . Thanks so much ! — user1029883, Aug 20 '12 at 13:29

EF4 One to many to one with Db foreign key

1 Answers1