10

I want to update the only field of entity when I know entity Id.

Is it possible in LINQ to SQL without retrieving full entity (with all fields from DataContext that is overhead) ? Is it possible to create and attach entity to DataContext and mark the exact field(s) to synchronize on DataContext.SubmitChanges (or something like that)?

Thank you in advance!

Andrew Florko
  • 7,672
  • 10
  • 60
  • 107

3 Answers3

8

Yes you can:

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 a select.

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
laktak
  • 57,064
  • 17
  • 134
  • 164
1

You can always create a standard T-SQL statement and execute that against your data store:

YourDataContext
  .ExecuteCommand("UPDATE dbo.YourTable SET ThatField = newValue WHERE ID = 777", null);

With Linq-to-SQL itself, you cannot do this - it's basic assumption is that it always operates on the object, the whole object, and nothing but the object.

If you need to do this on a regular basis, one way would be to wrap it into a stored proc and add that stored proc to your data context as a method you can call on the data context.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • OMG, it's a pity. Has situation changed in EF ? Would you personally rely on embedded T-SQL operators or Stored Procedures embedded in Model for such cases? – Andrew Florko Jul 21 '10 at 17:12
  • No - EF has the same approach - but so do NHibernate or any of the other OR mappers. They're designed to load, manipulate and save **objects** as a whole. I am not aware of any ORM that would allow you to retrieve just one or two fields, change and save those.... – marc_s Jul 21 '10 at 17:16
  • in EF we can create in-memory entity, initiate EntityKey property + primary key property, then attach, then change properties we want to update. SaveChanges works for updates. – Andrew Florko Aug 25 '10 at 09:11
1

You can refresh the object. This example will change the person's first name:

Person person = _entities.Persons.FirstOrDefault(p => p.Id == id);
person.FirstName = "Bill";
_entities.Refresh(System.Data.Objects.RefreshMode.ClientWins, person);
_entities.SaveChanges();
gnome
  • 1,113
  • 2
  • 11
  • 19
  • 1
    Thank you, @Gnome. But is it possible to update field without entity retrieving? – Andrew Florko Jul 21 '10 at 17:34
  • ;-) I was just re-reading your post. As far as I know you will need the entity. But this solution avoids that old-school-sql. – gnome Jul 21 '10 at 17:37
  • 1
    yes, it avoids the "old-school" SQL - but it will need to retrieve the full entity first, and will save back the full entity again. The "old school" SQL can get around that very easily..... – marc_s Jul 21 '10 at 18:49