How to set an FK column value without retrieve it

Question

Hi, i want to set a new value to my entity objects FK column, but i cant find the property to set. I dont want to get record from db.

I have a db like that

Db Tables:

Concept                ConceptType
-Id (PK)               -Id(PK)
-Name                  -Name
-ConceptTypeId(FK) (ALLOW NULL)

Code:

Concept conceptToUpdate = new Concept() { Id = 1 };
ConceptType conceptType = new ConceptType() { Id = 5 };
db.AttachTo("Concept", conceptToUpdate);
db.AttachTo("ConceptType", conceptType);
conceptToUpdate.ConceptType = conceptType;
db.SaveChanges();

This code is working if ConceptTypeId(FK) column is NULL before. If it is not NULL it gives exception. I trace the sql query, the problem is on sql query because it is checking that old value is NULL :S

SQL QUERY: (from SQL Profiler)

exec sp_executesql N'update [dbo].[Concept]
set [ConceptTypeId] = @0
where (([Id] = @1) and [ConceptTypeId] is null)
',N'@0 int,@1 int',@0=5,@1=1

Answer 1

The reason it fails is that in 3.5 SP1 is that for relationships the old FK value is part of the concurrency check, as you found via SQL profiler.

What you need is something like this:

Concept conceptToUpdate = new Concept() {
      Id = 1 , 
      ConceptType = new ConceptType {Id = OriginalFKValue}
};
ConceptType conceptType = new ConceptType() { Id = 5 };
db.AttachTo("Concept", conceptToUpdate); 
db.AttachTo("ConceptType", conceptType);
conceptToUpdate.ConceptType = conceptType;
db.SaveChanges();

This means you need to know not just the Id of the thing you want to update, but also it's original FK values, which is of course a real pain.

Hence a new feature in EF 4.0 called FK Associations. With FK Associations the original value of the FK is not part of the concurrency check.

Hope this helps

Alex