Append OR subquery in Linq

I am trying to build a simple search against some entities (EF4, if that makes any difference). Passed into my search query is a list of criteria objects. The crieteria object looks like this:

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 the ExpenseCategoryAndTypeCriteria

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

Searching on financial years and claimants needs to be an AND query, then I need the expense categories and expense types to be appended as an OR sub query.

In essence I'm 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've got 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'm currently seeing is that the last expense category requested is the only expense category I get results for. Also, looking at the code, it looks like I would expect this to be building a series of AND queries, rather that the required OR.

Any pointers?

ansaurus

tags:

views:

answers:

Append OR subquery in Linq

related questions