how does linq2sql handle tables with just FK in it?

Question

I have a mapping table like:

Article_to_Categories

ArticleID CategoryID

How would I insert into this table using linq2sql?

Answer 1

Provided that corresponding rows exist in the Article and Category tables, this should be no different than a standard insert.

On the other hand, if those tables are empty, then you'll need to insert rows into those tables before you execute SubmitChanges.

Linq-to-Sql will manage the precedence for you.

All in all, I suggest that you simply try to insert rows into the Article_to_Categories table and see what happens.

---

EDIT: If don't understand how to insert a row in Linq-to-Sql, consider examples on the web:

• Scott Guthrie's blog.
• MSDN.

Answer 2

In my experience, I have had similar problems inserting records in association tables like this one. What works for me is to honor the foreign key relationship and add objects rather than just the foreign key ID.

For example, this didn't work for me:

var newRec = new Article_To_Categories
{
    Article_ID = 1,
    Category_ID = 2
};

DataContext.Article_To_Categories.InsertOnSubmit(newRec);
DataContext.SubmitChanges();

What worked for me was this:

var newRec = new Article_To_Categories
{
    Article = DataContext.Articles.Where(a => a.Article_ID == 1).SingleOrDefault(),
    Category = DataContext.Articles.Where(a => a.Category_ID == 2).SingleOrDefault()
};

DataContext.Article_To_Categories.InsertOnSubmit(newRec);
DataContext.SubmitChanges();

As near as I could figure based on my research, if I add the object rather than the ID, the resulting Article_To_Categories object is correctly updated with the contents of the related records. Part of the updating process is the notification that the related records should not be re-inserted. If you just update the new record with the ID's, I don't believe that LINQ-to-SQL will automatically retrieve the records from the Article and Category tables, which means that those related records will be subject to any existing validation, whether from the object or the database.