I need a little help with a filtering problem which I am having. I want to return only the 'Neurons' which have 'Interventions' matching certain criteria. I think I should use FieldCompareSetPredicate, but I just can't get it to work properly. Basically, I want to write the following statement "Neurons.Where(neuron => neuron.Interventions.Contains(intervention == "Rimonabant") == true & neuron.Interventions.Contains(intervention == "CPCCOet") == true)" as a PredicateExpression which I can pass into adaptor.FetchEntityCollection()

Tables: 'Neurons' linked to 'Interventions' (one-to-many) on 'NeuronID' field.

I try this, but it doesn't return anything (no Neurons returned, when I expect several)

            PredicateExpression INTERVENTION_FILTERS= new PredicateExpression();
            INTERVENTION_FILTERS.AddWithAnd((InterventionsFields.Intervention == "CPCCOet"));
            INTERVENTION_FILTERS.AddWithAnd((InterventionsFields.Intervention == "Rimonabant"));


FieldCompareSetPredicate(
                    NeuronsFields.NeuronId,
                    null,
                    InterventionsFields.NeuronId,
                    null,
                    SetOperator.IN,
                    INTERVENTION_FILTERS,
                    null,
                    string.Empty,
                    0,
                    null
                )

Sorry this is long and perhaps unclear, but any help would be greatly appreciated.

Pete

        PredicateExpression INTERVENTION_FILTERS= new PredicateExpression();
        INTERVENTION_FILTERS.AddWithAnd((InterventionsFields.Intervention == "CPCCOet"));
        INTERVENTION_FILTERS.AddWithAnd((InterventionsFields.Intervention == "Rimonabant"));

The above predicate can never be true

You are filtering with an AND on 2 different values for the same field. I guess you need to use OR as follows:

            PredicateExpression INTERVENTION_FILTERS= new PredicateExpression();
            INTERVENTION_FILTERS.Add((InterventionsFields.Intervention == "CPCCOet"));
            INTERVENTION_FILTERS.AddWithOr((InterventionsFields.Intervention == "Rimonabant"));

Walaa wrote:

        PredicateExpression INTERVENTION_FILTERS= new PredicateExpression();
        INTERVENTION_FILTERS.AddWithAnd((InterventionsFields.Intervention == "CPCCOet"));
        INTERVENTION_FILTERS.AddWithAnd((InterventionsFields.Intervention == "Rimonabant"));

The above predicate can never be true

You are filtering with an AND on 2 different values for the same field. I guess you need to use OR as follows:

            PredicateExpression INTERVENTION_FILTERS= new PredicateExpression();
            INTERVENTION_FILTERS.Add((InterventionsFields.Intervention == "CPCCOet"));
            INTERVENTION_FILTERS.AddWithOr((InterventionsFields.Intervention == "Rimonabant"));

Thanks Walaa for getting back to me.

I agree that the predicate can never be true when applied to to the same 'Intervention'. But how do I get the Predicate to be applied to the collection of 'Interventions' associated with each 'Neuron'?

The statement below does what I want (but is not done on the DB, which I what I want).

return neurons.Where(a => a.Interventions.Any(b => b.Intervention == "Rimonabant") && a.Interventions.Any(c => c.Intervention == "CPCCOet"));

The above statement correctly returns only 'Neurons' where both Rimonabant and CPCCOet 'Interventions' are associated to the 'Neuron'.

Thanks again, pete

Please correct me if I'm wrong, but it seems to me you want to execute the following SQL:

SELECT * FROM Neurons
WHERE NeuronId IN (SELECT NeuronId FROM Interventions WHERE Intervension = 'CPCCOet' OR Internvension = 'Rimonabant') 

Am I correct? If not please post the required SQL statement.

Also please post a complete code snippet of what you are trying.

Walaa wrote:

Please correct me if I'm wrong, but it seems to me you want to execute the following SQL:

SELECT * FROM Neurons
WHERE NeuronId IN (SELECT NeuronId FROM Interventions WHERE Intervension = 'CPCCOet' OR Internvension = 'Rimonabant') 

Am I correct? If not please post the required SQL statement.

Also please post a complete code snippet of what you are trying.

The SQL statement you suggest is not what I am looking for. I tested the SQL you suggested, and it returns **Neurons **containing either Rimonabant OR CPCCOet Interventions. I want an SQL statement (and the appropiate LLBLGEN predicate expression), what will return **Neurons **that contain both Rimonabant AND CPCCOet Interventions (remembering that the **Neurons **and **Interventions **datatables are linked one-to-many). Unfortunately I'm not certain how to write it in SQL either.

I'm really appreciating the discussion thanks Walaa....this problem has been driving me crazy for a couple of days now.

Pete

Ok, so after some more playing around in SQL and then back in LLBLGEN, I think I have solved it - although I think there must be a faster query.

            p.PredicateExpression.Add(new
                FieldCompareSetPredicate(
                    NeuronsFields.NeuronId,
                    null,
                    InterventionsFields.NeuronId,
                    null,
                    SetOperator.In,
                    (InterventionsFields.Intervention == "Rimonabant"),
                    null,
                    string.Empty,
                    0,
                    null
                ));

            p.PredicateExpression.AddWithAnd(new
                FieldCompareSetPredicate(
                    NeuronsFields.NeuronId,
                    null,
                    InterventionsFields.NeuronId,
                    null,
                    SetOperator.In,
                    (InterventionsFields.Intervention == "CPCCOet"),
                    null,
                    string.Empty,
                    0,
                    null
                ));

The SQL equivalent is...

SELECT *
FROM Neurons
WHERE (Neurons.NeuronID) In (SELECT NeuronId FROM Interventions WHERE Intervention = 'Rimonabant') AND (Neurons.NeuronID) In (SELECT NeuronId FROM Interventions WHERE Intervention = 'CPCCOet');

Is this really the best way to achieve this? Is does the job for the moment so I am happy

Looks OK to me