Filter on multiple checkboxgroups

Question

I have a datagridview where I want to get a list of persons build on the values of the checkboxes (ID's saved in the tag of the checkbox). Those checkboxes are build by code and added to 2 different groupboxes (sgbExcursion and sgbLanguage)

The filter works fine if you select just one excursion (upper checkboxes) and just one language.

If I select 2 languages (lower checkboxes) it is treaten as "or". All persons speaking one of the selected languages are shown instead of only persons who speak both of the selected languages. Same problem with the excursions.

Here I build the list of selected excursions and languages:

List<int> oExcursionID = new List<int>();

foreach (Control oControl in sgbExcursion.Controls)
{
    if (oControl.GetType() == typeof(CheckBox))
    {
        CheckBox oCheckBox = (CheckBox)oControl;
        if (oCheckBox.Checked)
        {
            oExcursionID.Add(int.Parse(oCheckBox.Tag.ToString()));
        }
    }
}

List<int> oLanguageID = new List<int>();

foreach (Control oControl in sgbLanguage.Controls)
{
    if (oControl.GetType() == typeof(CheckBox))
    {
        CheckBox oCheckBox = (CheckBox)oControl;
        if (oCheckBox.Checked)
        {
            oLanguageID.Add(int.Parse(oCheckBox.Tag.ToString()));
        }
    }
}

Here I try to get the right values out of the database. Both languages and excursions are saved in a relational table (Person_ID, Exursion_ID) or (Person_ID, Language_ID).

var vGuides = (from oGuideToAdd in clsApplication._oDBConnection.tblGuides
                where ((from oGuideExcursion in clsApplication._oDBConnection.tblGuideExcursions
                        where oExcursionID.Contains(oGuideExcursion.ExcursionID)
                        select oGuideExcursion.GuideID).Contains(oGuideToAdd.ID)
                && (from oGuideLanguage in clsApplication._oDBConnection.tblGuideLanguages
                    where oLanguageID.Contains(oGuideLanguage.LanguageID)
                    select oGuideLanguage.GuideID).Contains(oGuideToAdd.ID))
                select oGuideToAdd).ToList();

What can I change to get the right output?

Answer 1

I believe if you want to have AND instead of OR you will have to do something like:

instead of your inner query:

from oGuideExcursion in clsApplication._oDBConnection.tblGuideExcursions
where oExcursionID.Contains(oGuideExcursion.ExcursionID)
select oGuideExcursion.GuideID

do something like:

public IQueryable<Excursion> GetExcustions(IList<int> excursionIds) 
{
    var query = clsApplication._oDBConnection.tblGuideExcursions;
    foreach(var id in excursionIds)
    {
        query = query.Where(x=>x.ExcursionId == id);
    }
    return query;
}

then you use it as:

var guideIdsFromExcustions = GetExcursions(oExcursionsID)
                                .Select(x=>x.GuideID)
                                .Distinct()
                                .ToList(); //ToList is probably optional

from oGuideToAdd in clsApplication._oDBConnection.tblGuides
where guideIdsFromExcustions.Contains(oGuideToAdd.GuideID)

And you do the same for languages. The idea is that Contains(...) generates OR (in fact it generates IN, but the result is the same), when a series of Where clauses are generated into AND statements.

I cannot encourage you more to separate huge unreadable query statements into a set of simple small ones. Then you can combine them into one final query in a readable form so it is easy to change, maintain, fix bugs.

Answer 2

The problem is that the line where oLanguageID.Contains(oGuideLanguage.LanguageID) will return true if only one of the languages is spoken.

You will need to check if the intersection of required languages with spoken languages is empty.

You can use something like !subset.Except(superset).Any(); where subset are the spoken languages and superset the required ones.

Check this link for more information Determine if a sequence contains all elements of another sequence using Linq