Flexiable update in Linq To Sql

Question

Using Linq To Sql/Entities we have enough flexibility to write select queries

But what about update queries.

What if i need to do something simple like that:

UPDATE suppliers
SET supplier_name = (SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id)

Using Linq to Sql i need to run a lot of update queries to get ti done. First i need to select the entity which i need to update and then i need to update each of them, seams to be very slow.

Is there any way to make it simple, or there is only store procedure mapping may help in such case?

Answer 1

For batch operations like this I would go straight to SQL. In LINQ to SQL you can use DataContext.ExecuteCommand() for this. For example:

db.ExecuteCommand("UPDATE suppliers SET supplier_name = (SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id)");

If you believe that inline SQL is evil, have a look at this post by Terry Aney on enabling batch updates and deletes in LINQ to SQL. I have no experience with this but it looks interesting.

Answer 2

You might want to check out PLINQO. It added batch updates / deletes plus a whole lot more to LINQ to SQL.