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).

Answer 1

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.