At what point does an IList cause a query to execute in an ORM?

Question

I am using telerik's OpenAccess ORM and I am making some changes to the generated code. For an entity that has a navigation property (a key to another table), these are generated as ILists like the following:

private IList<SystemUser> _SystemUsers = new List<SystemUser>();
public virtual IList<SystemUser> SystemUsers 
{ 
    get
    {
        return this._SystemUsers;
    }
}

First, at what point is SystemUsers actually queried against the database? It is an IList, so I would have thought that this would be executed against the database on object creation, but I know that isn't the case.

What I want to do is filter out deleted items on all my generated navigation properties and I have been making changes to the t4 template to do this with the following code:

private IList<SystemUser> _SystemUsers = new List<SystemUser>();

public virtual IList<SystemUser> SystemUsers 
{ 
    get
    {
        if (!Entities.IncludeDeletedEntities)
        {
            var currentContext = Entities.GetContext(this);
            ContextChanges changes = currentContext.GetChanges();
            IList<SystemUser> deletedItems = changes.GetDeletes<SystemUser>();
            return this._SystemUsers.Except(deletedItems).ToList(); //Question is here
        }

        return this._SystemUsers;
    }
}

Essentially this just returns the collection minus the ones that are marked for deletion. My concern is about the .ToList() and when it is executed. I don't want to slow my queries down by causing that ToList() to query against the database every time I access SystemUsers, but I also want to filter my items. I'm not even sure if that ToList() would cause a database hit. I'm not sure when the code would actually hit the database, so I am looking looking for some help/tips to filter out my deleted items without it hitting the database until I use SystemUsers in my code by either adding further filters (where clauses, etc).

score 1 · Answer 1 · answered Mar 12 '13 at 13:21

I can't say for sure with the Telerik tools, but would suspect that they are somewhat similar to EF and LINQ to SQL which evaluate any expressions on the call to GetEnumerator (which is called by ToList internally). To test this yourself, use a profiling tool to identify exactly where the database requests are being made. SQL Profiler and Intellitrace should work for profiling. There are other commercial profilers available as well, but I'm not sure what's out there for Telerik Open Access. You might want to check on their forums.

score 0 · Accepted Answer · answered Mar 12 '13 at 14:14

I don't know about OpenAccess but I think roughly the same principles apply as entity framework. From that background I only see major issues with your code.

The first objection is from an architectural point of view. Your entity object is not supposed to know about the existence of a context.

Other objections are technical.

As you already pointed out your code will be executed each time SystemUsers is addressed. The ToList() will always cause a database hit. I'm not sure what happens if you remove the ToList(), that depends on what GetChanges and GetDeletes do. I don't know. But at the very least it would cause an additional database hit each time SystemUsers is enumerated.
I assume that, like EF, OpenAccess also supports lazy loading of collections that are marked virtual. Probably it creates a proxy type that is derived from the entity type and that adds wiring to navigation properties in order to implement lazy loading. As the property only has a getter I assume that OpenAccess populates a collection by Adding items to it. This causes all kinds of side effects:
1. It would mean that the base property is accessed (running the query) each time an item is added to it.
2. Your code returns a temporary list. Any added item will be gone next time you address the property!! The collection will never get populated.
Finally it may cause unpredictable behaviour when a second reader is opened while your object is being materialized.

So I think you should solve the problem of soft deletes by a repository that returns entities with or without the ones that are marked for delete. Or maybe you can create a calculated (unmapped) property that returns the SystemUsers without the "deleted" ones.

You make some good points. I think I will stop going down that dangerous path. — Justin, Mar 12 '13 at 14:34

At what point does an IList cause a query to execute in an ORM?

2 Answers2