Using a nullable unique key in Entity Framework

Question

I am developing a C# application with .net version 4.5.2 and an Oracle DB.

The problem I have is that I can't get Entity Framework configured right to the database. The database contains a unique keys over 4 columns, but 2 of them are nullable.

[Key, Column("GEBRUIKER", Order = 0)]
public string User { get; set; }

[Key, Column("EL1", Order = 1)]
public string Element1 { get; set; }

[Key, Column("EL2", Order = 2)]
public short? Element2 { get; set; }

[Key, Column("EL3", Order = 3)]
public short? Element3 { get; set; }

When I try to get the values through this code from the database I get a null reference exception, because element 2 or 3 is empty.

When I remove the Keys from element 2 and 3, I wont get the right data, because when element 1 is the same at 2 rows, the second row will cache element 2 and 3.

Question: How can I handle these nullable unique keys?

Added extra information:

Well, this is a part of the create script of the database:

CREATE TABLE USERS
(
  GEBRUIKER            VARCHAR2(3 BYTE)     NOT NULL,
  EL1                  VARCHAR2(6 BYTE)     NOT NULL,
  EL2                  NUMBER,
  EL3                  NUMBER
)

CREATE UNIQUE INDEX USERS_UK ON USERS
(GEBRUIKER, EL1, EL2, EL3)


ALTER TABLE USERS ADD (
  CONSTRAINT USERS_UK
  UNIQUE (GEBRUIKER, EL1, EL2, EL3)
  USING INDEX USERS_UK
  ENABLE VALIDATE);

It is not possible to make any changes to the structure or data, because there are multiple applications who use this database. Also there are already EL2 and EL3 rows with the value 0.

Example data:

{'USER1','A','A','C'}
{'USER1','A','B','B'}
{'USER1','B','A','C'}

When I do a linq query to select USER1 AND EL1 = A I will get the next result:

{'USER1','A','A','C'}
{'USER1','A','A','C'}

instead of:

{'USER1','A','A','C'} 
{'USER1','A','B','B'}

Don't think you can have nullable PKs in EF. See here: stackoverflow.com/questions/10888087/… — Geoff James
– Geoff James, Commented Jun 2, 2016 at 11:56
So is there a way how i can get the right values with entity framework without editing a database? Is it possible to disable the caching based on the unique key? — R Pelzer
– R Pelzer, Commented Jun 2, 2016 at 12:10
Can you at least make sure that null values are replaced by e.g. 0? (And never again inserted as null). If so, you could fool EF and tell it that the key fields are not nullable. — Gert Arnold
– Gert Arnold, Commented Jun 2, 2016 at 13:58
In my case there are already 0 values, so 0 will be different from NULL. Also there are other applications who use this database so i cannot change the data or structure — R Pelzer
– R Pelzer, Commented Jun 2, 2016 at 14:09
You can't have nullable primary keys in Oracle either. I think what you have here is a heap with a unique index. orafaq.com/wiki/Primary_key — Colin
– Colin, Commented Jun 2, 2016 at 15:23

Bassam Alugili · Accepted Answer · 2016-06-02 14:03:04Z

1

No, you can not do that:

Look to your table creation SQL:

CREATE TABLE [dbo].[Users](
    [GEBRUIKER] [nvarchar](128) NOT NULL,
    [EL1] [nvarchar](128) NOT NULL,
    [EL2] [smallint] NOT NULL,
    [EL3] [smallint] NOT NULL,
 CONSTRAINT [PK_dbo.Users] PRIMARY KEY CLUSTERED 
(
    [GEBRUIKER] ASC,
    [EL1] ASC,
    [EL2] ASC,
    [EL3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

All primary keys are Not Null!!!!

let say you want to hack this key and change it by your self with SQL then you have to send the following script which will changes the key from nun nullable to nullabe!

  usersDatabaseContext.Database.ExecuteSqlCommand(@"
        while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY')) 
        begin 
            declare @sql nvarchar(2000) 
            SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[TableName]  DROP CONSTRAINT [' + CONSTRAINT_NAME + ']') 
            FROM information_schema.table_constraints 
            WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' 
            exec (@sql) 
        end 

        ALTER TABLE Users DROP CONSTRAINT [PK_dbo.Users]

        ALTER TABLE Users ALTER COLUMN EL2 SMALLINT NULL 
        ALTER TABLE Users 
        ADD CONSTRAINT [PK_dbo.Users] PRIMARY KEY CLUSTERED ( [GEBRUIKER] ASC, [EL1] ASC, [EL2] ASC, [EL3] ASC )", 
        TransactionalBehavior.DoNotEnsureTransaction);

        var user1 = new User { UserName = "Bassam", Element1 = "1", Element2 = null, Element3 = 3 };

I have :

- Removed all foreign keys which are related to this table 
- Dropped the primary key
- Changed the **Column E2 to nullable**
- Adding the modified primary key again

This will causes an error in SQL Server / Oracle DB with the following message in case of SQL Server: Cannot define PRIMARY KEY constraint on nullable column in table 'Users'.

Think good about it even let say you can do that what will happend with your primary key:

How can you guarantee the uniqueness of your PRIMARY key? Let say your User entity will creates the following row:

Id1    Id2  Id3  , Id4

"a" , "a" , null , null

You cannot create the same entry again because this row will be exists in the table!

edited Jun 2, 2016 at 14:03

answered Jun 2, 2016 at 13:51

Bassam Alugili

17.1k8 gold badges56 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

R Pelzer Over a year ago

Well, the oracle database is already created a long time ago and the unique key works fine. I even managed to add new rows to table, without any changes to the database. The problem is that he cant handle a read with more then 1 row.

Bassam Alugili Over a year ago

just remove the key from the nullable items and make them required and unique and combine the uniqueness with the primary key this will suffice your requirement stackoverflow.com/questions/18889218/…

Collectives™ on Stack Overflow

Using a nullable unique key in Entity Framework

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related