Match entities in query on through association

Hi

I have entities Clients and Suppliers which both have a through association with Entity Region.

What would the Lightspeed query look like to return Suppliers that share one or regions with a given Client?

eg. - something like:

Client theClient = uow.Client.First(p => p.Id == theClientID);

var theSuppliers = uow.Suppliers.Where(p => p.Regions.Id IN theClient.Regions.Id);

Thanks in advance!

I don't think there's an elegant way to do this. The best I can think of is something like this:

var client = uow.Clients.First(p => p.Id == clientId);
var regionIds = client.ClientRegions.Select(cr => cr.RegionId).ToArray();
var suppliers = uow.SupplierRegions.Where(sr => regionIds.Contains(sr.RegionId))
                                   .Select(sr => sr.Supplier);

(Here ClientRegion and SupplierRegion are the through entities. The reason for using the through entities is to avoid loading unnecessary Region entities, which are likely to be more heavyweight than through entities.)

Note that for this to be efficient, SupplierRegion.Supplier must both eager-loaded (it would also help is Client.ClientRegions was eager-loaded, but this is less crucial). If you don't want to make them eager load by default, use a named aggregate to force them to eager-load for this particular query.

Thanks a lot Ivan, that works perfectly!

Hmmm, maybe it doesn't work perfectly...

It seems like the ".Select(sr => sr.Supplier);" part is just selecting the last supplier record from the db as opposed to suppliers with matching regions. (And it only selects that last one when it should actually select multiple.)

Thoughts?

Each SupplierRegion found in the database should be giving rise to exactly one Supplier entity. If you leave off the Select, how many SupplierRegion entities are returned?

Bah, sorry, I had "regionIds.Contains(sr.Id))" instead of "regionIds.Contains(sr.RegionId))"

I'm a bad man - it works perfectly!

Thanks for your help.