Update with value from a select query in Linq To SQL

Question

Update TableToBeUpdated
Set ColumnNameId = (Select MasterColumnId 
                    from MasterTable where Id = @Id)
Where ForeingKeyId = @Id
   AND AnotherColumn = @AnotherColumn

How to achieve the above with one select statement and an update statement with a subquery?

Currently what I'm thinking is.

  //First Select statement
  var tableTBU = (from t in MsDataContext.TableToBeUpdated 
           where t.ForeingKeyId == <valueFromVariable>
           select t ).SingleOrDefault();

  //Second select statement
  var masterIdValue = (from m in MsDataContext.MasterTable 
                where m.Id == <valueFromVariable>
                select mod.MasterColumnId ).SingleOrDefault();


  tableTBU.ColumnNameId = masterIdValue ;
  tableTBU.SomeOtherColumn = "dkjsdlfs";
  //Some more things to be updated.

  //UpdateStatement
  MsDataContext.SubmitChanges();

Answer 1

LINQ-to-SQL works best if you allow the LINQ engine to manage the keys (I'm presuming that the relationship between MasterTable and TableToBeUpdated is one-to-one):

using (DataContext dc = new DataContext())
{
    var objectToUpdate = dc.MasterTable.Single(a => a.Id == id).TableToBeUpdated;

    objectToUpdate.SomeOtherColumn = "dkjsdlfs";
    dc.SubmitChanges();
}

This also presumes that you have related the two tables, either manually through the designer using an Association or through a foreign key relationship in the database.

UPDATE:

Since you don't have an existing foreign key relationship, then you need to relate the tables manually. Based on the SQL statement above:

using (DataContext dc = new DataContext())
{
    var objectToUpdate = dc.TableToBeUpdated
        .Single(b => b.ForeignKeyId.Contains(dc.MasterTable.Single(a => a.Id == id).Id)
            && b.AnotherColumn == anotherColumn);

    objectToUpdate.SomeOtherColumn = "dkjsdlfs";
    dc.SubmitChanges();
}