DbLinq - Cache problem

Question

I'm using linq to sql for MySql (using DbLinq) in an ASP.NET MVC website. I have a weird caching problem. Consider the following methods in my Repository class:

public IEnumerable<Message> GetInbox(int userId)
{
  using(MyDataContext repo = new MyDataContext(new MySqlConnection("[Connectionstring]")))
  {
    return repo.Messages.Where(m => m.MessageTo == userId);
  }
}

public IEnumerable<Message> GetOutbox(int userId)
{
  using (MyDataContext repo = new MyDataContext(new MySqlConnection("[Connectionstring]")))
  {
    return repo.Messages.Where(m => m.MessageFrom == userId);
  }
}

'MyDataContext' is the by DbLinq generated mapping to my database, which inherits from DataContext. I'm not reusing the datacontext here (the above code looks a bit silly but I wanted to make absolutely sure that it was not some datacontext / mysqlconnection re-using issue).

What happens is, whichever of the two methods I call, with whatever userId, the results stay the same. Period. Even though I can see that repo.Messages has more than 10 results, with varying MessageFrom and MessageTo values, I only get the first-queried results back. So if I call GetInbox(4374) it gives me message A and message B. Calling GetInbox(526) afterwards still gives me message A and B, even though there are messages C and D who do have a userId of 526. I have to restart the application to see any changes.

What's going on here? I'm sure I'm doing something so stupid that I'm going to be ashamed when someone points it out to me. If I'm not doing something very stupid, then I find this issue very strange. I read about not reusing DataContext, but I am not. Why this caching issue? Below is my controller code, but I doubt it matters:

[Authorize]
public ActionResult Inbox(int userId)
{
  Mailbox inbox = new Mailbox(userId, this.messageRepository.GetInbox(userId));
  return PartialView("Inbox", inbox);
}

Though there are similar questions on SO, I haven't found an answer to this exact question. Many thanks!

UPDATE: changing the code to: return repo.Messages.ToList().Where(m => m.MessageFrom == userId); fixes it, it works fine then. Seems like some cache problem. However, I of course don't want to fix it that way. Changing the code so that the datacontext is not disposed after the query does not fix the problem.

Answer 1

Caching in LINQ-to-SQL is associated with the DataContext, and is mainly limited to identity caching - in most cases it will re-run a query even if you've done it before. There are a few examples, like .Single(x=>x.Id == id) (which has special handling).

Since you are clearly getting a new data-context each time, I don't think that is the culprit. However, I'm also slightly surprised that the code works... are you sure that is representative?

LINQ's Where method is deferred - meaning it isn't executed until you iterate the data (for example with foreach). But by that time you have already disposed the data-context! Have you snipped something from the example?

Also - by giving it a SqlConnection (that you don't then Dispose()), you may be impacting the cleanup - it may be preferable to just give it (the data-context) the connection string.

Answer 2

I wrote some pretty similar code that seems to work fine. The only difference is that as Marc suggests, I'm passing in the connection string and calling ToList on the Where method. My Database is not automatically generated but derives from DataContext. The code is below.

class Program
{
    static void Main(string[] args)
    {
        List<Item> first = GetItems("F891778E-9C87-4620-8AC6-737F6482CECB").ToList();
        List<Item> second = GetItems("7CA18DD1-E23B-41AA-871B-8DEF6228F96C").ToList();
        Console.WriteLine(first.Count);
        Console.WriteLine(second.Count);
        Console.Read();
    }

    static IEnumerable<Item> GetItems(string vendorId)
    {
        using (Database repo = new Database(@"connection_string_here"))
        {
            return repo.GetTable<Item>().Where(i => i.VendorId.ToString() == vendorId).ToList(); ;
        }
    }
}

Answer 3

Start of by writing a test. This will tell you wether Linq2Sql is behaving correctly. Something like:

var inboxMessages = this.messageRepository.GetInbox(userId1);
Assert.That(inboxMessages.All(m => m.MessageTo == userId1);

inboxMessages = this.messageRepository.GetInbox(userid2);
Assert.That(inboxMessages.All(m => m.MessageTo = userid2);

If that succeeds, you should really check wether it's the deferred execution that's causing problems. You should enumerate inboxMessages right away.

Another thing that might be causing trouble, is the fact that you start enumerating when the datacontext is already disposed. The only way to solve this, is not to dispose it at all (and rely on the GC cleaning it up when it goes out of scope), or come up with a custom IDisposable object, so you can put a using around it. Something like:

using(var inboxMessages = this.messageRepository.GetInbox(userId1))
{
    Assert.That(inboxMessages.All(m => m.MessageTo == userId1);
}

Answer 4

Well, it seemed that it was a problem with DbLinq. I used source code from 3 weeks old and there was an apparant bug in QueryCache (though it has always been in there). There's a complete thread that covers this here.

I updated the dblinq source. Querycache is now disabled (does imply a performance hit) and well at least now it works. I'll have to see if the performance is acceptable. Must confess that I'm a bit baffled though as what I'm trying to do is a common linq2sql pattern. Thanks all.

Answer 5

I'd avoid using DBLinq for production code... many of Linq-To-SQL's features aren't implemented, and walking through the source code shows a low level of maturity... many of the methods are not implemented or marked as "unterminated".

...you've been warned!