Reputation: 68516
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:
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;
}
Upvotes: 16
Views: 30126
Reputation: 369
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();
Upvotes: 0
Reputation: 4209
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;
}
Upvotes: 24