5

I have a question about how to realize a Table relationship in Microsoft SQL-Server 2012.

I have a Table (MyTable) which should hold a Parent/Child structure. One Parent may have one Child and one Child only has one Parent. This is a classical one-to-one relation. The topmost entry obviously may not have a parent. (This is modelled via a nullable property)

When I try to model this in MS-SQL I receive a table as follows.

Generated From EntityFramework

Here is my Code:

CREATE TABLE [dbo].[MyTable](
    [Id] [uniqueidentifier] PRIMARY KEY NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [ParentObjectId] [uniqueidentifier] NULL
)

GO

ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_ParentObject] FOREIGN KEY([ParentObjectId])
REFERENCES [dbo].[MyTable] ([Id])
GO

ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [Unique_ParentId] UNIQUE(ParentObjectId)
GO

ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_ParentObject]
GO

The UNIQE Constraint should assure that there is at most one parent for one child.

BUT Entity Framework won't let me change the properties to be a one-to-one relation. It always generates a one-to-many relation.

EDIT: The code is DB-First.

Do you have any idea on how to model this properly in MS-SQL and EntityFramework?

sternze
  • 1,524
  • 2
  • 9
  • 15
  • Can you show you EF definition/entities? – Christoph Fink Jun 17 '14 at 11:10
  • By definition, do you mean the generated code, or the visual representation in VisualStudio including the properties (.edmx-File)? – sternze Jun 17 '14 at 11:13
  • 1
    Oh - DB-first. I assumed Code-First. Sorry can't help with DB-first - I am allergic agains *.edmx files... – Christoph Fink Jun 17 '14 at 11:16
  • This is true, but it would be much handier in code to select .Child instead of .Childs.FirstOrDefault() and check its value against null, ... If there is no way around I will do it like this. :) --> was entered after Tanner suggested to handle this in code and that there is not need for EF to map it. (comment deleted?) – sternze Jun 17 '14 at 11:22
  • Your intention is interesting. But I prefer you to use CREATE/UPDATE/DELETE store procedure for the table, rather than making restriction in table schema. It will also provide you chance to alter table data's on near future. – Dipon Roy Jul 17 '14 at 18:56
  • Does this problem still exist in EF6 or even in EF One? I asked similar question here : http://stackoverflow.com/questions/39881282/one-to-one-without-principal-and-dependent-in-ef?noredirect=1#comment67049660_39881282. – Iskander Raimbaev Oct 05 '16 at 18:44

2 Answers2

4

You can't do what you want in EF, unfortunately.

EF only supports one to one mappings when there is a shared primary key (ie both tables have to have the same primary key, and one has to be a foreign key of the other). You can't do this when using a self-referencing table for obvious reasons.

The reason EF doesn't support this is that EF doesn't support unique constraints, therefore it has no way to ensure that the 1:1 mapping is constrained. This could change because EF does now support unique indexes, however this hasn't changed the requirement for shared primary keys in one to one's.

The best you can do is create a one to many.

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
  • Good informative answer, thanks.. I ended up with a one to many relationship as you suggested.. – sternze Jul 24 '14 at 20:22
  • +1 This is the only real answer I've seen to this problem. EF keeps disappointing me... – Daniel Conde Marin Sep 03 '14 at 18:42
  • @Daniel - EF is now community maintained, although it's still managed by MS Employees. If this feature is important to you, I suggest going to the codeplex site and participating. I believe there is an open issue on this... – Erik Funkenbusch Sep 03 '14 at 22:04
-2

You should create the parent table first and then relate the child table to that so something like this

Parent Table/ Cars/ CarID/ Color/

Child Table/ Make/ MakeID/ Make/ CarID/

if you relate it that way you will achieve the correct relationship between a parent and child

mathis1337
  • 1,426
  • 8
  • 13
  • I know what you mean, but this was not my intention.. I was able to model the tables in DB sie, but not EF wasn't able to display it correctly. – sternze Jul 24 '14 at 20:20