Query on through association

Hi

What's the appropriate syntax for querying based on a through association?

For example, I have an entity "Person" which has a through association with entity "Region".  Each person can have multiple regions.

How do I query for persons who are associated with a particular region?

uow.person.Where(p => p.Region.ID == 20) ... or something like that?

And would the approach be any different if I add an additional through association to query on?  eg. So I could get persons from region X who are assigned to task Y.

Cheers and thanks,

Danzig

We don't directly support querying on through associations.  Instead, you need to query on the through entity, which will be called PersonRegion or something like that, and select the associated entity (the Person).

E.g.

var peopleWithRegion20 = from pr in UnitOfWork.PersonRegions
                                           where pr.RegionId == 20
                                           select pr.Person;

IMPORTANT: This will result in a n+1 query unless the PersonRegion.Person association is eager-loaded!  (I.e. the PersonRegions will be fetched in one query, but each Person will then be loaded separately as you iterate the query; take a look at the SQL log if it's not clear what I'm going on about.)  This is inefficient if there are a large number of results.  You can get around this with eager loading.  If you don't want PersonRegion.Person always to be eager loaded, but you want to avoid the n+1 in this case, you can use a named aggregate (see the WithAggregate operator).

 Cool, thanks Ivan.

Will that approach still work for querying conditionally on multiple properties?

eg. If I've got 5 search fields on my page, any combination of them (or none) can be used to modify the criteria.

So I need something along the lines of...

if (TaskList.SelectedValue != ""){

QueryExpression += Person.PersonTasks.TaskId == myOtherControl.SelectedValue;

}

if (FirstName.Text != ""){

QueryExpression += Person.FirstName.StartsWith(FirstName.Text);

}

Thanks for your help.