Entity Framework set navigation property to null

Question

I have a entity framework database first project. here is a extraction of the model:

public partial class LedProject
{
    public LedProject()
    {
        this.References = new HashSet<LedProjectReference>();
        this.Results = new HashSet<LedProjectResult>();
        this.History = new HashSet<LedProjectHistory>();
    }

    public string Identifier { get; set; }
    public string Name { get; set; }
    public Nullable<System.DateTime> CompletionDate { get; set; }
    public System.DateTime CreationDate { get; set; }
    public System.Guid ProjectId { get; set; }
    public string Comment { get; set; }

    public virtual User ContactUser { get; set; }
    public virtual User CreationUser { get; set; }
    public virtual Customer Customer { get; set; }
    public virtual LedProjectAccounting Accounting { get; set; }
    public virtual LedProjectState State { get; set; }
    public virtual ICollection<LedProjectReference> References { get; set; }
    public virtual ICollection<LedProjectResult> Results { get; set; }
    public virtual User ResponsibleUser { get; set; }
    public virtual ICollection<LedProjectHistory> History { get; set; }
}

public partial class User
{
    public System.Guid UserId { get; set; }
    public string LoginName { get; set; }
    public System.DateTime CreationDate { get; set; }
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public string Email { get; set; }
}

I have a problem with setting the navigation item ResponsibleUser of the class LedProject. When I set the ResponsibleUser to a another user and afterwards save the changes of the DBContext, the changes are stored in the database.

But, when I want to delete the current ResponsibleUser of an LedProject, by setting the navigation property to null. The changes are not stored in the database.

LedProject project = db.LedProject.Find(projectId);
project.Name = string.IsNullOrEmpty(name) ? null : name;
...
project.ResponsibleUser = responsibleUser == null ? null : db.User.Find(responsibleUser.UserId);
...
db.SaveChanges();

Is there any trick for deleting navigation properties?

Are you sure `responsibleUser` is actually null when you're setting `project.ResponsibleUser`? The declaration of that isn't here. Does it clear the navigation if you replace that whole line with `project.ResponsibleUser = null;`? — Bobson, Oct 10 '12 at 21:45
it still doesn't delete the property when i change the line to project.ResponsibleUser = null; In debug mode I see that the property is set to null, but it is not stored by the method SaveChanges() — boindiil, Oct 10 '12 at 21:49
Is the database column nullable? Is the mapping for the ID column set to allow nulls? I've usually seen properties declared as `Nullable` when null is actually a legitimate value for them. But I don't have a great deal of *working* EF practice, so if that's not it, I'm out of ideas. — Bobson, Oct 10 '12 at 21:55
The database column is nullable. It is for shure possible to store a null value in this property, because I can create a new LedProject without a ResponibleUser. — boindiil, Oct 10 '12 at 21:59
Seems a bit odd. What happens if you `project = db.LedProject.Find(projectId);` immediately after the call to `db.SaveChanges();`? Is it still null? — nick_w, Oct 10 '12 at 22:20
When i call `var project2 = db.LedProject.Find(projectId);` direct after `db.SaveChanges();` the new value is **not** assigned. So I debugged the lines in more detail. I figured out if I set a breakpoint **after** `project.ResponsibleUser = responsibleUser == null ? null : db.User.Find(responsibleUser.UserId);` the new value is not assigned. When I create a breakpoint before the line and step over the assigment (F10), the value is set. This behavior is reproduceable! Very strange behavior — boindiil, Oct 10 '12 at 23:07
I have finally solved it! It seems to were a problem with lazy loading of the navigation items. When I change the initial loading to `LedProject project = db.LedProject.Include("ResponsibleUser").Where(p => p.ProjectId == projectId).FirstOrDefault();` it works as designed! — boindiil, Oct 10 '12 at 23:13
So in that case `project.ResponsibleUser` was null due to the missing included. To hazard a guess, I would say that setting that property to null when it (sort of) already was meant that EF assumed nothing had changed, hence you change was not persisted. — nick_w, Oct 10 '12 at 23:19
It seems that the property was loaded when I tried to assign `null`. Because the property was not overridden when the assigment was called once. When I called it a second time directly after (in the **watch** window) the assigment was done correctly — boindiil, Oct 10 '12 at 23:24
@boindiil - Nice find! You should answer the question yourself with that, and then accept it. That way anyone else who has a similar problem can possibly see the answer. — Bobson, Oct 11 '12 at 03:21
That was my plan, but there is a 8h self answering lock, so I'll do it now — boindiil, Oct 11 '12 at 07:41

score 53 · Accepted Answer · answered Oct 11 '12 at 07:42

53

The problem lies in the lazy loading of the navigation property. It seems that the value is first set to null and afterwards loaded from the database. So the desired value (null in my case) is overridden by the currently stored value in the database.

LedProject project = db.LedProject
    .Include("ResponsibleUser")
    .Where(p => p.ProjectId == projectId)
    .FirstOrDefault();

This loads the ResponsibleUser when the Project is loaded. This finally solved my issue!

answered Oct 11 '12 at 07:42

boindiil

5,805
1
28
31

9

You just saved me from going cracy - THANK YOU! – Christoph Fink May 11 '13 at 16:07
2

Is there any way around this without eager loading the nav property? – Michael Hornfeck Mar 04 '14 at 18:14
2

This is incredibly bad. I had to stop using `Find()` and use `Where()` instead. Was punching my own face until I found this, thanks. – kenchilada Feb 20 '15 at 16:14
1

This worked thanks, is this the `official` way? is it documented somewhere? it feels like a bug. – SimonGates Dec 16 '15 at 16:51
@SimonGates I'd like to know this as well, seems weird that their isn't a better way than this and the other answer. – Martin Dawson Jul 15 '16 at 00:23
1

@SimonGates I found the official way for updating navigation's easily and have put an answer below. – Martin Dawson Aug 06 '16 at 14:39

score 6 · Answer 2 · edited May 23 '17 at 11:54

6

Like boindiil said, the problem is with the lazy loading. However, you only have to load the property when you want to null it so the Entity Framework machinery will know it has changed. The code could look like:

responsibleUser = responsibleUser == null ? null : db.User.Find(responsibleUser.UserId);
if (responsibleUser == null)
{
    // load the value to assure setting it to null will cause a change
    var dummy = project.ResponsibleUser; 
}

project.ResponsibleUser = responsibleUser;
...
db.SaveChanges();

I keep thinking there should be a way to use db.ChangeTracker to force a save without the load but I haven't found it yet (and the few things I have tried seemed really hacky).

edited May 23 '17 at 11:54

Community

1
1

answered Dec 03 '14 at 12:46

mheyman

4,211
37
34

1

This worked although both answers are hackish this one works better. You can see that this is the problem too because if you break point on the code and inspect the `ResponsibleUser` it will load it into memory and save changes for null. – Martin Dawson Jul 15 '16 at 00:22
you might want to make use of `_ = project.ResponsibleUser;`. This will stop the compiler complaining about an unused variable. – Tom Stein Apr 07 '20 at 15:41

score 4 · Answer 3 · edited May 23 '17 at 12:02

Figured out the best way to do this without having to eager load the navigation property so you can still use EF's Find() and not have to do a hack.

Use a primitive ID alongside the navigation property where the type is whatever the navigation properties ID type is (usually string for users), e.g:

public partial class LedProject
{
    public string ResponsibleUserId { get; set; }
    public virtual User ResponsibleUser { get; set; }
}

Update the string with the navigation property wherever you create the record and then when you want to remove the relationship just do ledProject.ResponsibleUserId = null.

If you name the id something other than navigation properties name + id at the end then you will need to use annotations or fluent api to map I think.

More info here: In what scenarios do I need foreign keys AND navigation properties in entity framework

score 3 · Answer 4 · edited Sep 30 '16 at 17:20

3

Starting with the Entity Framework 5.0:

db.Entry(project).Reference(p => p.ResponsibleUser).CurrentValue = null;

https://msdn.microsoft.com/en-us/data/jj713564.aspx

edited Sep 30 '16 at 17:20

Eiko

25,601
15
56
71

answered Sep 30 '16 at 16:45

LucasM

431
4
11

score 2 · Answer 5 · answered Oct 16 '18 at 06:39

See https://learn.microsoft.com/en-us/ef/ef6/fundamentals/relationships.

The Creating and modifying relationships section explains what happens in regards to the foreign key property and the navigational property both when assigning and setting to null.

There are some changes in EF5 and forward, but the they key is to define the foreign key property so that the relationship is no longer an independant association (lacks the foreign key property).

score 0 · Answer 6 · answered Sep 12 '16 at 11:49

I ran into this problem and come up with a small "hack" that doesn't break lazy loading.

Simply define the property on your model like this -

    public int? AccountId { get; set; }

    //workaround for entity framework lazy loading problem

    Account account;

    public virtual Account Account
    {
        get
        {
            return account;
        }
        set
        {
            account = value;


            if (value == null)
            {
                AccountId = null;
            }

        }
    }

Now you don't have to eagerly load the navigation property, and setting it to null will work. More importantly, by putting the hack directly inside your entity, you won't have to remember to do explicit checks anywhere else in the codebase.

score 0 · Answer 7 · answered Mar 23 '18 at 11:07

You can set all navigation properties to null like this : ( you need to have your EF Context),here : imported is IEnumerable < YourEntity>

  foreach (var entity in imported)
        {
            foreach (var np in _YourEntityRepository.GetReferenceProperties())
                entity.GetType().GetProperty(np.Name).SetValue(entity, null);
        }

with GetReferenceProperties defined as :

public IEnumerable<NavigationProperty> GetReferenceProperties()
    {

        var oc = ((IObjectContextAdapter)Context).ObjectContext;
        var entityType = oc.MetadataWorkspace.GetItems(DataSpace.OSpace)
                           .OfType<EntityType>()
                           .FirstOrDefault(et => et.Name == typeof(TEntity).Name);
        if (entityType != null)
        {
            foreach (NavigationProperty np in entityType.NavigationProperties
                    .Where(p => p.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One
                             || p.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.ZeroOrOne))
            {
                yield return np;
            }
        }
    }

score -1 · Answer 8 · answered Sep 20 '18 at 14:34

As another workaround, I compiled two methods into a extension method:

public static void SetToNull<TEntity, TProperty>(this TEntity entity, Expression<Func<TEntity, TProperty>> navigationProperty, DbContext context = null)
    where TEntity : class
    where TProperty : class
{
    var pi = GetPropertyInfo(entity, navigationProperty);

    if (context != null)
    {
        //If DB Context is supplied, use Entry/Reference method to null out current value
        context.Entry(entity).Reference(navigationProperty).CurrentValue = null;
    }
    else
    {
        //If no DB Context, then lazy load first
        var prevValue = (TProperty)pi.GetValue(entity);
    }

    pi.SetValue(entity, null);
}

static PropertyInfo GetPropertyInfo<TSource, TProperty>(    TSource source,    Expression<Func<TSource, TProperty>> propertyLambda)
{
    Type type = typeof(TSource);

    MemberExpression member = propertyLambda.Body as MemberExpression;
    if (member == null)
        throw new ArgumentException(string.Format(
            "Expression '{0}' refers to a method, not a property.",
            propertyLambda.ToString()));

    PropertyInfo propInfo = member.Member as PropertyInfo;
    if (propInfo == null)
        throw new ArgumentException(string.Format(
            "Expression '{0}' refers to a field, not a property.",
            propertyLambda.ToString()));

    if (type != propInfo.ReflectedType &&
        !type.IsSubclassOf(propInfo.ReflectedType))
        throw new ArgumentException(string.Format(
            "Expression '{0}' refers to a property that is not from type {1}.",
            propertyLambda.ToString(),
            type));

    return propInfo;
}

This allows you to supply a DbContext if you have one, in which case it will use the most efficient method and set the CurrentValue of the Entry Reference to null.

entity.SetToNull(e => e.ReferenceProperty, dbContext);

If no DBContext is supplied, it will lazy load first.

entity.SetToNull(e => e.ReferenceProperty);

Note, this issue is essentially a duplicate of: Entity Framework will only set related entity property to "null" if I first get the property and Setting a foreign key to null when using entity framework code first

Entity Framework set navigation property to null

8 Answers8

Linked

Related