CODEWITHSUNDEEP
c#sqlsql-serverentity-framework
Stachu
Stachu

Reputation: 5857

Trying to set up a 0..1 : 1 relationship with SQL and Entity Framework

Let's say there are two tables in our db.

Table1 has fields:

Id
Field 2
Field 3

Table2 has fields:

Table1Id
Field 2
Field 3

Right now Table1's PK is Id, and Table 2's PK is Table1ID (which makes sense.)

Table1 may or may not have a Table2 entity related to it. (depending on whether Table1.Field2's value is 3, but anyways..)

I've set up a FK on Table2 such that Table1.Id is related to Table2.Table1Id

My end goal here is to, when accessing Table1 via entity framework (db-first, by the way), I can get to my Table2 entity by just Table1.Table2 (should be null or populated).

Right now, I have to do something like Table1.Table2s.First()

What relationship am I missing to have this First() unnecesarry?

Note: Table 1 already exists in our DB and is used. Table 2 is part of a new thing.

Here's my current creation of Table2 leading to my problem

CREATE TABLE Table2(
    Table1Id NUMERIC(18, 0),
    Field2 BIT NOT NULL,    
    Field3 BIT NOT NULL,    


    CONSTRAINT Table2_pk PRIMARY KEY (Table1Id ),

    CONSTRAINT FK_Table1ID
        FOREIGN KEY (Table1Id)
        REFERENCES Table1(Id)
);

Even stranger, everything looks fine in the EDMX

Further, the EDXM file's association checks out with multiplicity 1 and 0..1

EDIT:

Throughout enough fuddling this works. I reverted my model and made the connection from scratch /again/ and it magically works. I'll try to think about why this work and edit if I figure out why.

Upvotes: 0

Views: 564

Answers (1)

Stephen Byrne
Stephen Byrne

Reputation: 7505

Are you using DB Fist or Code First? With DB First, it should just work:

Here is an image of what I get when I use EF DB first: EDMX

And here's an image showing that this setup does correctly give a singleton relationship:

Singleton

Also, if you edit your .edmx file with a text editor, the Association properties should be defined something like this:

<Association Name="FK_Table1ID">
          <End Role="Table1" Type="XOneModel.Store.Table1" Multiplicity="1" />
          <End Role="Table2" Type="XOneModel.Store.Table2" Multiplicity="0..1" />
          <ReferentialConstraint>
            <Principal Role="Table1">
              <PropertyRef Name="id" />
            </Principal>
            <Dependent Role="Table2">
              <PropertyRef Name="Table1Id" />
            </Dependent>
          </ReferentialConstraint>
        </Association>

Can you double-check that?

If you are using Code-First, then you need to make sure you are defining your relationships correctly and that you define the Table2 member in Table1 as an single instance property and not a collection...if you're using CF, can you post your model code?

Upvotes: 1

Related Questions