How can I query cross tables with Repository Pattern?

Question

In my asp.net mvc 3 application, I'm using the repository pattern. I have 3 entities, Company, Country, City. Each of them has their own repository. Company entity has FoundedCountry and FoundedCity foreign keys. Now in a view, I want to show the company details. In this view I want to view Company details as well as, FoundedCountry name and FoundedCity name. In my opinion I have to handle this with a kind of JOIN query. But I'm stuck at how to achieve this in repository pattern. How can I handle this JOIN in repository pattern?

Thank you.

Answer 1

The repository should have a task-based interface. This means that ORM's, joins etc are inside the repository. The app just sees an interface whtch returns an object that it can use.

This means you don't create a repository around a table (it pretty much defeats the purpose). In your scenario I suggest you have (at least) 2 repositories: one will handle everything related to updating the model and the other will serve only reads (queries).

This means the query repository will return only the data you want (it basically returns view model bits). Of course, the actual tables and joins are an implementation detail of the repository.

Answer 2

Don't construct your repository pattern in a way that is preventing joins! This usually means to use the same ORM context (DataContext/ObjectContext) for all instances associated with the current HTTP request.

I consider it to be an anti-pattern to have a generic IRepository because database access is rarely constrained to a single type of entity at the same time.

You could consider the DataContext/ObjectContext to be a repository by itself.

A last advice: If you don't know what a repository abstraction is good for - don't use one.