Updating primary key in DLinq

[Andrus]

I need to update natural primary key like
Territories.TerritoryID column in northwind database.

In pure ADO .NET this is easy. I can use ExecScalar to run UPDATE command
on primary key.

Server referential integrity causes cascade updates automatically.

I read from MSDN that Linq-SQL does not allow to update object ids.

Any idea how to perform this in DLinq ?

Andrus.

 

[Frans Bouma [C# MVP]]

I need to update natural primary key like
Territories.TerritoryID column in northwind database.

In pure ADO .NET this is easy. I can use ExecScalar to run UPDATE
command on primary key.

Server referential integrity causes cascade updates automatically.

I read from MSDN that Linq-SQL does not allow to update object ids.

Any idea how to perform this in DLinq ?

Updating a PK is actually semantically not possible. An entity
instance (the table row) is identified by a PK value (1 or more
fields). If that PK value changes, the entity is actually another
instance. So if you change the PK, you do what you can't do: change the
identity of an instance. It comes down to ADD the entity with the NEW
PK to the table and REMOVE the original one with the OLD PK value.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Andrus]

Updating a PK is actually semantically not possible. An entity

instance (the table row) is identified by a PK value (1 or more
fields). If that PK value changes, the entity is actually another
instance. So if you change the PK, you do what you can't do: change the
identity of an instance. It comes down to ADD the entity with the NEW
PK to the table and REMOVE the original one with the OLD PK value.

I cannot remove the original primary key value since may referenced from
other tables by
IMMEDIATE referential integrity constaints like ON DELETE CASCADE or ON
DELETE SET NULL

I'm looking a way to implement pk update in DLinq. This is a *required*
feature in databases containing natural pks.

The operation must be like:

1. start transaction
2. replace all primary key referenced in memory with new reference
3. update primary key reference in database.

This simply changes identity identifier.
This is like changing variable name during refactoring or object memory
addres by .NET compacting garbage collector.

Without natural primary key support DLinq is not complete solution.
In this case it is not reasonable replacement to ADO .NET and has very
limited usage in natural key databases.

Andrus.

 

[Frans Bouma [C# MVP]]

I cannot remove the original primary key value since may referenced
from other tables by IMMEDIATE referential integrity constaints like
ON DELETE CASCADE or ON DELETE SET NULL

I'm looking a way to implement pk update in DLinq. This is a required
feature in databases containing natural pks.

no, it's not. If you require updating attributes which happen to be
PK's, you've picked the wrong attribute to be the PK, it's that simple.

The operation must be like:

1. start transaction
2. replace all primary key referenced in memory with new reference
3. update primary key reference in database.

This simply changes identity identifier.
This is like changing variable name during refactoring or object
memory addres by .NET compacting garbage collector.

Without natural primary key support DLinq is not complete solution.
In this case it is not reasonable replacement to ADO .NET and has
very limited usage in natural key databases.

What's a good PK is an old debate. However picking something that
changes isn't a good PK, because it can't identify the entity. It's
basic database theory.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Andrus]

Updating a PK is actually semantically not possible.

I looked into MS Linq-SQL sample code.
NorthwindMapping.designer.cs implements primary key property updating logic
for all primary key columns:

public string TerritoryID
{
....
set
{
if ((this._TerritoryID != value))
{
this.OnTerritoryIDChanging(value);
this.SendPropertyChanging();
this._TerritoryID = value;
this.SendPropertyChanged("TerritoryID");
this.OnTerritoryIDChanged();
}
}
}

I cannot try Linq-SQL.

Can anybody confirm that Linq-SQL updates primary key in database if object
primary key property is changed ?

Andrus.