Some time ago I came across the following problem. After performing a refactoring on a class and extracting associated class, I had to make my classes map to a single table in database. For that purpose I used the usual Association attribute and ended up with a code akin to this:
[Table()]
class Customer
{
private Address address;
[Column(IsPrimaryKey=true, IsDbGenerated=false)]
public String SSN
{
get;
set;
}
[Column]
public String FirstName
{
get;
set;
}
[Column]
public String LastName
{
get;
set;
}
[Association(ThisKey="SSN", Storage="address", OtherKey="SSN")]
public Address Address
{
get
{
return address;
}
set
{
address = value;
}
}
[Table(Name="Customer")]
class Address
{
[Column(IsPrimaryKey = true, IsDbGenerated = false)]
public String SSN
{
get;
set;
}
[Column]
public string Street
{
get;
set;
}
}
static void Main(string[] args)
{
DataContext context = new DataContext("Data Source=XYZ;Initial Catalog=TEST;Integrated Security=SSPI");
Customer customer = new Customer{
SSN = "123",
FirstName = "First",
LastName = "Last",
Address = new Address{
Street = "My Street",
SSN = "123"
}
};
Table customers = context.GetTable();
customers.InsertOnSubmit(customer);
context.SubmitChanges();
}
//DB DDL
CREATE TABLE [DBO].[CUSTOMER](
[SSN] [NVARCHAR](50) NOT NULL,
[FIRSTNAME] [NVARCHAR](50) NULL,
[LASTNAME] [NVARCHAR](50) NULL,
[STREET] [NVARCHAR](50) NULL,
CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED
As you can see, a single Customer has a single Address. Customer with corresponding Address maps to a table Customer. Everything works well, until you try save new instances. You get the following error: Violation of PRIMARY KEY constraint ‘PK_Customer’. Cannot insert duplicate key in object ‘dbo.Customer’. If you try to execute the code and you should be able to reproduce the error.
Solution
In the end, it is all just about method call order.
The solution is the commit the root object first (Customer) in our example, without related object (Address). This way, the instance of related object is created also, with all properties empty.
Second step is to set the properties of related object. Here the important detail is not to create the instance of related object, since it has been already created by linq2sql. Then, you commit changes again and this time the related object is also updated and saved.
It would probably not work if you had to enforce not null constraint on some of the related object properties.
Here is the same example fixed to work without error.
class Program
{
static void Main(string[] args)
{
DataContext context = new DataContext(
"Data Source=XYZ;Initial Catalog=TEST;Integrated Security=SSPI");
Table customers = context.GetTable();
Customer customer = new Customer
{
SSN = "SomeSSN",
FirstName = "First",
LastName = "Last"
};
customers.InsertOnSubmit(customer);
//Save customer first, this will also create the Address
context.SubmitChanges();
//Now Address is not null anymore, so it will be updated
//Linq2SQL will not try to insert new row
customer.Address.Street = "Updated";
context.SubmitChanges();
//get one instance for demo purposes
Customer saved = (from all in customers
select all).First();
Console.WriteLine(saved.Address.Street);
}
}
[Table()]
class Customer
{
private EntityRef
address;
[Column(IsPrimaryKey=true, IsDbGenerated=false)]
public String SSN
{
get;
set;
}
[Column]
public String FirstName
{
get;
set;
}
[Column]
public String LastName
{
get;
set;
}
[Association(ThisKey="SSN", Storage="address")]
public Address Address
{
get
{
return address.Entity;
}
set
{
address.Entity = value;
}
}
}
[Table(Name="Customer")]
class Address
{
[Column(IsPrimaryKey = true, IsDbGenerated = false)]
public String SSN
{
get;
set;
}
[Column]
public string Street
{
get;
set;
}
}
//DB DDL
CREATE TABLE [dbo].[Customer](
[SSN] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Street] [nvarchar](50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
Share and Enjoy:
These icons link to social bookmarking sites where readers can share and discover new web pages.
