sashafierce sashafierce - 5 months ago 17
SQL Question

Linq - conditional Contains()

I have the following method for filtering shops by the criteria specified in parameters:

public int[] GetShopIds(IEnumerable<Guid> OrderCreaatorIds, IEnumerable<Guid> OrderItemCategoryIds, int StatusId)
{
var query = from s in _db.Shops
join o in _db.Orders on s.Id equals o.ShopId
join oi in _db.OrderItems on o.Id equals oi.OrderId
where
OrderCreaatorIds.Contains(o.CreatorId)
&& OrderItemCategoryIds.Contains(oi.CategoryId)
&& (int)o.StatusId == StatusId
select s.Id;

return query.ToArray();
}


The thing is:
OrderCreaatorIds
,
OrderItemCategoryIds
can be
null
and
StatusId
can be
0
. In that case I do not want to have those where clauses, e.g. if
OrderCreaatorIds
is
null
then the query should work as follows:

public int[] GetShopIds(IEnumerable<Guid> OrderCreaatorIds, IEnumerable<Guid> OrderItemCategoryIds, int StatusId)
{
var query = from s in _db.Shops
join o in _db.Orders on s.Id equals o.ShopId
join oi in _db.OrderItems on o.Id equals oi.OrderId
where
OrderItemCategoryIds.Contains(oi.CategoryId)
&& (int)o.StatusId == StatusId
select s.Id;

return query.ToArray();
}


etc.

Unfortunately
where OrderCreaatorIds != null && OrderCreaatorIds.Contains(o.CreatorId)
is not working.

Answer
public int[] GetShopIds(IEnumerable<Guid> OrderCreaatorIds, IEnumerable<Guid> OrderItemCategoryIds, int StatusId)
{
    var query = from s in _db.Shops
        join o in _db.Orders on s.Id equals o.ShopId
        join oi in _db.OrderItems on o.Id equals oi.OrderId
        select new { s = s, o = o, oi = oi };

    if (null != OrderCreaatorIds)
        query = query.Where(x_ => OrderCreaatorIds.Contains(x_.o.CreatorId));

    if (null != OrderItemCategoryIds)
        query = query.Where(x_ => OrderItemCategoryIds.Contains(x_.oi.CategoryId));

    if (0 < StatusId)
        query = query.Where(x_ => (int)x_.o.StatusId == StatusId);

    return query.select(x_ => x_.s.Id).ToArray();
}

Maybe you will have to add some casting to IQueryable<> to make it compilable. I did not check it in compiler.