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
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.