CODEWITHSUNDEEP
c#linq-to-sql
Dreamwalker
Dreamwalker

Reputation: 3035

How to insert child object with one to one relationship in Linq to SQL

EDIT

Ok everything I was doing code wise was correct I had placed the relationship on the tables wrong!

Question

I have a one to one relationship from task to message. When inserting new items I am getting a foreign key violation.

Details:

DB Structure

Task
    ID (Primary Key, Guid, Default = NEWID())
    MessageID (Unique Index with foreign key pointing to Message.ID)

Message
    ID (Primary Key, Guid, Default = NEWID())

The dbml settings for the primary keys on each table have Auto Generate Value set to true and Auto-Sync set to OnInsert

I have the following code to insert the new objects

    var objMessage = new Data.Message()
    {
        Body = "",
        Subject = ""
    };
    Context.Messages.InsertOnSubmit(objMessage);

    var objTask = new Data.Task()
    {
        Message = objMessage
    };

    Context.Tasks.InsertOnSubmit(objTask);
    Context.SubmitChanges();

This throws a SQL error foreign key constrain violated.

The SQL profiling shows linq to sql is inserting task before message which it can't do as it needs the id from message so of course this causes a foreign key violation.

Where am I going wrong in this?

Upvotes: 0

Views: 2254

Answers (2)

Arion
Arion

Reputation: 31239

The problem what I can see is the first InsertOnSubmit. If you want to add it to the related table then I do not think you need the first InsertOnSubmit. Because when you insert the main object the child object will also be inserted with it's foreign key.

I think you can just do it like this:

var objTask = new Data.Task()
    {
        Message = new Data.Message()
                {
                    Body = "",
                    Subject = ""
                }
    };
    Context.Tasks.InsertOnSubmit(objTask);
    Context.SubmitChanges();

Upvotes: 2

Saman Gholami
Saman Gholami

Reputation: 3512

Try to do this :

var objMessage = new Data.Message()
{
    Body = "",
    Subject = ""
};
Context.Messages.InsertOnSubmit(objMessage);
Context.SubmitChanges();

var objTask = new Data.Task()
{
    Message = objMessage
};

Context.Tasks.InsertOnSubmit(objTask);
Context.SubmitChanges();

You must SubmitChanges before using the new record

Upvotes: 0

Related Questions