Unexpected behavior in entity structure

Question

I came across what, in my opinion, is a very strange situation with the infrastructure of the entity. Basically, if I update a row directly with the sql command, when I return this row via linq, it has no updated information. See the example below for more information.

First I created a simple DB table

CREATE TABLE dbo.Foo ( Id int NOT NULL PRIMARY KEY IDENTITY(1,1), Name varchar(50) NULL ) 

Then I created a console application to add an object to the database, update it using the sql command, and then restore the newly created object. There he is:

 public class FooContext : DbContext { public FooContext() : base("FooConnectionString") { } public IDbSet<Foo> Foo { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Foo>().ToTable("Foo"); base.OnModelCreating(modelBuilder); } } public class Foo { [Key] public int Id { get; set; } public string Name { get; set; } } public class Program { static void Main(string[] args) { //setup the context var context = new FooContext(); //add the row var foo = new Foo() { Name = "Before" }; context.Foo.Add(foo); context.SaveChanges(); //update the name context.Database.ExecuteSqlCommand("UPDATE Foo Set Name = 'After' WHERE Id = " + foo.Id); //get the new foo var newFoo = context.Foo.FirstOrDefault(x => x.Id == foo.Id); //I would expect the name to be 'After' but it is 'Before' Console.WriteLine(string.Format("The new name is: {0}", newFoo.Name)); Console.ReadLine(); } } 

The line below writes "Before," however I expect it to display "After." The strange thing is that if I run the profiler, I see that the sql query is being executed, and if I run the query in the management studio, it returns "After" as the name. I am starting sql server 2014.

Can someone please help me understand what is going on here?

UPDATE:

It will go to the database by the line FirstOrDefault. Please see the attached screenshot from the sql profiler.

So my question is actually this:

1) If this is caching, should it not be in the database? Is this a bug in EF?

2) If it goes to db and spends resources, EF should not update object.

Answer 1

FooContext includes change tracking and caching, so the in-memory object returned from your request is the same instance that you added earlier. Calling SaveChanges() clears the context, and FooContext not aware of the changes that occurred under it in the database.

As a rule, it’s good not to make expensive database queries for each operation.

In your example, try to make the same request from the new FooContext , and you should see "After".

Update

Answering your updated question, yes, you are right. I skipped before you used FirstOrDefault() . If you used context.Find(foo.Id) , as I mistakenly assumed, then there would be no request.

As for why the in-memory object is not being updated to reflect the change in the database, I need to do some research to do something more than speculate. However, here are my assumptions:

• A database context instance cannot return more than one instance of the same object. Within a unit of work, we must be able to rely on context to return the same instance of an object. Otherwise, we can request different criteria and get 3 objects representing the same conceptual object. At this point, how can the context relate to changes in any of them? What if the name is changed to another value by two of them, and then SaveChanges() is called - what should happen?
• Given that the context tracks no more than one instance of each object, why can't EF just update this object at the point at which the request is made? EF may even discard this change if there is a pending change in memory, because it knows about these changes.
  • I think one part of the answer is that it is impossible to distinguish all columns from large entities in large result sets.
  • I think most of the answer is that it executes a simple SELECT statement, should not be able to cause side effects throughout the system. Objects can be grouped or looped by the value of a property and change the value of this property indefinitely and as a result of a SELECT query is very unreasonable.