Hello,
I have a need for cross-tenant queries for certain "super users" but am unsure how to best set up the structure without resorting to a bunch of hacks. I will describe the scenario and I hope that it makes sense and that someone may have an idea of how to approach the design.
We are using aspnetzero in a multi-tenant setup with 1 database per school district. In some cases, we want to roll up multiple districts for "state-level" views. Our plan was to create a tenant per state, in addition to the database for each district. This would allow districts to stay independent but also allow us the flexibility to have "super districts" that had reports that ran across multiple districts.
For example, if we wanted to run a query to get all of the students in the state of Oregon that got an "A" on an assessment, we'd have an application service method similar to the following (pseudo code):
public async Task<OutputDto_StudentAssessmentResults> GetAssessmentResults(InputDto_SelectedDistricts input)
{
var allResults = new List<ResultList>();
foreach(var district in input){
// set repository connection string
var cxnString = helper.getCxnStringForDistrict(district);
repo.setCxn(cxnString);
var results = repo.getResults(district);
allResults.append(results);
}
return new OutputDto_StudentAssessmentResults()
{
StudentResults = allResults
};
}
Hopefully, the above conveys the idea. We are trying to run the same query against multiple tenant databases and return the combined results. However, we are struggling with changing the connection string. It seems that most of the provided functionality sets this automatically and it feels like maybe we aren't thinking of this correctly (feels like a bad code smell). Would appreciate any thoughts on this kind of set up.
Thanks
2 Answer(s)
-
0
You can implement a DbPerDistrictConnectionStringResolver similar to DbPerTenantConnectionStringResolver, together with a Data Filter similar to tenant filter.
Then, your code would be:
foreach (var district in input) { using (_unitOfWorkManager.Current.SetDistrictId(district.Id)) { var results = repo.getResults(district); allResults.append(results); } } -
0
Thanks! This is exactly the type of insight I was looking for!
