8.9/ jQuery
If I have repositories I wish to join, and still leverage WhereIf for filtering, what's the ANZ way?
For example:
Suppose we have this:
var query = _lookup_employeeRepository.GetAll().WhereIf(
!string.IsNullOrWhiteSpace(input.Filter),
e=> e.email != null && e.email.Contains(input.Filter)
);
And what I want to do is something like this:
var query = from e in _lookup_employeeRepository.GetAll()
join ed in _employeeDepartmentRepository.GetAll() on e.Id equals ed.EmployeeId
join du in _departmentUserRepository.GetAll() on ed.DepartmentId equals du.DepartmentId
where e.TenantId == tId && du.UserId == uId && (e.firstName.Contains(input.Filter) || e.lastName.Contains(input.Filter))
select e;
However - instead of doing it that way, use the WhereIf for filtering..
So I reviewed this: https://docs.microsoft.com/en-us/dotnet/api/system.linq.enumerable.join?view=netcore-3.1
So I'd like to use Whereif, so my query would look something like:
var query2 = _lookup_employeeRepository.GetAll()
.Join(_employeeDepartmentRepository.GetAll(), Id => EmployeeId, ) // <----- How do I do this?
.WhereIf(
!string.IsNullOrWhiteSpace(input.Filter), e =>
e.firstName.Contains(input.Filter) || e.lastName.Contains(input.Filter)
);
Do I have to take the tables .toList() first? If so, I'll do it as per my second example in this (using from e in ...)
4 Answer(s)
-
0
hi
query is IQueryable(TEntity) , I think you can directly join query on it.
var query = _lookup_employeeRepository.GetAll().WhereIf( !string.IsNullOrWhiteSpace(input.Filter), e=> e.email != null && e.email.Contains(input.Filter) ); -
0
Yes, that's what I'm trying to figure out how to do.
Microsoft docs say:
Join<TOuter,TInner,TKey,TResult>(IEnumerable<TOuter>, IEnumerable<TInner>, Func<TOuter,TKey>, Func<TInner,TKey>, Func<TOuter,TInner,TResult>)
I found this example on Stack Overflow which looks promising: https://stackoverflow.com/questions/19644617/linq-multiple-join-iqueryable-modify-result-selector-expression
here's what I am doing for now:
var uId = AbpSession.UserId; var depList = (from ed in _employeeDepartmentRepository.GetAll() join du in _departmentUserRepository.GetAll() on ed.DepartmentId equals du.DepartmentId where du.UserId == uId select ed.EmployeeId).ToList(); var query = _lookup_employeeRepository.GetAll() .WhereIf( !string.IsNullOrWhiteSpace(input.Filter), e => e.firstName.Contains(input.Filter) || e.lastName.Contains(input.Filter) ) .Where(e => depList.Contains(e.Id));Not the most elegant, and it hits the database twice.
Ultimately, I may have to avoid using WhereIf and just test if there's a filter and create one or two queries.
Something like:
If input.filter then Query with a filter else Query without a filter
-
0
I found this MS article - I will attempt to use this information to accomplish what I need to do.
-
0
Hi @marble68
Did you find a solution ? I think hitting database twice is not a problem. It might be even faster in some cases.
