Understanding IQueryable
An object of a class that implements IEnumerable<...> represents a sequence of similar objects. You can ask for the first element, and when you have an element you can ask for the next element of the sequence, as long as there is one.
At its lowest level, this enumeration of elements is done using GetEnumerator and repeatedly calling MoveNext / Current, like this:
IEnumerable<T> myEnumerable = ...
// Get the enumerator and repeatedly ask for the next element of the sequence
using (Enumerator<T> myEnumerator = myEnumerable.GetEnumerator())
{
while (myEnumerator.MoveNext())
{
// there is a next element in the sequence, get it and process it
T element = myEnumerator.Current;
Process(element);
}
}
foreach and LINQ methods that don't return IEnumerable<...> like ToList, ToDictionary, FirstOrDefault, Sum, Any, etc, will deep down call GetEnumerator and MoveNext / Current.
Although an object of a class that implements IQueryable looks like an Enumerable sequence, it doesn't represetn the sequence itself. It represents the potential to create an enumerable sequence.
To be able to fetch the data to create the enumerable sequence, the IQueryable holds an Expression and a Provider.
The Expression represents what data must be fetched in some generic format. The Provider knows who holds this data (usually a Database management system) and what language is used to communicate with this DBMS (usually something like SQL).
As long as you use LINQ methods that return IQueryable<...>, only the Expression is changed. There is no communication with the database, no data is fetched. These methods are not costly (= don't need a lot of processing power). Concatenating these LINQ methods is fast. You don't need to use async-await for this.
On the other hand, when you use foreach, or one of the LINQ methods that don't return IQueryable<...>, like ToList, Any, Count, FirstOrDefault, then deep inside GetEnumerator and MoveNext / Current are called.
When GetEnumerator is called, the Expression is sent to the Provider, who will translate the Expression into a SQL query and send the query to the DBMS. The returned data is converted into an eneumerable sequence of which the Enumerator is returned.
Some IQueryable objects are smarter and wait with fetching the data until the first MoveNext, to prevent fetching data that is never accessed.
Even smarter IQueryable objects fetch the data "per page". Only the first few objects (100?) are fetched, so if you stop enumerating after the third element, not all 1000 elements are fetched. Only after you enumerate after the 100th element, the next page of 100 elements is fetched.
Back to your question
Now that you understand IQueryable, you might gather that it's useless to use async methods that return the IQueryable. After all: only the Expression and the Provider are created, there is no communication with the DBMS.
You'll also understand that you won't see async methods for LINQ methods that return IQueryable<...>, like WhereAsync. You will find methods like GetEnumeratorAsync, foreachasync, ToListAsync and FirstOrDefaultAsync, these methods really need to access the DBMS to create the return value.
var organizations = (await _organizationRepository.WithDetailsAsync())
.Where(o => o.OrganizationType == organizationType)
Alas you didn't mention the return value of WithDetailsAsync. As stated before: if the database is not contacted, why make it an awaitable method? It seems that after the await the data is already fetched. The return value is represented as an IEnumerable<...>.
OR
You are using the async await in an a way that is not meant for IQueryable. If you really didn't mean to materialize data before your final ToList, then your methods should return IQueryable<...> not Task<IQueryable<...>> or something similar.
You use two repositories OrganisationRepository and TenantRepository.
Do they use the same database connection (via DbContext), or two different databases?
If these repositories use different databases, then you do have to materialize the data before you can join them. In that case await GetQueryableAsync should return IEnumerable<...>, not IQueryable.
If they are from the same database, then the problem is that OrganisationRepository and TenantRepository each hold their own instance of a DbContext. And that is your problem.
The error says, that you are try to join object from different DbContext objects
When creation these Repositories, these classes should not create the DbContext, the creator of these classes should tell both Repositories which DbContext to use.
This is known as Dependency Injection. If you are not familiar with this concept, consider to read some background information about it.
The idea is, that you won't let the repository create the DbContext, but that you create the DbContext, and that you tell each repository object what DbContext object to use. Something like this:
using (MyDbContext dbContext = ...)
{
var organisationRepository = new OrganisationRepository()
{
DbContext = dbContext,
};
var tenantRepository = new TenantRepository
{
DbContext = dbContext,
};
var stateRepository = new StateRepository
{
DbContext = dbContext,
};
Now let the repositories only create IQueryable<...>, don't execute the queries yet!
Continuing within the using statement:
IQueryable<Organization> organizations = _organizationRepository.WithDetails()
.Where(organization => organization.OrganizationType == organizationType);
IQueryable<Tenant> tenants = _tenantRepository.GetQueryable();
IQueryable<OrganizationState> organizationStates = _organizationStateRepository.GetQueryable())
.Where(orgState => orgState.StateId == currentStateId);
IQueryable<State>states = _stateRepository.GetQueryable();
// LinkOptions is also an IQueryable:
var linkOptions = (from organization in organizations
join tenant in tenants on organization.TenantId equals tenant.Id
join organizationState in organizationStates on organization.Id equals organizationState.OrganizationId
join state in states on organizationState.StateId equals state.Id
where tenant.Name.ToLower().Contains(searchTermLower)
select new GetLinkedOrganizationsDto
{
Id = tenant.Id,
Name = tenant.Name,
Address = organization.Address,
City = organization.City,
StateId = state.Id,
State = state.Name,
PostalCode = organization.PostalCode,
Country = organization.Country,
PhoneNumber = organization.PhoneNumber
})
.GroupBy(dto => dto.Id)
.Select(g => g.First());
Until now only IQueryables are created, because you never needed any data. The Joins, GroupBy and Select only changed the Expression in the one and only Provider you created..
Just before the return is the first moment you access the DbMs. Because you use ToList, GetEnumerator is called, which sends the Expression to the Provider. The Provider translates the Expression into SQL and executes the query. The fetched data is returned data as an enumerator.
This is the only place where the database is contacted. Use async-await here:
var fetchedLinkOptions = await linkOptions.ToListAsync();
return new ListResultDto<GetLinkedOrganizationsDto>(linkOptions);
}
// end of using statement, the DbContext is Disposed and cannot be used anymore
Conclusion
Use one DbContext
If you want to combine data from one database before you meterialize it, make sure that the classes use the same DbContext. Use Dependency Injection for this.
No async await for methods that return IQueryable
Postpone materializing the data as long as possible. Therefore no async methods are needed until the moment that you materialize: ToListAsync. Note that if your repository doesn't materialize data it won't need async methods anymore.
