Hi,

My code below only works if the parameter "ClientName" is empty, otherwise it returns nothing.

public EntityCollection<ClientEntity> GetAllAvailableParticipants(int EventId, string ClientName)
        {
            IEnumerable<EventParticipantEntity> participants = new EventBLL().GetParticipantsByEventId(EventId);
            int[] participantArray = participants.Select(i => i.ClientId).ToArray();


            EntityCollection<ClientEntity> output = new EntityCollection<ClientEntity>();

            IRelationPredicateBucket bucket = new RelationPredicateBucket();

            bucket.PredicateExpression.Add(new FieldCompareRangePredicate(ClientFields.ClientId, null, participantArray)).Negate = true;

            if (!string.IsNullOrEmpty(ClientName))
            {
                bucket.PredicateExpression.Add(new FieldLikePredicate(ClientFields.Surname, null, "%" + ClientName + "%"));             
            }

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(output, bucket, 0);
            }

            return output;
        }

I basically want this SQL:

SELECT * FROM Table WHERE ID NOT IN (1,2,3,4) AND Column1 LIKE ="%string%"

Please advise and thanks in advance John

Hi John,

jmcjq2 wrote:

if (!string.IsNullOrEmpty(ClientName))
            {
                bucket.PredicateExpression.Add(new FieldLikePredicate(ClientFields.Surname, null, "%" + ClientName + "%"));             
            }

Are you sure that the data in DB satisfy the filter criteria? I mean, your sql code works in SQL analyzer with that parameters?

Then, are you sure you are filtering the correct field? you are using ClientFields.Surname, but What is passed to ClientName parameter?

Examine the Generated SQL to know if everything is ok with that.

daelmo wrote:

Hi John,

jmcjq2 wrote:

if (!string.IsNullOrEmpty(ClientName))
            {
                bucket.PredicateExpression.Add(new FieldLikePredicate(ClientFields.Surname, null, "%" + ClientName + "%"));             
            }

Are you sure that the data in DB satisfy the filter criteria? I mean, your sql code works in SQL analyzer with that parameters?

Then, are you sure you are filtering the correct field? you are using ClientFields.Surname, but What is passed to ClientName parameter?

Examine the Generated SQL to know if everything is ok with that.

Thanks daelmo, ill try and get tracing going .. btw how do you use a Custom Property in a PredicateExpression?

jmcjq2 wrote:

.. btw how do you use a Custom Property in a PredicateExpression?

If by "Custom Property" you mean "a property created by you in your Entity", you cant use it in PredicateExpression. You can only if you are filtering in-memory, but you are filtering on the server. What do you want to filter? because you can use expressions in predicates, with that you can do almost everything.

daelmo wrote:

jmcjq2 wrote:

.. btw how do you use a Custom Property in a PredicateExpression?

If by "Custom Property" you mean "a property created by you in your Entity", you cant use it in PredicateExpression. You can only if you are filtering in-memory, but you are filtering on the server. What do you want to filter? because you can use expressions in predicates, with that you can do almost everything.

I created a property called "ClientName" which is a concatenation of Surname and Firstname in my Client Enitity and I was hoping i could filter using "ClientName".

daelmo wrote:

Hi John,

jmcjq2 wrote:

if (!string.IsNullOrEmpty(ClientName))
            {
                bucket.PredicateExpression.Add(new FieldLikePredicate(ClientFields.Surname, null, "%" + ClientName + "%"));             
            }

Are you sure that the data in DB satisfy the filter criteria? I mean, your sql code works in SQL analyzer with that parameters?

Then, are you sure you are filtering the correct field? you are using ClientFields.Surname, but What is passed to ClientName parameter?

Examine the Generated SQL to know if everything is ok with that.

Hi,

I finally figured out what's causing the problem but yet to get the correct predicateexpression(s) to make it work.

The problem is the negate property is applied to the whole WHERE clause rather than just one of the expressions or in the other case not applied to the whole WHERE clause depending on which expression was last added.

            bucket.PredicateExpression.Add(new FieldCompareRangePredicate(ClientFields.ClientId, null, participantArray)).Negate = true;

             bucket.PredicateExpression.Add(new FieldLikePredicate(ClientFields.Surname, null, "%" + ClientName + "%")).Negate = false;             

So instead of

       WHERE ( NOT ( [BTA_DEVT].[dbo].[Client].[ClientId] IN (1)) AND [BTA_DEVT].[dbo].[Client].[Surname] LIKE '%Smith%')

it generates this

 WHERE ( NOT ( [BTA_DEVT].[dbo].[Client].[ClientId] IN (1) AND [BTA_DEVT].[dbo].[Client].[Surname] LIKE '%Smith%')

The subtle difference is the extra ")" before the AND clause, how do I make the negate property to apply only to the expression it's attach to?

Thanks in advance John

Got it work! didn't realise I can use the prefix "!" rather than the negate property.

Cheers