CODEWITHSUNDEEP
c#entity-framework
Andre Figueiredo
Andre Figueiredo

Reputation: 13425

EF Attach won't update database fields to null

I'm facing a problem that when I set a field any value than empty, EF will update the database.

But, when I clear the field (input[text] empty), the matching field in DB won't update to NULL.

Here are the codes:

C#

var entidade = new Plantas { CodPlanta = vm.CodPlanta };
db.Plantas.Attach(entidade);

entidade.CodEstado = vm.CodEstado;
entidade.NomePlanta = vm.NomePlanta;
entidade.CEP = vm.CEP;
entidade.Telefone = vm.Telefone;
entidade.Fax = vm.Fax;

db.SaveChanges();

SQL generated by EF

exec sp_executesql N'update [dbo].[Plantas]
set [CodEstado] = @0, [NomePlanta] = @1, [CEP] = @3
where ([CodPlanta] = @4)
',N'@0 int,@1 varchar(200),@3 char(8),@4 int',
@0=26,
@1='Apiaí',
@2='Integrada',
@3='18320000',
@4=373
go

See that when vm.Telefone and vm.Fax, these fields won't show in UPDATE query. But if a put some value, they will.

Even if the fields have some value before.

If a put a breakpoint at db.Savechanges(), it will show entidade.Telefone = null. But it won't go to the generated query.

How can I force Entity Framework to set fields to null?

Upvotes: 0

Views: 2897

Answers (2)

Andre Figueiredo
Andre Figueiredo

Reputation: 13425

I found that setting attached instance of entity to "Modified" works:

db.Entry(entidade) = EntityState.Modified;
db.SaveChanges();

Upvotes: 2

Kris Vandermotten
Kris Vandermotten

Reputation: 10201

The reason is that EF will only save modified fields. So if the field was null before you attach the object, and you set it to null again, EF will not update the database.

The solution is pretty simple: set the field to something other than null before you attach the object, than modify it to null before you save it.

One caveat: if you have optimistic concurrency control on the field, you may have to read the record from the database instead of attaching an empty object. Alternatively, use one column for optimistic concurrency control, such as a version number or SQL Server timestamp column.

For example, say you have this problem with the CEP column. Then do:

var entidade = new Plantas { CodPlanta = vm.CodPlanta };

entidade.CEP = "fake old value";

db.Plantas.Attach(entidade);

entidade.CEP = vm.CEP;

db.SaveChanges();

Upvotes: 2

Related Questions