Inserting item in LINQ to SQL datacontext - doesn't appear until application restart

Question

I have an ASP.NET MVC (using the release candidate) application that works with a class library that among other things uses LINQ to SQL for data persistance.

One of the thing the app/db maintains, is the concept of a "Folder" - like a disk folder, but with a hierarchy that only exists in the database. Other database objects live in these folders, seen from the perspective of the user.

Each class in the class library has its own static reference to my DataContext object. In addition, the MVC controller each has it's own DataContext object.

I have two Actions returning JSON data. One is a "GetFoldersJSON" which returns the folder structure in a format suitable for a select (dropdown) list. The other one is "AddFolderJSON" which accepts some form data, inserts a new Folder into the database, and then returns GetFoldersJSON() for sending the new folder list to the client. The client uses AJAX with these actions to refresh the dropdownlists once a new folder is created.

The flow of this operation is as such:

1. AddFolderJSON() action - Get the form information with new folder name, parent folder etc.

2. Create new Folder object. Perform a dataContext.Folders.InsertOnSubmit(newFolder); Perform a dataContext.SubmitChanges(); Perform a dataContext.Refresh(OverwriteThingy, dataContext.Folders); return result of GetFoldersJSON()

3. GetFoldersJSON() uses a function which resides in the Folder definition - a partial class extending the OR-mapped folder object. This function recursively adds folders and subfolders to a flat list using code like this:

   var rootFolders = from f in db.Folders
                     where f.ParentFolder == null
                     orderby f.name
                     select f;
   

db is of course the local, static DataContext reference.

Then a simple loop:

    foreach (var fldr in rootFolders)
    {
        AddFolderContents(list, fldr);
    }

The AddFolderContents() function then adds the current folder to the list, and proceeds to call itself for each subfolder of the current folder, thus creating a hierarchy.

This works fine for data already in the database, but when we run this procedure after creating a new Folder, the new folder does not show up. If we refresh the page, it still doesn't show up. It doesn't actually show up until the application is restarted.

I have tried to do Refresh() calls just about everywhere, and it seems to have no effect in this case.

Is there a way to tell LINQ to SQL "Hey, I know what you're thinking, but DROP IT ALL, and just get ALL the data from the database, NOW!"?

I have a nagging feeling I've heard about this before, but couldn't find it described here.

Answer 1

Is there a way to tell LINQ to SQL "Hey, I know what you're thinking, but DROP IT ALL, and just get ALL the data from the database, NOW!"?

db = new customDataContext();

db is of course the local, static DataContext reference

Yeah, watch out there. ASP.NET is multithreaded and you don't want to share an unsafe-for-threading DataContext instance.

---

It seems as though you are accessing children folders through their parents. DataContext caches the state of objects it has already read and returns those same instances to you on further querying. The parent instance needs to be notified that it has a new child instance. Check out the generated ParentFolder property and see if it notifies the parent folder. If so, you're in business:

child.ParentFolder = parent;

If not, you need to do it this way:

parent.Children.Add(child);

Do not do it by id, the autogenerated code does not notify the parent object in this case.

child.ParentFolderId = parent.Id; //bad, broken, do not do

Do this before your SubmitChanges call.

Answer 2

My first instinct was:-

David B has given you one of the poissibilites, new up the DataContect but there is another.

db.Refresh(System.Data.Linq.RefreshMode.KeepChanges, myObjectsCollection)

myObjectionCollection is Collection of all the Linq objects that you want refreshed from the datatase. (i.e. mostly everthing in your case).

Beware it's sllllooooowwwww! Apparently it's better in EF - see here.

However on more careful re-reading I'm not sure what it going on. Linq should find new records (but not updated records) and they should appear even without a refresh.

Hmmmm....

Answer 3

I have a feeling you aren't using the DataContext object (holding static references) in the way it's meant to be used.

DataContext objects are meant to be used as a "unit of work"... Instantiate it, do your work, dispose of it. Need to do some more work? Instantiate another one, do your work, dispose of it.

In this MSDN page, Microsoft says:

The DataContext is the source of all entities mapped over a database connection. It tracks changes that you made to all retrieved entities and maintains an "identity cache" that guarantees that entities retrieved more than one time are represented by using the same object instance.

In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operations.

Similarly, if you read the bottom of Joseph and Ben Albahari's 10 LINQ Myths page (they are the authors of C# In A Nutshell) you will see this quote about using a static datacontext:

This strategy will result in stale data, because objects tracked by a DataContext instance are not refreshed simply by requerying.

Using a single static DataContext instance in the middle tier of a distributed application will cause further trouble, because DataContext instances are not thread-safe.

The correct approach is to instantiate fresh DataContext objects as required, keeping DataContext instances fairly short-lived.