Workaround for LINQ to SQL Entity Identity Caching

Question

I've come across what appears to be a bug in linq to sql where identity caching does not work when performing primary key queries inside of a compiled query.

I wrote the following sample to demonstrate the usage of identity caching. It only executes one call to the database the first time it's hit, every time after that it retrieves the customer entity from the data context's cache.

    for(int i=0; i<10; i++)
    {
        DataContext.GetTable<Customer>().Single(c=>c.Id == 1);
    }

Unfortunately, when I convert the above sample to a compiled query, it fails to utilize the identity cache and actually executes 10 calls to the database.

    for(int i=0; i<10; i++)
    {
        RetrieveCustomer(DataContext, 1);
    }

    private static readonly Func<DataContext, int, Customer> RetrieveCustomer =
    CompiledQuery.Compile((DataContext context, int id) => context.GetTable<Customer>().Single(c=>c.Id == id));

Has anyone else come across this problem and created a workaround for it? It is extremely important for server based applications to utilize compiled queries and identity caching, so I'm hoping this is a problem that someone else has worked through before!

Answer 1

Looks like a bug - there have been a number in this area.

As a work-around I wouldn't create a compiled query for such a small/simple operation - the real benefit of compiled query is for large queries that take a lot of time to process into TSQL.

Update: This is a bug and was resolved as will not fix.

Answer 2

I ended up using a dirty hack to workaround this by using reflection to call a private method of linq entity objects called GetCachedEntity to forcefully utilize the cache. I didn't have time to implement a cleaner solution but for anyone interested in this topic I would recommend implementing your own caching mechanism for this scenario.