bovlk
asked on
LINQ to SQL 1:N relationship problem
Hello,
suppose I have these two tables in SQL Server 2005:
Authors:
ID - int, IDENTITY, autoincremented, autogenerated integer
Name - nvarchar(50)
Books:
ID - int, IDENTITY, autoincremented, autogenerated integer
Title - nvarchar(50)
AuthorID - int, foreign key to Authors. Cannot be null.
Let's suppose the relation is really 1:N so that each book has exactly one author. Now I want to connect by LINQ to SQL and create a new entry in both tables. So I do this:
[Table(Name = "dbo.Authors")]
class Author
{
[Column(IsPrimaryKey = true, AutoSync = AutoSync.OnInsert, IsDbGenerated = true, DbType = "Int IDENTITY")]
public int ID { get; set; }
[Column]
public string Name { get; set; }
}
[Table(Name = "dbo.Books")]
class Book
{
[Column(IsPrimaryKey = true, AutoSync = AutoSync.OnInsert, IsDbGenerated = true, DbType = "Int IDENTITY")]
public int ID { get; set; }
public string Title { get; set; }
// This is how we get linked to the related Author record
// See http://msdn.microsoft.com/en-us/library/bb386950.aspx
public int AuthorID { get; set; }
private EntityRef<Author> _Author;
[Association(Storage = "_Author", ThisKey = "AuthorID")]
public Author Author
{
get { return this._Author.Entity; }
set { this._Author.Entity = value; }
}
}
and I add a new book with a code like this where Author already exists and has a valid ID:
private static Book AddBook(DataContext context, Author author)
{
// Get the table object
Table<Book> table = context.GetTable<Book>();
// Create a new instance of Book
Book ts = new Book();
ts.Name = "Something";
ts.Author = author;
// Insert to the db
table.InsertOnSubmit(ts);
// Submit the changes
context.SubmitChanges();
return ts;
}
The problem is that I context.SubmitChanges fails with the exception that it cannot add a record whose AuthorID is NULL. And I don't have any clue why. I tried to change the set Author method in Book to
set { AuthorID = value.ID; this._Author.Entity = value; }
so that the AuthorID gets the correct value but this did not help.
I got the original code from http://msdn.microsoft.com/en-us/library/bb386950.aspx
I'm doing it in VC#2008 Express, SQL Server 2005 Express, WinXP Professional SP2 Czech, .NET 3.5.
Can you help me, please?
Thanks
Bolek
suppose I have these two tables in SQL Server 2005:
Authors:
ID - int, IDENTITY, autoincremented, autogenerated integer
Name - nvarchar(50)
Books:
ID - int, IDENTITY, autoincremented, autogenerated integer
Title - nvarchar(50)
AuthorID - int, foreign key to Authors. Cannot be null.
Let's suppose the relation is really 1:N so that each book has exactly one author. Now I want to connect by LINQ to SQL and create a new entry in both tables. So I do this:
[Table(Name = "dbo.Authors")]
class Author
{
[Column(IsPrimaryKey = true, AutoSync = AutoSync.OnInsert, IsDbGenerated = true, DbType = "Int IDENTITY")]
public int ID { get; set; }
[Column]
public string Name { get; set; }
}
[Table(Name = "dbo.Books")]
class Book
{
[Column(IsPrimaryKey = true, AutoSync = AutoSync.OnInsert, IsDbGenerated = true, DbType = "Int IDENTITY")]
public int ID { get; set; }
public string Title { get; set; }
// This is how we get linked to the related Author record
// See http://msdn.microsoft.com/en-us/library/bb386950.aspx
public int AuthorID { get; set; }
private EntityRef<Author> _Author;
[Association(Storage = "_Author", ThisKey = "AuthorID")]
public Author Author
{
get { return this._Author.Entity; }
set { this._Author.Entity = value; }
}
}
and I add a new book with a code like this where Author already exists and has a valid ID:
private static Book AddBook(DataContext context, Author author)
{
// Get the table object
Table<Book> table = context.GetTable<Book>();
// Create a new instance of Book
Book ts = new Book();
ts.Name = "Something";
ts.Author = author;
// Insert to the db
table.InsertOnSubmit(ts);
// Submit the changes
context.SubmitChanges();
return ts;
}
The problem is that I context.SubmitChanges fails with the exception that it cannot add a record whose AuthorID is NULL. And I don't have any clue why. I tried to change the set Author method in Book to
set { AuthorID = value.ID; this._Author.Entity = value; }
so that the AuthorID gets the correct value but this did not help.
I got the original code from http://msdn.microsoft.com/en-us/library/bb386950.aspx
I'm doing it in VC#2008 Express, SQL Server 2005 Express, WinXP Professional SP2 Czech, .NET 3.5.
Can you help me, please?
Thanks
Bolek
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.