Overriding entity caching/change-tracking behaviour in Linq to SQL

I think that perhaps the original question was too long-winded with too many unnecessary details, so this is my attempt to simplify.

I am looking for a means to perform any of the actions below. I only need to do one, not all. If anyone knows the answer to even one of these, please respond. So, is it possible to do any of the following in Linq to SQL:

• Pull entities out of a DataContext via ExecuteQuery or ExecuteMethodCall without having those entities tracked?

• Invoke ExecuteQuery or ExecuteMethodCall and guarantee that I always receive fresh copies of the results retrieved from the database, even if those entities had already been retrieved and are already in the identity cache?

• Instruct Linq to SQL not to perform any change tracking whatsoever on specific entity types - but still allow change tracking for other types?

Restrictions:

• The Refresh method is out of the question; the number of entities is quite开发者_C百科 large and this would become a performance disaster.

• I cannot simply set ObjectTrackingEnabled to false, because the DataContext does not allow setting it back to true after a query has been executed, and I do need some of the entities to be tracked.

• I also cannot throw away the original DataContext and use a new one; I need to be able to do this in the middle of a transaction.

This is starting to become a serious problem, and I really think that the default behaviour is ill-conceived. If I execute an ad-hoc query or stored procedure, I expect the results I receive to be the exact results that were returned by said query. It only makes sense; if I wanted the old, stale entities, why would I go back to the database to get them?

At the moment, my workaround is to either (a) create a new DataContext specially for the query and override the transaction isolation level, or (b) make the return type a "DTO" that is identical to the entity in every way but without the [Table] attribute, and map it to the original entity using AutoMapper. Both of these seem like horrible hacks.

Would really appreciate any suggestions anyone has on this conundrum.

I've managed to come up with a viable longer-term workaround for this issue. It's not perfectly ideal, but it's been relatively painless to employ so far and is far less scary than the alternatives.

Since these queries are pure SQL anyway - they're all ExecuteQuery for inline SQL or ExecuteMethodCall for stored procedures - I've decided to just drop down to "raw" ADO.NET for instances when I don't want the DataContext to know about certain entities.

Of course, it would be horrible to have to deal with a bunch of IDbCommand instances and manual mappings from IDataReader, so I spent a few hours this morning coding up a library to do most of the heavy lifting for me, exposing a "fluent" (I use the term loosely) wrapper for the IDbCommand, an automatic LinqDataReaderMapper which uses the MetaModel so I can use my existing entities without modifications, and a bunch of overloaded extension methods to make it quicker to write the simpler queries.

At the end of the day, I'm writing something like this:

var results = context.Connection
    .Command("SELECT Column1, Column2 FROM Table " +
             "WHERE FilterColumn1 = @Param1 AND FilterColumn2 = @Param2")
    .Parameters(
        p => p.Name("Param1").Value(someValue),
        p => p.Name("Param2").Value(someOtherValue))
    .ExecuteReader()
    .MapWith(context.Mapping).To<MyEntity>();

Or just this:

var results = context
    .ExecuteQueryRaw<MyEntity>(CommandType.StoredProcedure, "SomeProc",
        new { Param1 = someValue, Param2 = someOtherValue });

I'm not going to post the entire code for it - it's pretty long and I think it would just be a big tl;dr at this point - but the main idea is that these both give me back an IEnumerable<MyEntity>, and since they're being copied directly from an IDataReader, they are essentially detached entities - the DataContext has no direct knowledge of them and can therefore neither intercept the results nor track them after the fact.

So, problem partially solved, although it would still be better if I could just get the DataContext to behave itself.

If you construct a new object for your results from a LINQ query, it will not be change tracked. So you could use your existing DataContext to load values you know you won't need to update, like so (code from here):

using (NorthwindDataContext context = new NorthwindDataContext())
{
  var a = from c in context.Categories
  select new Category
  {
    CategoryID = c.CategoryID,
    CategoryName = c.CategoryName,
    Description = c.Description
  };
}

I think this is essentially what you have in the second example in your answer, I just want to affirm that that works and isn't a bad idea.

Also, my understanding is that you shouldn't be using a single massive DataContext in any case; a DataContext is really a Unit-of-Work level collection, not the whole world. So using separate DataContexts for your read-only type data and your updateable data makes perfect sense (unless you can't predict which is which in advance, I suppose).