Question 20055 in Mysql Question posted in
The official MySQL documentation can be found here.

Update database columns with null c# entity framework – Mysql

I have a problem in my program where if I try to update a table and replace the value of a foreign key column, which is not null, with the value null, that it doesn‘t update the table.
Here is my code (I‘m using the entity framework):

public void UpdateDatabase(Car car)
        {
            using (var context = new DBContext())
            {
                Car? existingCar = context.Cars.Find(car.Id);

                existingCar.TopSpeed = car.TopSpeed;
                existingCar.BreakingForce = car.BreakingForce;
                existingCar.Acceleration = car.Acceleration;
                existingCar.nitroPower = car.nitroPower;
                existingCar.Hp = car.Hp;
                existingCar.Brand = car.Brand;
                existingCar.Model = car.Model;
                existingCar.Color = car.Color;
                existingCar.TintedWindows = car.TintedWindows;
                existingCar.Weight = car.Weight;
                existingCar.Image = car.Image;
                existingCar.Price = car.Price;
                existingCar.Spoiler = car.Spoiler;
                existingCar.Rims = car.Rims;
                existingCar.Nitro = car.Nitro;
                existingCar.Engine = car.Engine;
                existingCar.Break = car.Break;
                existingCar.Exhaust = car.Exhaust;
                existingCar.Tyres = car.Tyres

                context.SaveChanges();
            }
        }

I debugged the code and it works if the value in the table is already null, then it does execute the query but if the column has a value in the Database, it can‘t be overwritten with null. It also doesn‘t throw an exception nor does the context.SaveChanges() reveal anything.

Does someone maybe have a solution?

Tags:

2

Answers


  1. 0

    What does the class where you are defining the columns look like?

    For example, if it has the required tag:

    [Required]
public int TopSpeed { get; set; }

    Then it wouldn’t be able to set it to null. You’d have to change it to:

    public int TopSpeed { get; set; }

    Also you can check your database itself (SQLserver or whatever you are using) and see if the NOT NULL constraint is there.

    Login or Signup to reply.
  2. 0

    You mention setting a FK to null but I don’t see any FK in your example.

    If you have a FK column and a navigation property, you want to be sure to update the navigation property, not just the FK. This means that you need to eager load the navigation property to dis-associate the reference. The FK and navigation property should also be marked as Null-able.

    For example, if I have a Car and a Driver that I want to remove:

    public class Car
    {
    //…

    public int? DriverId { get; set; }
[ForeignKey(nameof(DriverId))]
public virtual Driver? Driver { get; set; }

    }

    You might expect this to work:

    var car = _context.Cars
    .Single(x => x.Id == carId);
car.DriverId = null;
_context.SaveChanges();

    And it can work, "sometimes". It will work only so long as the DbContext didn’t happen to be also tracking that car’s Driver. When you load an entity and don’t explicitly eager load a related entity, the DbContext will still scan through all tracked entities and associate related entities it happens to know about. So if an earlier operation happened to load and track the Driver of that Car, the Driver navigation property would be set. Otherwise, it would be #null. This leads to unexpected behaviour because if we do the following:

    var car = _context.Cars
    .Include(x => x.Driver)
    .Single(x => x.Id == carId);
car.DriverId = null;
_context.SaveChanges();

    … to ensure that the Driver navigation reference was loaded, that code would not work. The reason is that when we save the changes, our Car still has the Driver reference set and FK change is ignored. Instead, we should do the following:

    var car = _context.Cars
    .Include(x => x.Driver)
    .Single(x => x.Id == carId);
car.Driver = null;
_context.SaveChanges();

    Now you would have the expected behaviour, even though we don’t explicitly set the FK to #null. (We could, but it isn’t necessary) Now there is still a gotcha here… You might be thinking, "Ok, we just set the navigation properties not the FK and everything is good." Yes, but only so long as the navigation property was loaded. Take the original example without the eager load:

    var car = _context.Cars
    .Single(x => x.Id == carId);
car.Driver = null;
_context.SaveChanges();

    This will work "sometimes" and fail "sometimes" If the Driver happened to be tracked and populated by the DbContext, the above code would work. (where setting the FK to null would not work) If the Driver reference was not tracked/populated, then the above code would not work. In that case the Driver reference would already be #null, so nothing changes if we set it to #null so nothing is added to the change tracker to know to perform an update when we save. Setting both to #null would work in both scenarios:

    var car = _context.Cars
    .Single(x => x.Id == carId);
car.Driver = null;
car.DriverId = null;
_context.SaveChanges();

    However, this is extremely prone to bugs as sooner or later you will forget to update one or the other.

    My advice when it comes to navigation properties and FK properties is only declare one or the other in an entity. If using navigation properties, use a shadow property for the FK. This also means if you want to make any changes to relationships, always eager load the relationship. Alternatively if you don’t need a navigation property and want raw speed/simplicity (I.e. FKs relationships that can be represented by Enumerations rather than entity classes) then just declare the FK property.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search