CODEWITHSUNDEEP
c#sql-serverlinqentity-framework
ilija veselica
ilija veselica

Reputation: 9574

Does LINQ update whole object in db if only one column is changed?

If an object has more than one column and program updates only one column, does LINQ update all columns in database no matter if they are changed or not or it updates only changed column(s)?

Example class:

MyObject
{
    int ID {get; set}
    string Field1 {get; set}
    string Field2 {get; set}
    string Field3 {get; set}
    string Field4 {get; set}
    string Field5 {get; set}
}

Now, I grab a recod from db and change only one field 

var myObject= 
(
   from x in db.TableName
   where x.ID == 12345
   select x
)
.Single();

myObject.Field1 = "something";

db.SubmitChanges();

Does SQL query perform an update statement on all columns or only Field1 column?

Upvotes: 1

Views: 1046

Answers (1)

David
David

Reputation: 218828

It's not that granular. (Nor should it be, as column-level tracking would introduce a whole host of complexity into concurrency tracking, which is already a difficult and compromise-ridden subject.)

When you're using an ORM (such as Linq to SQL, Entity Framework, etc.) the focus is on the object. The framework maps that object (the entire graph of related objects, actually) into the relational database schema. But what you're updating when you commit your changes to an ORM for persistence is the object graph.

The ORM will track which objects changed, and will make its concurrency checks at the object level in accordance with the mapping logic and concurrency rules set forth. But it's going to compile SQL update statements for each record in its entirety as it corresponds to the object.

The state of the object is what changed, and so it should be persisted in its entirely new state. While it's certainly possible to track changes at the column level, the return on investment for that just doesn't exist. The code would be vastly more complex, which means:

  • It would be far more difficult to support
  • It would be far more prone to errors
  • It would run much slower
  • It would be far more difficult to understand and predict its behavior
  • Not to mention, of course, lots of new confusion in concurrency tracking. (Suppose User A updates the phone number for Record X, and User B simultaneously updates the address for Record X. How would you propose those changes be merged automatically? I'm sure you can imagine much, much more complex examples from there.)

The trade-off just doesn't add up in this case. When using an ORM, you're updating an object. The persistence model is abstracted (and pretty well optimized as it stands anyway).

For transactional systems, this is ideal. When committing a unit of work for a transactional system, in the vast majority of cases you're starting with an aggregate root (or a small number of aggregate roots) and updating the graph of objects beneath them. The relational graph is the more important piece in this scenario, and that's what the ORM is meant to handle.

For making mass-updates to targeted columns, you're no longer talking about units of work in a transactional system. At this point you're talking about directly interacting with the table data for data manipulation, data migration perhaps, even some business intelligence tasks. This is a whole different toolset, outside the scope of what ORMs provide.

Upvotes: 1

Related Questions