Basic SQL / Entity Framework 1 to 0..1 entity model question

Question

In my database I have 2 tables A and B

A has columns ID, Title, and B_ID. B_ID allows nulls. B has columns ID and SomeText ID in both is primary key identity A also has a unique index on B_ID

This seems like a basic way to say that A can have 0 or 1 B rows associated with it, and each B is associated with exactly 1 A. Is that correct so far ?

When I create an ADO.NET Entity Framework entity model of that database, A has a navigation property to B which has multiplicity 0..1. That makes sense.

Entity B has a navigation property to A, but the multiplicity on that is *(Many).

I guess my database definition won't let one B get associated to many A's, so maybe it's not a problem, but is there a way to define the database and/or modify the entity model so that the navigation property from B to A has a Multiplicity of 1 ?

Answer 1

I don't fully understand your entity setup, but if B has a primary key that includes both the ID value and any other field, or no primary key relationship at all, then the relation is correct as Entity Framework displays it. You might double-check your schema to make sure everything is configured as you believe it is.

To have a multiplicity of 1 from B to A you would need to have a primary key in B that is solely the ID value that is used as a foreign key in A.

If my interpretation of your entity layout isn't correct, please clarify your question and I'll try to help further.

Answer 2

Thanks mwigdahl, the answer was in fact in the database definition.

I changed the table definitions. Table A has columns ID, B_ID and Title Table B has columns A_ID and SomeText

ID is the primary key of table A A_ID is the primary key of table B B_ID in Table A is a foreign key reference to column A_ID in Table B

When I create an Entity Framework Model from that db definition I get the expected 1 to 0..1 relationship between A and B.

Answer 3

It sounds like you should omit B's Id in A's table (is it the same as A's id??) Are you doing something like this?

CREATE TABLE dbo.People(PersonId int NOT NULL, Name varchar(50) NOT NULL)
CREATE TABLE dbo.BioData(PersonId int NOT NULL, ExtraInfo varchar(255) NOT NULL)