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
bovlkAsked:
Who is Participating?
 
bovlkAuthor Commented:
Thanks joechina. Meanwhile, I found the error in my code. The [Column] attribute is missing on AuthorID in class Book. Should be:

[Column]
public int AuthorID { get; set; }
0
 
joechinaCommented:
Try load the author , then load the books of the author, then add a book.
For example

var author = dc.Authors.Single(a => a.AuthId == 1);
author.Books.Load();  //this is required when lazy load is on (delay load is true)
Book b = new Book();
b.Name = "Test Book";
author.Books.Add(b);
dc.SubmitChanges();


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.