Are nullable foreign keys allowed in Entity Framework 4?

Question

I have a problem updating a foreign key in an Entity Framework entity. I am using self tracking entities and have an entity with some relations where the foreign key is also present as a property (one of the new features of EF4). The key (an integer) is marked as Nullable and Concurrency Mode fixed.

Specifically I have an Alarm entity with a many to 0..1 relationship to a confirming user. (a user may confirm several alarms, but an alarm can be confirmed by only zero or one users).

The entity definitions (simplified):

Alarm properties
Id      Int32   non-nullable  identity entity key
UserId  Int32   nullable concurrency mode fixed
Alarm navigation properties
User    0..1 multiplicity

User properties
Id      Int32   non-nullable  identity entity key
Name    String  non-nullable

In my self tracking entity the confirming user id is auto-generated as a Nullable just as expected, however if I assign a user to an already persistent alarm and run ApplyChanges, the self tracking context extension tries to set the original value (null) in the EF context (In SetValue in the context extensions), but silently skips that because the ClrEquivalentType of the EdmType is a non-nullable Int32.

Auto-generated extension code:

    private static void SetValue(this OriginalValueRecord record, EdmProperty edmProperty, object value)
    {
        if (value == null)
        {
            Type entityClrType = ((PrimitiveType)edmProperty.TypeUsage.EdmType).ClrEquivalentType;
            if (entityClrType.IsValueType &&
                !(entityClrType.IsGenericType && typeof(Nullable<>) == entityClrType.GetGenericTypeDefinition()))
            {
                // Skip setting null original values on non-nullable CLR types because the ObjectStateEntry won't allow this
                return;
            }
        }

        int ordinal = record.GetOrdinal(edmProperty.Name);
        record.SetValue(ordinal, value);
    }

When the EF later tries to update my alarm I get an OptimisticConcurrencyException because it constructs a WHERE clause in the UPDATE statement where it uses 0 (zero) as the original user foreign key value instead of the correct "is null". (The WHERE clause is part of the EF optimistic concurrency mechanism, where the original values of the properties marked with "fixed" concurrency mode are checked agains the properties in the database).

Are nullable foreign keys / primitive types not fully supported in self tracking entities for EF? If not, am I forced to use dummy entities instead of null or are there other workarounds?

Update I have tried to reproduce the problem without STE, but plain EF seems to handle optimistic concurrency well for nullable foreign keys, so this is an STE problem, not an EF problem. There is numerous issues with self tracking entities, so it is not surprising that there is a glitch here. If I find a workaround that can be implemented in the STE T4 script I will post it here.

Pull

Answer 1

Bill Huth posted a working patch at MSDN.

Pull

Answer 2

Yes, nullable foreign keys are certainly allowed. We use them all over the place. You don't show your database or model, so it's difficult to be certain what the problem could be, but it sounds as though the Entity Framework cannot figure out the primary key of one of the tables involved. Perhaps you don't have one, maybe because one of them is a view? I'm guessing here, because you don't give much information about what you're doing.

Corby

I am pretty sure my primary keys are OK. The problem occurs when I change the nullable referred entity to a new entity. This causes the original entity reference (null) and the key (null) to be stored in the original values collection. When ApplyChanges is called the original values are attempted moved from the entities change tracker to the EF context, but since the EF context defines the type of the key as an Int32 and not Nullable<Int32> as the Self Tracking entity, it cannot be assigned null. – Pull

Nullable int columns should be in your CSDL as int?, not int. Try it with a new (previously un-mapped) table; you'll see. Have you changed the nullability since you created the model? – Corby

I tried to create a new entity "FooEntity" with no primary key and a nullable Int32 called Foo. This is what is generated in the CSDL: <EntityType Name="FooEntity" > <Property Type="Int32" Name="Foo" Nullable="true" /> </EntityType> No Int32? here. Changing Int32 to Int32? causes an error "The value 'Int32?' is invalid according to its datatype 'schemas.microsoft.com/ado/2008/09/edm:TPropertyType' – Pull

BTW Are you using self tracking entities with concurrency checking? The problem occurs when the self tracking entities context extension populates the context with a nullable integer marked with fixed concurrency mode. If you are not using a concurrency model or self tracking entities you probably don't experience the problem. – Pull

Type="Int32" Nullable="true" is what I meant whent I wrote "int?". This shouls be codegened as int?. Fixed concurrency is probably not right for a random FK; we use a TIMESTAMP` field for this. – Corby

I guess I could use the standard optimistic concurrency pattern with TIMESTAMP fields, but I see no logical reason why EF should not support a concurrency model for foreign keys. EF can handle nullable foreign keys fine. What it fails to handle is the scenario where the STE wants to store an updated null key as the original value into the EF context. It makes no sense that EF doesn't support that. To me it looks like a bug or design flaw at best, but I was hoping for a work around. Other people at the MSDN forums are reporting the same problem, but no answers from MS. – Pull

It's possible that it's a bug. But if the nullable column is mapped as int rather than int? (in your codegened entity) then the bug is in your CSDL or codegen, not the EF. If you see the same behavior with int? then it would look like an EF bug. – Corby

The nullable Int32 is mapped in STE as Nullable<Int32> as expected. STE handles the nullable as expected, but when assigning a null value with a non-null value, the null value gets stored in the original values collection. However STE fails to inject the null value as the original value into the EF context, which makes EF use zero, not null, in the optimistic concurrency check. – Pull

Have you looked at the code generated by the STE template? If the bug was there (I haven't checked; I don't use STEs) it would be something you could fix yourself by changing the T4. – Corby

Yes, the SetValue method above is from the STE template. I don't know if the problem is fixable in the STE code since I don't know how to tell the EF context that my changed foreign key was original a null value and it should use "IS NULL" not " = 0" in the optimistic concurrency where clause when updating. To me it looks like EF has an issue with optimistic concurrency when using nullable foreign keys. My question title should probably have been "are nullable foreign keys with fixed mode optimistic concurrency allowed in EF?". – Pull

Looks to me like the issue is this code: typeof(Nullable<>) == entityClrType.GetGenericTypeDefinition(). If your entity has int?, this code should allow the SetValue to function, because it should detect that the type is nullable and allow it. If I were in your shoes, I'd debug through this and see where it's failing. – Corby

Yes the problem is that entityClrType.GetGenericTypeDefinition() returns an Int32 type not a nullable. This is in conflict with the STE wrapping, that correctly defines the foreign key as Nullable<Int32>. – Pull

As I wrote in my resent edit plain EF handles optimistic concurrency as expected. This is an STE issue that hopefully can be solved by patching the T4 code. – Pull

Yes, I agree; it seems like the bug is in the template. – Corby