When calling 'SaveChanges' receiving the exception "Cannot insert the value NULL into column 'XXXXX'"

Question

I have an application that creates entities in offline mode then saves them to the database when it is available. Many entities may be created while the application is offline.

Here is a basic version of the method:

public void SaveEntityToDatabase(Entity entity)            
{
    Context.Entities.AddObject(entity);
    Context.SaveChanges();
}

Since these entities are created offline there is no way to tell if there are existing entities with similar data. For instance, I have a field "SomeText" that must be unique across records. This field is type 'text' in SQL Server so rather than try to directly compare the value I hash it and compare the hash value. If there are no matching records I go ahead and add the new entity otherwise I break out of the method early.

Here is a basic version of the updated method:

public void SaveEntityToDatabase(Entity entity)            
{
    var hashedValue = entity.SomeText.ToSHA1();

    if (Context.Entities.Where(e => e.SomeTextHash == hashedValue).Count() > 0)
        return;

    entity.SomeTextHash = hashedValue;

    Context.Entities.AddObject(entity);
    Context.SaveChanges();
}

Unfortunately doing the existence check causes my app to throw the exception "Cannot insert the value NULL into column 'SomeText'".

Commenting out the check fixes the problem but doesn't allow for me to check existing records.

//if (Context.Entities.Where(e => e.SomeTextHash == hashedValue).Count() > 0)
    //return;

If I insert a breakpoint at SaveChanges and inspect 'entity' if is fully populated. It seems that the existence check is invalidating 'entity' in the object graph but I am not familiar enough with the internal workings of Entity Framework to be sure.

What is causing the "Cannot insert the value NULL into column 'SomeText'" exception and how can I avoid it?

Answer 1

Why not just create a unique index on the SomeTextHash column and if an "cannot insert duplicate blah blah" type exception is thrown, swallow it?