Entity Framework Association wants to use child tables primary key as foreign key

I have two tables, family_fishing (the parent table) with a key of PROGRAMNUMBER and family_fishing_reg with a key of randomnumber (seriously, I did not design these tables) and a column of programnumber which should be the foreign key.

ProgramNumber in both tables is NOT NULL. The type in both tables is the same.

When I bring them into Entity Framework and create an assocation, the association wantd the dependant key to be the randomnumber column, the entity key on the child table. (RandomNumber is a guid.)

When I double click on the association I can ONLY select randomnumber for the dependant key but this will never relate to family_fishing.PROGRAMNUMBER which is an nvarchar(6). (Yes, a key column as an nvarchar is not my choice either.)

I have to work with the tables that I have. I'm trying to plug into a database that is not managed by me. I have binged and googled all over and can't seem to understand why this is. The association is looking for a mapping, which also wants to use the randomnumber column. But this should be a typical 1 to many foreign key association.

What can I try? What can I read? Who can I ask to find an answer to this?

I take it you've read this http://msdn.microsoft.com/en-us/library/ee382822.aspx and this http://msdn.microsoft.com/en-us/library/ee382820.aspx.

I'd be inclined to scrap that database and do it properly. Having columns called "SomethingNumber" one of which is a Guid and the other nvarchar(6) is so seriously screwed up as to be virtually unworkable. It is certainly a huge barrier to understanding and so to the maintainability of code.

If it says its a "number" it should be a number, not a code or a GUID and leave you to guess which one.

Yes...

However, that would mean, not only writing the system that reads this data, which is the job I'm working on, it would mean re-writing the system that collects the data which is handled by someone else. So, I either fix their mess for double the time, or I try to work with what I've got.

Or you write an intermediate system (such as a SSIS package) which translates their mess into something that's actually useable.