Use linq to generate direct update without select

Question

G'day everyone.

I'm still learning LINQ so forgive me if this is naive. When you're dealing with SQL directly, you can generate update commands with conditionals, without running a select statement.

When I work with linq I seem to follow the pattern of:

1. Select entities
2. Modify entities
3. Submit changes

What I want to do is a direct update using linq and deferred execution. Is it possible that the actual execution occurs directly at the SQL without any data being transmitted up to the client?

DataContext dc = new DataContext

var q = from product in dc.Products
        where product.Type = 1
        set product.Count = 0

dc.SubmitChanges

So in essence LINQ has all the information it needs WITHOUT using a select to generate an update command. It would run the SQL:

Update Products Set Count = 0 Where Type = 1

Does a keyword like "set" exist in LINQ?

Answer 1

Linq 2 SQL doesn't have direct insert/update/delete equivalents of SQL. In V1 the only updates you can do using linq is thought SubmmitChanges on the context or if you fallback to sql.

However some people have tried to overcome this limitation of linq using custom implementations.

Linq batch update.

Answer 2

No, neither LINQ nor LINQ to SQL has set-based update capabilities.

In LINQ to SQL, you must query for the object you wish to update, update the fields/properties as necessary, then call SubmitChanges(). For example:

var qry = from product in dc.Products where Product.Name=='Foobar' select product;
var item = qry.Single();
item.Count = 0;
dc.SubmitChanges();

If you wish to do batching:

var qry = from product in dc.Products where Product.Type==1 select product;
foreach(var item in qry)
{
  item.Count = 0;
}
dc.SubmitChanges();

Alternatively, you could write the query yourself:

dc.ExecuteCommand("update Product set Count=0 where Type=1", null);

Answer 3

The PLINQO (http://plinqo.com) framework is using the LINQ batch update to perform updates

context.Task.Update(t => t.Id == 1, t2 => new Task {StatusId = 2});

This will perform a Update Task Set StatusId = 2 Where Id = 1

Answer 4

You can actually let LINQ-to-SQL generate update statements:

Foo foo=new Foo { FooId=fooId }; // create obj and set keys
context.Foos.Attach(foo);
foo.Name="test";
context.SubmitChanges();

In your Dbml set UpdateCheck="Never" for all properties.

This will generate a single update statement without having to do a select first.

One caveat: if you want to be able to set Name to null you would have to initialize your foo object to a different value so Linq can detect the change:

Foo foo=new Foo { FooId=fooId, Name="###" };
...
foo.Name=null;

If you want to check for a timestamp while updating you can do this as well:

Foo foo=new Foo { FooId=fooId, Modified=... }; 
// Modified needs to be set to UpdateCheck="Always" in the dbml