9

I have the following tables in SQL Server database enter image description here

Which has a 1-1 association table (FooBar) which has unique indexes on corresponding FooId, BarId, and the primary key is (FooId, BarId).

To be clear FooBar does not allow any FooId (due to unique constraint) to be in the table more than once neither can any BarId (due to unique constraint) be in the table more than once. This is what makes it a 1-1 associative table.

I want to have this association table instead of 1-1 relationship between Foo and Bar because in my real world scenario, Bar will have other relationships to different unrelated tables and I will want similar association tables (as opposed to adding new FK columns to Bar for each new table)

Which I then bring these tables into my EDMX designer. The relationship is brought in as a Many to Many instead of One to One. enter image description here

Which of course isn't what I want. I can manually change the model to a 1-1 relationship. enter image description here

But then I get an error (in the designer). enter image description here

Is this a bug or is it not possible to create a 1-1 association in this manner in EF?

Jim
  • 4,910
  • 4
  • 32
  • 50
  • 1
    It appears to be from the SQL Server Database Diagram. The FooBar table has unique constraints on FooId and BarId as well as a PK unique key on the two together. It is a 1-1 association. – Jim Feb 17 '15 at 16:52
  • FooBar is a many-to-many association table. EF is not your problem here. – Augusto Barreto Feb 17 '15 at 16:54
  • 1
    @AugustoBarretom please read my SQL setup more carefully, I have unique constraints on FooId and BarId in FooBar. This is a 1-1 associative table. – Jim Feb 17 '15 at 17:01
  • 1
    @Jim right, now I understand. I'm sorry, but I can't help you with that. Maybe you could try creating a FooBar class and manually map FooBar table to it in the edmx, but I not sure. – Augusto Barreto Feb 17 '15 at 17:06
  • 1
    Sorry for the misunderstanding Jim, here's another SO question that seems related, maybe it will be of some help? http://stackoverflow.com/questions/27609023/how-to-get-ef6-to-honor-unique-constraint-on-fk-in-association-relationship-mu – Kritner Feb 17 '15 at 17:14

2 Answers2

8

It is a "bug" with the entire EF design: Entity Framework 4-6.1x Only Honors Multiplicity on Primary Keys.

Thus even though we know (and the RA models) that it is a 1-1 relationship due to a Candidate Key Constraint, EF does not and will not like it. Tough luck.

The "solutions" include:

  1. Changing the model to something EF understands (EF understands Code First, not RA). Granted this may indicate an "issue" with the selected RA model, but such is orthogonal to the question ..

  2. Live with the incorrectly generated multiplicity rules and "use with care"; the dirty work can be wrapped, but has to be added manually outside of the auto-generated model.

  3. .. Hmm, others?

Shameless plug to unresolved questions deal with the same core lack-of feature:

Community
  • 1
  • 1
user2864740
  • 60,010
  • 15
  • 145
  • 220
  • Is there a way to override DbContext.OnModelCreating to force the relationship via code to get around this? – Jim Feb 17 '15 at 17:24
  • @Jim I couldn't help there: I selected option #2 after many unsavory thoughts towards a certain.. but I don't think so with Model / Schema First because the error is from the design-time generator itself. (Unless talking about removing it entirely from the *DL's, perhaps..) – user2864740 Feb 17 '15 at 17:35
  • I had thought about doing that, remove the relationship in the designer and added via code. Haven't tried anything like that before, mixing models. But I've been known to hack my around bugs like this before :-) Might give it a shot. – Jim Feb 17 '15 at 17:43
1

The relationship you've shown in your first graphic is not a 1to1 relationship as far as EF is concerned.

It's a many to many relationship between Foo and Bar

Think about it this way:

Possible combinations with the following Foo and Bar values

Foo
1
2
3

Bar
1
2
3

FooBar
1, 1
1, 2
1, 3
2, 1
2, 2
2, 3
3, 1
3, 2
3, 3

Your FooBar table is a composite key, meaning it's a combination of the Foo and Bar values making up the key - not a 1 to 1 relationship

to define a 1 to 1 relationship between Foo and Bar, your schema should look something more like this:

Foo
FooId PK

Bar
FooId PK FK to Foo.FooId

the FooBar table is not needed for a 1to1 relationship between foo and bar.

As you stated in your question/comments - yes you put a unique constraint on the individual parts of your composite key, but EF doesn't take into account unique constraints for your model when determining a relationship. If you want a 1to1 relationship, you should create a 1to1 model, rather than mocking a 1to1 relationship via unique constraints.

Kritner
  • 13,557
  • 10
  • 46
  • 72
  • 1
    This isn't correct because in my SQL table I have unique constraints on FooId and BarId as well as the PK (FooId, BarId). There can only be one entry for any Foo-Bar combo. i.e. FooId 1 can never be in the table more than once, BarId 1 same. So your FooBar representation is not what I have setup. – Jim Feb 17 '15 at 16:55
  • The unique constraints are on what table? `Foo` and `Bar`? Or `FooBar`? – Kritner Feb 17 '15 at 16:56
  • I'm not sure that EntityFramework would take into account your unique constraints. Why can't you just model your tables in an actual 1to1 relationship instead of mocking a 1to1 via unique constraints? – Kritner Feb 17 '15 at 17:02
  • 1
    I explain that in the question. I don't want to add columns to Bar as I add new relationships. Want it to be agnostic of other relationships, hence using associative tables. – Jim Feb 17 '15 at 17:07
  • I didn't downvote it. I hate downvoting. Don't think it is constructive ;-) – Jim Feb 17 '15 at 17:46