Base solution for your next web application
Open Closed

How to use .Join for repository queries along with WhereIf( #9360


User avatar
0
marble68 created

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)
  • User Avatar
    0
    maliming created
    Support Team

    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)
    );
    
  • User Avatar
    0
    marble68 created

    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

  • User Avatar
    0
    marble68 created

    I found this MS article - I will attempt to use this information to accomplish what I need to do.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/method-based-query-syntax-examples-join-operators

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @marble68

    Did you find a solution ? I think hitting database twice is not a problem. It might be even faster in some cases.