CODEWITHSUNDEEP
c#sqlentity-frameworkentity-framework-6guid
soomon
soomon

Reputation: 427

Entity Framework foreign key that is not a primary key

I am having problems with my MSSQL database design.

I had 2 tables that looked like this:

CREATE TABLE tenants 
(
    tenantId INT PRIMARY KEY,
    tenantName VARCHAR
)

CREATE TABLE users 
(
    userId INT PRIMARY KEY,
    userName VARCHAR,
    tenantId INT,
    FOREIGN KEY (tenantId) REFERENCES tentants(tenantId)
)

I wanted to switch from only IDs to GUIDs for uniqueness and changed the tables. I have read that GUIDs as primary keys are not recommended for performance reasons. So I kept the IDs:

CREATE TABLE tenants 
(
    tenantId INT PRIMARY KEY,
    tenantGuid UNIQUEIDENTIFIER,
    tenantName VARCHAR
)

CREATE TABLE users 
(
    userId INT PRIMARY KEY,
    userGuid UNIQUEIDENTIFIER,
    userName VARCHAR,
    tenantGuid UNIQUEIDENTIFIER,
    FOREIGN KEY (tenantGuid) REFERENCES tentants(tenantGuid)
)

Now Entity Framework is going wild just dismissing all relationships because the guids are not part of the primary key.

On old posts I have read that this would be not supported. Is it still not supported?

How am I support to solve this?

Thanks a lot!

Also sorry for the bad formatting, the editor doesn't respect my line breaks :/

soomon

Upvotes: 1

Views: 1099

Answers (2)

Suraj KV
Suraj KV

Reputation: 76

If you still want to us the unique identifier, you need to take note on the below two things

Set the tenants.tenantGuid to UNIQUE KEY and also set the users.tenantGuid as the same data type

Modify the tables as below

CREATE TABLE tenants (
    tenantId INT PRIMARY KEY,
    tenantGuid UNIQUEIDENTIFIER UNIQUE,
    tenantName VARCHAR
)

CREATE TABLE users (
    userId INT PRIMARY KEY,
    userGuid UNIQUEIDENTIFIER,
    userName VARCHAR,
    tenantGuid UNIQUEIDENTIFIER,
    FOREIGN KEY (tenantGuid) REFERENCES tenants(tenantGuid)
)

Hope this works for you

Upvotes: 0

Jan Paolo Go
Jan Paolo Go

Reputation: 6522

I wanted to switch from only IDs to GUIDs for uniqueness and changed the tables.

You don't really need to switch to GUIDs to ensure uniqueness.Your IDs will be guaranteed to be unique (whichever type they are, say integer) as long as they are set as your primary keys.

I have read that GUIDs as primary keys are not recommended for performance reasons. So I kept the IDs

tenantGuid uniqueidentifier foreign key to tenants.tenantGuid

Assuming you're talking about performance of 'joins' when using GUID as the key then keeping ID's as primary key will not make a difference since queries will be made on the GUID anyway.

Now entity framework is going wild just dismissing all relationships because the guids are not part of the primary key.

How am I support to solve this?

I believe you're better off not solving this and rather choosing either to go back to your previous database design which uses the integer ID. Or ultimately use GUID as your primary key (removing integer ID in the process).

Upvotes: 1

Related Questions