How to insert in linq to sql

Question

I'm having problem with two tables at the time of insert. The tables were created as follows:

I have a table "Page" that has a relationship with another table "Content" so that the FK "Page" falls within the "Content"

By the time I insert a new content, there is already an index on the table "Page" I want to put in the "Content" realize why a table scan select "Page" the correct line. The problem is that it's time to take a InsertOnSubmit the content. it automatically is also creating a new index on the "Page".

Anyone have any ideas?

Answer 1

Since the Page Entity has a one to many relation with the Content entity, therefore each Page has a lot of Contents and each Content falls into only One Page, so the Page entity must has a list of Contents and the Content entity must has a property of type Page that represents the page reference instead of the foreign key PageId, so you didn't need to retrieve the id of the page in order to add a new Content, as follows:

[Table]
public class Page
{
    ...
    private EntitySet<Content> _contents;
    [Association(Storage = "_contents", ThisKey = "Id", OtherKey = "PageId")]
    public EntitySet<Content> Contents
    {
        get { return _contents; }
        set { _contents.Assign(value); }
    }
    ...
}

For the Content entity:

[Table]
public class Content
{
    ...
    [Column]
    int PageId{ get; set; }
    EntityRef<Page> _page;
    [Association(Storage = "_page", ThisKey = "PageId", 
      OtherKey = "Id", IsForeignKey = true)]
    public Page Page
    {
        get { return _page.Entity; }
        set { _page.Entity = value; }
    }
 }

Whenever you need to inert a new Content, you have to pass a page reference like this:

 Page pageReference = //Get By its id or whatever
 //or
 Page pageReference = new Page { Id = 1, name = " ",....}; 
 Repository.Add( new Content{ Id = 1,...,Page = pageReference });

Note that: the add method is just do an InserOnSubmit to the entity.

So whenever you want to insert a new content you can attach any page or add any new page just from the page entity it self not from Content, and you didn't need to retrieve the id of the page in order to tell'em what is the page that will have this content just define the relation as objects relations not as the foreign keys.