Add entity to EntitySet throw exception on submitchanges()

Question

Hello

I'm using Linq to Sql and have a Many-To-Many relation and therefore use a relation table.

When i try to insert data with relation/association i get error.

I use manual schema creation.

My simplified code:

First table/class Booklet has this association:

    private EntitySet<BookletChapterRel> _bookletChapterRel = new EntitySet<BookletChapterRel>();

    [Association(Name = "ep_booklet_ep_book_chap_rel", OtherKey = "BookletID", ThisKey = "BookletID", Storage = "_bookletChapterRel")]
    public EntitySet<BookletChapterRel> BookletChapterRel
    {
        set { _bookletChapterRel.Assign(value); }
        get { return _bookletChapterRel; }
    }

The BookletChapterRel has these two association:

    internal EntityRef<Booklet> _booklet;
    [Association(Name = "ep_booklet_ep_book_chap_rel", OtherKey = "BookletID", ThisKey = "BookletID", Storage = "_booklet", IsForeignKey = true, DeleteOnNull = true, DeleteRule = "CASCADE")]
    public Booklet Booklet
    {
        get { return _booklet.Entity; }
        set { _booklet.Entity = value; BookletID = value.BookletID; }
    }

    internal EntityRef<Chapter> _chapter;
    [Association(OtherKey = "ChapterID", ThisKey = "ChapterID", Storage = "_chapter")]
    public Chapter Chapter
    {
        get { return _chapter.Entity; }
        set { _chapter.Entity = value; ChapterID = value.ChapterID; }
    }

Chapter has this association:

    internal EntityRef<BookletChapterRel> _bookletChapterRel;
    [Association(OtherKey = "ChapterID", ThisKey = "ChapterID", Storage = "_bookletChapterRel")]
    public BookletChapterRel BookletChapterRel
    {
        get { return _bookletChapterRel.Entity; }
        internal set { _bookletChapterRel.Entity = value; ChapterID = value.ChapterID; }
    }

I try to make a simple insert:

Booklet b = new Booklet();
b.NumberOfPages = 0;
b.Title = "Hello";
b.DateCreated = DateTime.Now;
b.DateModified = DateTime.Now;

bookletRepository.addBooklet(b); //Just calls booklettable.InsertOnSubmit()

var bookChapRel = new BookletChapterRel()
{
    ChapterID = 23,
    ViewOrder = 0
};

b.BookletChapterRel.Add(bookChapRel);

bookletRepository.SubmitBookletChanges();

But every time i get the exception:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ep_book_chap_rel_ep_booklet". The conflict occurred in database "easypiecy_v2", table "dbo.ep_booklet", column 'BookletID'.

If i do something similar with a EntityRef there is no problem.

What i'm i doing wrong? I have tried to do same thing on the same database with automatic schema generation and it worked fine.

Thanks, Michael.

Answer 1

ChapterID = 23

May be the problem in that. Is your DB allow you to create BookletChapterRel with same ChapterID?

Is BookletID auto-increment in DB?

Answer 2

Okay, I got it solved after examining the auto designers code:

Instead of using:

private EntitySet<BookletChapterRel> _bookletChapterRel = new EntitySet<BookletChapterRel>();

I have to use the EntitySet overload like this:

private EntitySet<BookletChapterRel> _bookletChapterRel;

Constructor:

public Booklet()
{
   _bookletChapterRel = new EntitySet<BookletChapterRel>(new Action<BookletChapterRel>(this.attach_ep_book_chap_rels), null);
}