EF Core Stored Procedure FromSqlRaw Does Not Give Updated Values

Question

When running a procedure on EF Core 3 using FromSqlRaw that updates values in the table, EF DOES NOT return the updated values when I query the database for those changed values.

I have been able to reproduce this behavior. To reproduce create a new console app c# with .net core 3.1.

Copy paste the code below into your main Program.cs file:

using System;
using System.Linq;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

namespace EfCoreTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");

            // testing proc
            var dbContext = new TestContext();
            var tables = dbContext.TestTables.ToList();
            var updated = dbContext.TestTables
                .FromSqlRaw("execute testProc @Id=@Id, @Comments=@Comments", new object[]
                {
                    new SqlParameter("Id", 1),
                    new SqlParameter("Comments", "testing comments 2"),
                })
                .ToList();
            var again = dbContext.TestTables.ToList();
        }
    }


    public class TestTable
    {
        public int TestTableId { get; set; }

        public string Comment { get; set; }
    }

    public class TestContext : DbContext
    {
        public DbSet<TestTable> TestTables { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=localhost\SQLEXPRESS;Database=TestDb;Trusted_Connection=True");
        }
    }
}

Ensure that the following packages are installed:

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.SqlServer.Design

Change your connection string if necessary.

Run dotnet ef migrations add initial

Run dotnet ef database update

Run the following code in your db:

drop procedure if exists testProc
go
create procedure testProc
@Id int,
@Comments nvarchar(max)
as
begin
    update dbo.TestTables
    set Comment = @Comments
    where TestTableId = @Id;

    select * from dbo.TestTables;
end

go


INSERT INTO [dbo].[TestTables]
(Comment) VALUES ('Test Comment');

So when you run the Main program on debug and put a breaker, you'll notice that NONE of the objects return values that were updated by the procedure when go to inspect it. While in debug if you run a select statement on the table you will see that the "Comment" field is indeed updated.

Why is this?

Answer 1

This is not specific to FromSql, but the way EF Core (all versions) tracking queries work.

Here is an excerpt from EF Core How Queries Work documentation topic:

The following is a high level overview of the process each query goes through.

1. The LINQ query is processed by Entity Framework Core to build a representation that is ready to be processed by the database provider
   • The result is cached so that this processing does not need to be done every time the query is executed
2. The result is passed to the database provider
   • The database provider identifies which parts of the query can be evaluated in the database
   • These parts of the query are translated to database specific query language (for example, SQL for a relational database)
   • One or more queries are sent to the database and the result set returned (results are values from the database, not entity instances)
3. For each item in the result set
   • If this is a tracking query, EF checks if the data represents an entity already in the change tracker for the context instance If so, the existing entity is returned If not, a new entity is created, change tracking is setup, and the new entity is returned

Note the last bullet. What they do is basically an implementation of the so called client wins strategy (as opposed to database wins which you are looking for), and currently there is no way of changing that other than using no-tracking query.

In your example, insert AsNotTracking() somewhere in the queries (before ToList, after dbContext.TestTables - it really doesn't matter because it applies to the whole query), or just

dbContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

and now you'll see the updated values (from your SP call or from other sessions to the same database).