Link to home
Create AccountLog in
Avatar of bovlk
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
SOLUTION
Avatar of joechina
joechina

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
ASKER CERTIFIED SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.