Update a record without first querying?

Question

Lets say I query the database and load a list of items. Then I open one of the items in a detail view form, and instead of re-querying the item out of the database, I create an instance of the item from the datasource in the list.

Is there a way I can update the database record without fetching the record of the individual item?

Here is a sample how I am doing it now:

dataItem itemToUpdate = (from t in dataEntity.items
                                 where t.id == id
                                 select t).FirstOrDefault();

Then after pulling the record I update some values in the item and push the record back:

itemToUpdate.itemstatus = newStatus;
dataEntity.SaveChanges();

I would think there would be a better way to do this, any ideas?

Answer 1

You can also use direct SQL against the database using the context of the datastore. Example:

dataEntity.ExecuteStoreCommand
   ("UPDATE items SET itemstatus = 'some status' WHERE id = 123 ");

For performance reasons, you may want to pass in variables instead of a single hard coded SQL string. This will allow SQL Server to cache the query and reuse with parameters. Example:

dataEntity.ExecuteStoreCommand
   ("UPDATE items SET itemstatus = 'some status' WHERE id = {0}", new object[] { 123 });

UPDATE - for EF 6.0

dataEntity.Database.ExecuteSqlCommand
       ("UPDATE items SET itemstatus = 'some status' WHERE id = {0}", new object[] { 123 });

Answer 2

This article as part of Microsoft's Getting Started explains entity states and how to do this:

Add/Attach and Entity States

Look at the section 'Attaching an existing but modified entity to the context'

Now I'm off to read the rest of these tutorials.

Answer 3

If the DataItem has fields EF will pre-validate (like non-nullable fields), we'll have to disable that validation for this context:

DataItem itemToUpdate = new DataItem { Id = id, Itemstatus = newStatus };
dataEntity.Entry(itemToUpdate).Property(x => x.Itemstatus).IsModified = true;
dataEntity.Configuration.ValidateOnSaveEnabled = false;
dataEntity.SaveChanges();
//dataEntity.Configuration.ValidateOnSaveEnabled = true;

Otherwise we can try satisfy the pre-validation and still only update the single column:

DataItem itemToUpdate = new DataItem
{
    Id = id,
    Itemstatus = newStatus,
    NonNullableColumn = "this value is disregarded - the db original will remain"
};
dataEntity.Entry(itemToUpdate).Property(x => x.Itemstatus).IsModified = true;
dataEntity.SaveChanges();

Assuming dataEntity is a System.Data.Entity.DbContext

You can verify the query generated by adding this to the DbContext:

/*dataEntity.*/Database.Log = m => System.Diagnostics.Debug.Write(m);

Answer 4

It works somewhat different in EF Core:

There may be a faster way to do this in EF Core, but the following ensures an UPDATE without having to do a SELECT (tested with EF Core 2 and JET on the .NET Framework 4.6.2):

Ensure your model does not have IsRequired properties

Then use the following template (in VB.NET):

    Using dbContext = new MyContext()
        Dim bewegung = dbContext.MyTable.Attach(New MyTable())
        bewegung.Entity.myKey = someKey
        bewegung.Entity.myOtherField = "1"

        dbContext.Entry(bewegung.Entity).State = EntityState.Modified
        dbContext.Update(bewegung.Entity)

        Dim BewegungenDescription = (From tp In dbContext.Model.GetEntityTypes() Where tp.ClrType.Name = "MyTable" Select tp).First()
        For Each p In (From prop In BewegungenDescription.GetProperties() Select prop)
            Dim pp = dbContext.Entry(bewegung.Entity).Property(p.Name)
            pp.IsModified = False
        Next
        dbContext.Entry(bewegung.Entity).Property(Function(row) row.myOtherField).IsModified = True
        dbContext.SaveChanges()
    End Using

Answer 5

The code:

ExampleEntity exampleEntity = dbcontext.ExampleEntities.Attach(new ExampleEntity { Id = 1 });
exampleEntity.ExampleProperty = "abc";
dbcontext.Entry<ExampleEntity>(exampleEntity).Property(ee => ee.ExampleProperty).IsModified = true;
dbcontext.Configuration.ValidateOnSaveEnabled = false;
dbcontext.SaveChanges();

The result TSQL:

exec sp_executesql N'UPDATE [dbo].[ExampleEntities]
SET [ExampleProperty ] = @0
WHERE ([Id] = @1)
',N'@0 nvarchar(32),@1 bigint',@0='abc',@1=1

Note:

The "IsModified = true" line, is needed because when you create the new ExampleEntity object (only with the Id property populated) all the other properties has their default values (0, null, etc). If you want to update the DB with a "default value", the change will not be detected by entity framework, and then DB will not be updated.

In example:

exampleEntity.ExampleProperty = null;

will not work without the line "IsModified = true", because the property ExampleProperty, is already null when you created the empty ExampleEntity object, you needs to say to EF that this column must be updated, and this is the purpose of this line.

Answer 6

You should use the Attach() method.

Attaching and Detaching Objects