Create one to one relationship by using entity framework database first

ef-code-first ef-database-first entity-framework one-to-one

Question

By coding EF Code Initial as follows, we can establish a one-to-one relationship:

public class User
{
public int UserID {get;set;}
public string Name {get;set;}
public int UserDetailID {get;set;}

public UserDetail Detail {get;set;}
}

public class UserDetail
{
public int UserDetailID {get;set;}
public string Address {get;set:}
public int UserID {get;set;}

public User User {get;set;}

}

However, I ran into issues in visual studio 2012 when I attempted to establish the same relationship using the EF Database initially formula. This is my code:

CREATE TABLE [dbo].[Users] (
[UserID]                 UNIQUEIDENTIFIER CONSTRAINT [DF_Users_UserID] DEFAULT (newid()) NOT NULL,
[UserDetailID]                 UNIQUEIDENTIFIER NOT NULL,
[Name]                      NVARCHAR (50)    NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([UserID] ASC),
CONSTRAINT [FK_Users_UserDetails] FOREIGN KEY ([UserDetailID]) REFERENCES [UserDetails]([UserDetailID])
);

CREATE TABLE [dbo].UserDetails] (
[UserDetailID]     UNIQUEIDENTIFIER CONSTRAINT [DF_UserDetails_UserDetailID] DEFAULT (newid()) NOT NULL,
[UserID]           UNIQUEIDENTIFIER NOT NULL,
[Address]             NVARCHAR(100) NOT NULL,
CONSTRAINT [PK_UserDetails] PRIMARY KEY CLUSTERED ([UserDetailID] ASC),
CONSTRAINT [FK_UserDetails_Users] FOREIGN KEY ([UserID]) REFERENCES [dbo].[Users] ([UserID])

The error message looks like this:

"Error  2   SQL01767: Foreign key 'FK_Users_UserDetails' references invalid table 'UserDetails'.    

I believe the "UserDetailID" foreign key hasn't yet been created when this problem occurs when it tries to reference it. But I'm not sure how to repair it, and I'm not even sure this is the right approach. I know working one-on-one with EF is challenging, and some people even claim it's impossible. Can anyone make any suggestions for me? I'm grateful.

Just to be clear, I'm trying to create/update my.edmx file from the database on SQL server after designing it in a Visual Studio 2012 database project and publishing it to the server. I'm not sure how to establish a one-to-one connection that the EF will correctly identify and use to generate the appropriate classes in the.edmx file.

1
4
6/24/2013 8:44:22 PM

Accepted Answer

It's not difficult to establish a 1:1 relationship, but I don't get why you would want one in this situation. It's not an usual demand, either. You are conversing with the incorrect people if this is what they are saying.

However, as you seem to be doing, utilizing SQL queries has nothing to do with EF; rather, you are just interacting with the database directly. In the first CREATE, you are attempting to add the constraint, but you haven't yet built the other table. as you said in your inquiry.

I believe you must first construct both tables before adding the constraint using ALTER TABLE.

Additionally, there are many questions concerning 1:1 on SO, so I advise you to search there.

EDIT: So you want to use a database project (I only have Visual Studio Express, so I don't have those) to create a "1:1" relationship using SQL, and then you want to add an entity data model to another (likely different) project so that it references the database and creates a 1:1 relationship automatically.

Unfortunately, that is a whole different story. When I mentioned the option of creating 1:1, I was only referring to EF and not databases as a whole. In reality, creating 1:1 in SQL is really difficult or impossible, as you claimed. I believe it makes sense that in order to insert into a 1:1 relationship, you would need to do so simultaneously in both tables or quickly play around with constraint disabling when inserting rows.

There are generally a few options available.

  1. Avoid dividing the tables needlessly. The only reason to split in a genuine 1:1 situation is for performance reasons (e.g., partioning), which I would avoid in this instance.

  2. As shown in display here, map many tables to a single object.

  3. In the application, establish a 1:0:1 relationship and impose your own requirements.

You can use the following SQL to construct a relationship in either option 2 or option 3 that utilizes the same PK on the second table as the relationship's FK.

CREATE TABLE [dbo].[Users] (
[UserID] UNIQUEIDENTIFIER CONSTRAINT [DF_Users_UserID] DEFAULT (newid()) NOT NULL,
[Name] NVARCHAR (50)    NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([UserID] ASC),
);

CREATE TABLE [dbo].[UserDetails] (
[UserID]           UNIQUEIDENTIFIER NOT NULL,
[Address]             NVARCHAR(100) NOT NULL,
CONSTRAINT [PK_UserDetails] PRIMARY KEY CLUSTERED ([UserID] ASC),
CONSTRAINT [FK_UserDetails_Users] FOREIGN KEY ([UserID]) REFERENCES [dbo].[Users] ([UserID]) ON DELETE CASCADE 
);

Where possible, I advise you to use store-generated identity as well.

7
6/25/2013 9:24:11 AM

Popular Answer

Make UserID the primary key and a foreign key to the UserID column of the User table, and simply remove UserDetailID from the UserDetail table.

This is the proper way to create 1:1 connections in a database, and EF knows it. It uses a database-first approach to map the entities correctly.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow