Having serious problems with Entity Framework & Foreign Keys :(

Question

Hi folks,

I've got the following entities on my EDMX :-

These two entites were generated by Update Model From Database.

Now, notice how my country has the following primary key :-

Name & IsoCode

this is because each country is UNIQUE in the system by Name and IsoCode.

Now, with my States ... it's similar. Primary Key is :-

Name & CountryId

Each state is unique by name and per country.

Now, the Foreign Key for States is a CountryId. This is the sql :-

ALTER TABLE [dbo].[States]  WITH CHECK ADD 
        CONSTRAINT [FK_States_Countries] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Countries] ([CountryId])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[States] CHECK CONSTRAINT [FK_States_Countries]
GO

Pretty simple stuff.

BUT EntityFramework doesn't like it :( It's assuming that i need to connect some properties from State entity to both primary key properties in the Country entity.

Is it possible to add an ASSOCIATION between Country and State on Country.CountryId <-> State.CountryId ... like i have mapped in my DB ?

Cheers ;)

Answer 1

In EF (3.5 and 4.0) FKs MUST point to Primary Keys.

But you appear to be attempting to point to a Candidate Key (i.e. [Countries].[CountryId]

I know that this is something the EF team are considering for the next version though :)

Hope this helps

Alex