Unexpected behavior in entity framework

Question

I ran into what I think is a really odd situation with entity framework. Basically, if I update an row directly with a sql command, when I retrive that row through linq it doesn't have the updated information. Please see the below example 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 DB, update it with a sql command and then retrieve the object that was just created. Here it 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 write line at the bottom prints out "Before" however I would expect that it prints out "After". The odd thing about it is that if I run profiler I see the sql query run and if I run the query in management studio myself, it returns "After" as the name. I am running sql server 2014.

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

UPDATE:

It is going to the database on the FirstOrDefault line. Please see the attached screen shot from sql profiler.

So my question really is this:

1) If it is caching, shouldn't it not be going to the DB? Is this a bug in EF?

2) If it is going to the db and spending the resources, shouldn't EF update the object.

Answer 1

FooContext includes change tracking and caching, so the in-memory object that is returned from your query is the same instance that you added earlier. Calling SaveChanges() does clear the context and FooContext is not aware of the changes that happened underneath it in the database.

This is usually a good thing -- not making expensive database calls for every operation.

In your sample, try making the same query from a new FooContext, and you should see "After".

update

Responding to your updated question, yes, you are right. I missed before that you were using FirstOrDefault(). If you were using context.Find(foo.Id), as I wrongly assumed, then there would be no query.

As for why the in-memory object is not updated to reflect the change in the database, I'd need to do some research to do anything more than speculate. That said, here is my speculation:

• An instance of the database context cannot return more than one instance of the same entity. Within a unit of work, we must be able to rely on the context to return the same instance of the entity. Otherwise, we might query by different criteria and get 3 objects representing the same conceptual entity. At that point, how can the context deal with changes to any of them? What if the name is changed to a different value on two of them and then SaveChanges() is called -- what should happen?
• Given then that the context tracks at most a single instance of each entity, why can't EF just update that entity at the point at which a query is executed? EF could even discard that change if there is a pending in-memory change, since it knows about those changes.
  • I think one part of the answer is that diffing all the columns on large entities and in large result sets is performance prohibitive.
  • I think a bigger part of the answer is that it executing a simple SELECT statement should not have the potential to cause side effects throughout the system. Entities may be grouped or looped over by the value of some property and to change the value of that property at an indeterminate time and as a result of a SELECT query is highly unsound.