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?

https://stackoverflow.com/questions/46205114/how-to-refresh-an-entity-framework-core-dbcontext — lptr, Feb 06 '20 at 19:30

Ivan Stoev · Accepted Answer · 2020-02-06T19:38:32.263

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.

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

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)

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).

Ok, so I was aware of no tracking, and new it had something to do with how ef is caching results. I used `AsNoTracking` on the wrong query and that's when I decided to ask the question. Thank you for your answer and that high level excerpt. My tests for my implementation are now passing. However this does raise a design issue for me, whether `AsNoTracking` for most db queries returning results from repositories should be standard within the context of my project. — Paul Carlton, Feb 06 '20 at 21:36
This was the answer to my problems as well, despite not making any changes, the results of my Stored procedure I was executing weren't updated inside of a loop until I added .AsNoTracking().ToList() — CodeThief, Sep 22 '20 at 11:02

EF Core Stored Procedure FromSqlRaw Does Not Give Updated Values

1 Answers1