Extra db queries despite entities already been loaded

Hello,

We just started using Lightspeed in our new Asp.net 3.5 web application project and it seems that in our implementation in some places Lightspeed does make extra database queries even though the necessary data has already loaded.

For instance we assign a list of Participants as the data source of a repeater using a named aggregate to eager load related Messages for those Participants along the way. On the repeater's item data bound event we want get one of the Participant's Messages and the total Message count for that Participant. Setting a breakpoint just before any of these properties/related entities is accessed shows that the Participant entity is fully loaded, including the related message entities. Continuing debugging shows that despite the data was already available (according to the visual studio debugger) a new db query is triggered nevertheless.

We are using the latest nightly built and our unit of work holder is created on the Application_BeginRequest event and disposed again at the Application_EndRequest.

Any suggestions would be highly appreciated.

Oliver

Hello Oliver,

I've had a quick go at reproducing this but our test models are not exhibiting the redundant re-query behaviour.  Could you provide us with a small console project that demonstrates the problem?  (Or if it doesn't happen in a console project, a Web project -- but in this case it would be useful to know if the problem didn't occur when running in the console.)  You can attach a zip file via the Options tab, or if you don't want it to be public mail it to ivan @ the obvious domain name (please remove all binaries first).  Thanks!

Hi Ivan,

Thanks a lot for the quick reply. I tried to narrow it down a bit when exactly the problem occurs and looks like the following SortMessages() function causes the problems. If I call this function in the Page_Load, then later on in the repeater's ItemDataBind the re-querying occurs, if I comment out the call to the function everything works fine as expected.

Any ideas why this could cause the behavior? If it doesn't help I will try to get the source code with a db dump to you.

Cheers,

Oliver

{


        public ArrayList sortedMessages;
        
        public void SortMessages()
        {
            sortedMessages = new ArrayList(Messages.Count);

            IList<Message> rootMessages = Messages.FindAll(m => m.ParentId == null).OrderBy(m=> m.Id).ToList();

            foreach (Message rootMessage in rootMessages)
            {
                sortedMessages.Add(rootMessage);
                rootMessage.IndentLevel = 0;
                CreateTreeRecursive(rootMessage, 0);
            }
        }

        private void CreateTreeRecursive(Message msg, int indentLevel)
        {
            IList<Message> childrenList = Messages.FindAll(m => m.ParentId == msg.Id);

            foreach (Message childMsg in childrenList)
            {
                childMsg.IndentLevel = indentLevel+1;
                sortedMessages.Add(childMsg);
                CreateTreeRecursive(childMsg, indentLevel + 1);
            }
        }
    }

This tentatively looks like it is because you are running an explicit query by ParentId for the messages.  LightSpeed doesn't remember the queries you've done before, so it has to re-run the query: it finds, surprise surprise, that the results are the same as before, so it returns the same entity instances; but the cost of the additional query has already been paid.

If instead you set up a one-to-many association from Message to Message, and navigate to the child collection, then LightSpeed will remember when it has loaded the child collection, and will *not* re-query the database.  For example:

public void SortMessages() { /* as before * }

private void CreateTreeRecursive(Message msg, int indentLevel) {
  IList<Message> childrenList = msg.ChildMessages;  // using the LightSpeed association
  /* rest as before */
}

This results in the same query being issued to the database, but because it is going through the LightSpeed association, LightSpeed is able to mark the child collection as "loaded" and not needing to be reloaded in future -- which it can't do with a "raw" query like the one you're using at the moment.

Note that even with this change you will still perform two queries for the set of root messages, one in the Page_Load and one in the DataBind.  (You won't incur the additional queries to traverse the tree, because the entities returned from the second root query are already loaded with their children.  (Assuming all this is happening within the same unit of work of course.))  To avoid the second root query you would need to cache the results of the first root query, and re-use that rather than re-executing SortMessages().

If this doesn't make sense, or still doesn't help, let me know.