CODEWITHSUNDEEP
c#postgresqlasp.net-coreentity-framework-core
Frets
Frets

Reputation: 151

Getting processed data after stored procedure call

//1. inserting data into the db
_context.MyModel.AddRange(content);
_context.SaveChanges();

//2. calling a stored procedure for data process
_context.Database.ExecuteSqlRaw("CALL process_data()");

//3. getting back processed data - not working, getting pre-processed data instead
List<MyModel> processedContent = _context.MyModel.ToList();

I'm having a problem fetching processed data from step 2. in step 3. - the data I keep getting back is the one I inserted in step 1. (the pre-processed data) while I want to get stored procedure processed data.

I've put a break point on step 3., and run a query on the db - and yes, all the processed data is there. It seems like the _context doesn't get updated with new data.

Any clue what I'm doing wrong here?

Upvotes: 0

Views: 343

Answers (2)

Shay Rojansky
Shay Rojansky

Reputation: 16692

I couldn't reproduce the problem, see the code below. Although EF Core's DbContext indeed retains state, every time you execute ToList (or almost any other query operation), your query is executed and the latest results are fetched. A notable exception is Find, which will return tracked entity instances without querying the database.

If you still have issues, try changing the full sample below to demonstrate what isn't working.

class Program
{
    static async Task Main(string[] args)
    {
        await using var ctx = new BlogContext();
        await ctx.Database.EnsureDeletedAsync();
        await ctx.Database.EnsureCreatedAsync();

        await ctx.Database.ExecuteSqlRawAsync(@"
CREATE PROCEDURE process_data()
    LANGUAGE SQL
AS $$
    INSERT INTO ""Blogs"" (""Name"") VALUES ('Inserted from stored procedure');
$$;
");

        ctx.Blogs.Add(new Blog { Name = "Original" });
        await ctx.SaveChangesAsync();

        ctx.Database.ExecuteSqlRaw("CALL process_data()");

        await foreach (var blog in ctx.Blogs)
            Console.WriteLine(blog.Name);
    }
}

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(@"...")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Upvotes: 0

Frets
Frets

Reputation: 151

The only solution I've got for now is to instantiate a new context:

//1. inserting data into the db
_context.MyModel.AddRange(content);
_context.SaveChanges();

//2. calling a stored procedure for data process
_context.Database.ExecuteSqlRaw("CALL process_data()");

MyDbContext _newContext = new MyDbContext(_configuration);

//3. getting back processed data - working fine with new db context
List<MyModel> processedContent = _newContext.MyModel.ToList();

The other solution involves refreshing each entry inside the _context using:

foreach (var c in content)
{
   _context.Entry(c).Reload();
}

This works but it's not performance friendly.

Upvotes: 1

Related Questions