CODEWITHSUNDEEP
c#asp.net.netasp.net-mvcentity-framework
Chris Dixon
Chris Dixon

Reputation: 9167

Strange caching issues in Entity Framework 7

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?!

Upvotes: 3

Views: 614

Answers (1)

Jcl
Jcl

Reputation: 28272

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

There are several options:

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

  2. Use a new DbContext instance for each query

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

Upvotes: 4

Related Questions