Add OR subquery to Linq

Question

I am trying to create a simple search on multiple objects (EF4, if that matters). My search query includes a list of criteria objects. The crieteria object is as follows:

public class ClaimSearchCirtieria
{
    public Guid? FinancialYear { get; set; }
    public bool AllClaimants { get; set; }
    public IList<Guid> ClaimantIds { get; set; }
    public bool AllExpenseCategories { get; set; }
    public IList<ExpenseCategoryAndTypeCriteria> EpenseCategoryAndTypes { get; set; }
}

And ExpenseCategoryAndTypeCriteria

public class ExpenseCategoryAndTypeCriteria
{
    public Guid ExpenseCategory { get; set; }
    public bool AllTypesInCatgeory { get; set; }
    public IList<Guid> ExpenseTypes { get; set; }
}

Search by fiscal years and applicants should be an AND query, then I need expense categories and expense types that should be added as an additional OR query.

In essence, I am trying to do:

select * 
from claims
where <financial year> AND <Claimants> AND (expense type 1 OR expense type 2 or expense category X)

So far I have this:

public PagedSearchResult<Claim> Search(ClaimSearchCirtieria searchCriteria, int page, int pageSize)
{
    var query = All();

    if (searchCriteria.FinancialYear.HasValue)
    {
        query = from claim in query
                where claim.FinancialYearId == searchCriteria.FinancialYear
                select claim;
    }

    if (!searchCriteria.AllClaimants)
    {
        query = from claim in query
                where searchCriteria.ClaimantIds.Contains(claim.ClaimantId)
                select claim;
    }

    if (!searchCriteria.AllExpenseCategories)
    {
        foreach (var item in searchCriteria.EpenseCategoryAndTypes)
        {
            if (item.AllTypesInCatgeory)
            {
                //Just search on the category
                query = query.Where(claim =>
                    (from transaction in claim.ClaimTransactions
                     where item.ExpenseCategory == transaction.ExpenseType.ExpenseCategoryId
                     select transaction).Count() > 0
                );
            }
            else
            {
                //Search for the specified types
                query = query.Where(claim =>
                    (from transaction in claim.ClaimTransactions
                     where item.ExpenseTypes.Contains(transaction.ExpenseTypeId)
                     select transaction).Count() > 0
                );
            }
        }
    }

    return PagedSearchResult<Claim>.Build(query, pageSize, page);
}

What I see now is that the last category of expenses requested is the only category of expenses for which I get results. Also, looking at the code, it seems like I would expect this to build a series of I-requests, namely the required OR.

Any pointers?

Answer 1

LINQKit PredicateBuilder. AsExpandable() Entity Framework.