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?