CODEWITHSUNDEEP
linq-to-sql
Thomas Jespersen
Thomas Jespersen

Reputation: 11793

How can I force Linq to SQL NOT to use the cache?

When I make the same query twice, the second time it does not return new rows form the database (I guess it just uses the cache).

This is a Windows Form application, where I create the dataContext when the application starts.

How can I force Linq to SQL not to use the cache?

Here is a sample function where I have the problem:

public IEnumerable<Orders> NewOrders()
{
    return from order in dataContext.Orders
           where order.Status == 1
           select order; 
}

Upvotes: 7

Views: 4709

Answers (2)

Simon_Weaver
Simon_Weaver

Reputation: 146090

It can matter HOW you add an object to the DataContext as to whether or not it will be included in future queries.

Will NOT add the new InventoryTransaction to future in memory queries

In this example I'm adding an object with an ID and then adding it to the context.

var transaction = new InventoryTransaction()
                 {
                     AdjustmentDate = currentTime,
                     QtyAdjustment = 5,
                     InventoryProductId = inventoryProductId
                 };

dbContext.InventoryTransactions.Add(transaction);
dbContext.SubmitChanges();

Linq-to-SQL isn't clever enough to see this as needing to be added to the previously cached list of in memory items in InventoryTransactions.

WILL add the new InventoryTransaction to future in memory queries

var transaction = new InventoryTransaction()
                 {
                     AdjustmentDate = currentTime,
                     QtyAdjustment = 5
                 };

inventoryProduct.InventoryTransactions.Add(transaction);
dbContext.SubmitChanges();

Wherever possible use the collections in Linq-to-SQL when creating relationships and not the IDs.

In addition as Jon says, try to minimize the scope of a DataContext as much as possible.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1501626

The simplest way would be to use a new DataContext - given that most of what the context gives you is caching and identity management, it really sounds like you just want a new context. Why did you want to create just the one and then hold onto it?

By the way, for simple queries like yours it's more readable (IMO) to use "normal" C# with extension methods rather than query expressions:

public IEnumerable<Orders> NewOrders()
{
    return dataContext.Orders.Where(order => order.Status == 1);
}

EDIT: If you never want it to track changes, then set ObjectTrackingEnabled to false before you do anything. However, this will severely limit it's usefulness. You can't just flip the switch back and forward (having made queries between). Changing your design to avoid the singleton context would be much better, IMO.

Upvotes: 10

Related Questions