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: http://stackoverflow.com/questions/10888087/entity-framework-field-of-composite-key-cannot-be-nullable — Geoff James, Jun 02 '16 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, Jun 02 '16 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, Jun 02 '16 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, Jun 02 '16 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. http://www.orafaq.com/wiki/Primary_key — Colin, Jun 02 '16 at 15:23
I can only think of very dirty hacks to intercept the `null` values from the DbDataReader and replace them by ... something. But you shouldn't want that. I think you have to look for another way than EF to read the data. — Gert Arnold, Jun 02 '16 at 18:42
Well, it looks like the only possible solution is to do a SQLQuery on the dbcontext then. — R Pelzer, Jun 03 '16 at 09:05

Bassam Alugili · Accepted Answer · 2016-06-02T14:03:04.257

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!

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. — R Pelzer, Jun 02 '16 at 14:17
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 http://stackoverflow.com/questions/18889218/unique-key-constraints-for-multiple-columns-in-entity-framework — Bassam Alugili, Jun 02 '16 at 14:22

Using a nullable unique key in Entity Framework

1 Answers1