Linq to SQL insert/select foreign/primary records

Question

I have table A and Table B.
Table B contains two columns, Name and ID. Table A contains several columns and a foreign key column pointing to B.ID called B_ID

On the client side, I have all the data I want to insert into Table A, except for B_ID. I have B.Name though.

using link to sql I need to retrieve the id in table B based on the value of Name. If the record does not exist I need to insert it and get back the ID (which is an identity column).

Now that I have B.ID, I then have to insert record in table A.

I've built my data context class by importing tables A and B in the designer, I'm just not sure how to go about writing the linq to sql statements here.

Answer 1

When building your data context class, make sure to first set up an association between Table A & Table B.

The following should give you an idea of how to insert or update an object from Table B with a child relationship to Table A. Basically, you check to see if the object B exists. If it exists then return its ID. If not, create object A, add it to object B, and then insert object B. By creating object B, LINQ to SQL will insert the necessary information into both Table A & B.

DataContext db = new DataContext();

//Build object A
ObjectTableA objA = new objA();
objA.prop1 = ...
objA.prop2 = ...

// Check to see if the object exists in Table B
var query = from b in db.TableB
     where b.Name == name
     select b;

ObjectTableB objb = query.FirstOrDefault();

//if objb does not exist then create and insert a new object B
if (!objb) {
 objB = new ObjectTableB();
 objB.Name = name;

 //add objA
 objB.propA = objA;
 db.TableB.InsertOnSubmit(objB);
 db.SubmitChanges();
}

return objB.ID;