How do I just LINQ Join() to link two IQueryables?

Question

I have two IQueryables:

Ingredient:

IngId
Description

AvailableIngredient:

IngId

I already have an IQueryable for Ingredient:

var ingQuery = from i in context.Ingredients
               select i;

How can I add a join to his so it filters by AvailableIngredient (i.e. an Inner Join)? I know how to do it if I had to join all the time, i.e. from... join context.Available... etc), but the Join is conditional, so I need to use the other syntax:

if (filterByAvailable)
{
   IQueryable<Available> availableQuery = GetAvailableIngredientQuery(context);
   ingQuery = ingQuery.Join(...); // Can I use this to join to the query?
}

This may not be the right method, so this is what I want to do:

• GetAvailableIngredientQuery returns the available ingredients query, i.e. 3000 of 6000 (but it doesn't enumerate the results yet as it's returned as an IQueryable from EF)
• Join the availableQuery to the ingQuery, so there's an Inner Join between the two queries

EDIT:

This is the code I'm currently using (very fast), but it means duplicated code:

IQueryable<Ingredient> query;
if (filterByAvailable)
{
    IQueryable<Available> availableQuery = GetAvailableIngredientQuery(context);
    query = from item in context.Ingredients
               // Quite a few `where` clauses and stuff
            join t in availableQuery on item.IngId equals t.IngId
            select item;
}
else
{ 
    query = from item in context.Ingredients
               // The SAME `where` clauses and stuff as above
            select item;
}

Fishy

Answer 1

Use the first query as the source of the subsequent query.

IQueryable<Ingredient> query = from item in context.Ingredients
                             // Quite a few `where` clauses and stuff
                               select item;

if (filterByAvailable)
{
    IQueryable<Available> availableQuery = GetAvailableIngredientQuery(context);
    query = from item in query
            join t in availableQuery on item.IngId equals t.IngId
            select item;
}

Optime

Thanks, definately looks like it'll work (I don't know why I did try this)! Unfortunately I'm in a totally unbuildable state at the moment, so I'll try in a bit. – Fishy

Answer 2

I have implemented this logic for pagination with Where clause and anyone can use it for pagination and other purpose. ResponseDTO<List> response = new ResponseDTO<List>();

        var requests =  (from r in _context.Requests.Where(x => x.IsActive && x.EmployeeId == search.EmployeeNumber && x.ServiceId == search.ServiceId)
                              join s in _context.Services
                              on r.ServiceId equals s.Id
                              join b in _context.RequestStatus
                              on r.RequestStatusId equals b.RequestStatusId
                              select new GetRequestHistoryDto
                              {
                                  Id = r.Id,
                                  ReferenceNumber = r.ReferenceNumber,
                                  ServiceId = r.ServiceId,
                                  ServiceNameEn = s.NameEn,
                                  ServiceNameAr = s.NameAr,
                                  RequesterId = r.RequesterId,
                                  RequestStatusId = r.RequestStatusId,
                                  RequestStatusName = b.RequestStatusEn,
                                  CreatedOn = r.CreatedOn
                              });
        if (requests != null)
        {
            if (!string.IsNullOrEmpty(search.SearchKey))
            {
                requests = requests.Where(x => x.ReferenceNumber.ToLower().Contains(search.SearchKey.ToLower())
                || x.ServiceId.ToString().Contains(search.SearchKey)
                || x.ServiceNameEn.ToString().ToLower().Contains(search.SearchKey.ToLower())
                || x.ServiceNameAr.ToString().ToLower().Contains(search.SearchKey.ToLower())
                || x.RequesterId.ToString().ToLower().Contains(search.SearchKey.ToLower())
                || x.RequestStatusName.ToString().ToLower().Contains(search.SearchKey.ToLower()));
            }
            if (search.IsSortDesc)
            {
                requests = requests.OrderByDescending(e => e.CreatedOn);
            }
            else
            {
                requests = requests.OrderBy(e => e.CreatedOn);
            }
        }

        response.TotalRecords = requests.Count();

        if (search.pageNumber > 0 && search.pageSize > 0)
        {
            requests = requests.Skip((search.pageNumber - 1) * search.pageSize).Take(search.pageSize);
        }
        requests = requests.OrderByDescending(x => x.CreatedOn);
        response.Data = await requests.ToListAsync();

Slave