Strange caching issues in Entity Framework 7

Question

I've come across something quite specific and wondering if anyone out there has faced the same issue.

My SQL query (in a Stored Procedure) is simple, I've simplified it a little but:

BEGIN
        SELECT DISTINCT
            [ANU].[OldUserId] AS [ID]
            ,[ANU].[Email]
        FROM
            [dbo].[AspNetUsers] AS [ANU]
        INNER JOIN
            [dbo].[User] AS [U]
        ON
            [U].[ID] = [ANU].[OldUserId]
    END

Pretty simple, and the SP is fine when run directly through SQL Management Studio.

However, I run it via Entity Framework as such:

 [ResponseCache(Duration = 0)] // used this out of desperation
 public List<DriverDTO> GetByOrganisation(int organisationId, bool isManager)
 {
      return _context.Set<DriverDTO>().FromSql("dbo.New_User_List @OrganisationId = {0}, @IsManager = {1}", organisationId, isManager).ToList();
 }

DriverDTO:

public class DriverDTO
{
      [Key] // tried removing this also
      public int ID { get; set; }
      public string Email { get; set; }
}

It runs and brings back results, fine. However these results are getting cached. Every call to the SP after the first call brings back the same results, even if I update the records. So, say I edit a User record and change the email - the originally fetched email will always be brought back.

Again, running the SP via SQL Manager brings back the correct results, but my C#/EF side does not. The only logical thing in my head here is that something is somehow being cached under the hood that I desperately need to get around?!

Jcl · Accepted Answer · 2016-04-07 10:26:55Z

4

Your loaded entities are cached in the DbContext (in each DbSet's Local collection).

There are several options:

Use AsNoTracking for your query:

return _context.Set<DriverDTO>()
           .AsNoTracking()
           .FromSql("dbo.New_User_List @OrganisationId = {0}, @IsManager = {1}", organisationId, isManager)
           .ToList();

This should avoid Entity Framework caching completely for this query

Use a new DbContext instance for each query
Alternatively, detach all cached entities from the context before issuing your query... something like (untested):
```
_context.Set<DriverDTO>().Local.ToList().ForEach(x=>
{
   _context.Entry(x).State = EntityState.Detached;
});
```
Notice that, opposite to what one may think, you can't use _context.Set<DriverDTO>().Local.Clear(), since this will mark your entities as deleted (so if you SaveChanges afterwards, it'll delete the entities from the database), so be careful if you are experimenting with the local cache.

Unless you have a need to use a single DbContext or have the received entities from the SP attached to it, I'd go for #2. Otherwise, I'd go for #1. I put #3 there for completeness but I'd avoid mangling with the local cache unless strictly necessary.

edited Apr 7, 2016 at 10:26

answered Apr 7, 2016 at 10:17

Jcl

28.4k5 gold badges66 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Dixon Over a year ago

You've saved my bacon today in a big way, thank you! The .AsNoTracking() function worked an absolute treat for me and is the simplest route to go down for my scenario. I just hope this helps others too.

Jcl Over a year ago

@ChrisDixon glad it helped. As a side note, AsNoTracking queries are much faster and require less than half the memory than normal EF querying... so I'd use it always unless there's a reason not to (basically, wherever your context will be used only for querying, and not for updating/deleting)

Chris Dixon Over a year ago

Brilliant advice. Learn something new every day and will speed up my system dramatically!

Collectives™ on Stack Overflow

Strange caching issues in Entity Framework 7

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related