How to convert update/join SQL to Linq To SQL?

Question

I would like to convert this t-sql to linq to sql.

UPDATE Table1
SET CustomerName = 'john Doe'
FROM Table1 
JOIN Table2 
ON Table1.TableID = Table2.TableID
WHERE CustomerID = 4

Answer 1

Linq to SQL doesn't have an "update" statement, unfortunately. It's possible (but a little complicated) to do with extension methods, IQueryable, expression trees, and DbCommands. One approach is here - there's some source code you can download at the end.

Keep in mind that this might be buggy for more complex queries. When I use Linq to SQL I generally do all my batch updates/deletes through stored procedures.

Alternatively, if you just want to make the update code easier to write and don't care about performance (i.e. because you know there will be a very small number of rows to update), there is an implementation of an Update Operator (extension method) that you might be able to use.

Answer 2

ExecuteCommand comes in handly for those few (hopefully) occasions where LINQ to SQL isn't powerful enough.

Answer 3

If I understand your SQL Query correctly you want to update the name of the customer with CustomerId = 4.

Since you can't issue this SQL directly (as others have already pointed out) you need to fetch the customer with the id 4 from the db (using a DataContext), change his name and submit the changes to the databases using DataContext.SubmitChanges().

Answer 4

This is a simple way to do it using LINQ-to-SQL:

var query = from x in db.Table1
            join y in db.Table2 on x.TableID equals y.TableID
            where x.CustomerID == 4
            select x;

foreach(var result in query) {
    result.CustomerName = "John Doe";
}

db.SubmitChanges();

This approach is fine if you're sure that the result set will me small but it performs miserably on large result sets. There are approaches to batch updates using LINQ-to-SQL or you could just use DataContext.ExecuteCommand.