Null Predicate

Posts   
 
    
lbluser
User
Posts: 14
Joined: 18-Apr-2008
# Posted on: 09-Jun-2008 15:18:22   

Hi I want to add the following predicate in predicate expression.

object value = "test";

IPredicateExpression predA = new PredicateExpression();

predA.AddWithOr(new FieldCompareNullPredicate(new EntityField2("Name",new DbFunctionCall("CAST {0} as Varchar",new object[]{value})),null,false));

How to add the custom EntityField2 in predicate?

e.g.

select * from employee where employeeName = 'xyz' and @customParam is null

where @customParam is a parameter passed in our function

please suggest a solution for such predicate

Thanks

lbluser

daelmo avatar
daelmo
Support Team
Posts: 8152
Joined: 28-Nov-2005
# Posted on: 10-Jun-2008 06:48:11   

Sorry, I can't catch your situation. Could you please elaborate more?

Do you want to reuse your custom EntityField2 object in another predicate?

lbluser
User
Posts: 14
Joined: 18-Apr-2008
# Posted on: 10-Jun-2008 07:10:50   

Hi

yes i want to use the custom EntityField2 object in another predicate . Actually first i create a Custom EntityField2 which contains a DateTime value. Now i want to see whether EntityField2 is null or not.

Thanks

lbluser

Walaa avatar
Walaa
Support Team
Posts: 14643
Joined: 21-Aug-2005
# Posted on: 10-Jun-2008 08:03:05   

IPredicateExpression predA = new PredicateExpression();

predA.AddWithOr(new FieldCompareNullPredicate(new EntityField2("Name",new DbFunctionCall("CAST {0} as Varchar",new object[]{value})),null,false));

And what was the outcome of the above code?

lbluser
User
Posts: 14
Joined: 18-Apr-2008
# Posted on: 10-Jun-2008 08:14:11   

Hi Please see the following example to understand what i want.

select '@CustomParam' as MyPram , TeamID from Team where '@CustomParam' is null or '@CustomParam' = ''

First i want to add '@CustomParam' in the select. Then i want to add a predicate on '@CustomParam' which will check whether '@CustomParam' is empty/null or not.

Thanks

lbluser

Walaa avatar
Walaa
Support Team
Posts: 14643
Joined: 21-Aug-2005
# Posted on: 10-Jun-2008 09:08:17   

You should be using a dynamic list where you can define your field as such

             ResultsetFields fields = new ResultsetFields(n);
             fields.DefineField(new EntityField2("Name",.....);

And then use that define resultsetField in the null predicate.

lbluser
User
Posts: 14
Joined: 18-Apr-2008
# Posted on: 10-Jun-2008 11:00:14   

Hi I tried the method, you have mentioned in the last post. But i am getting an error

Must declare the scalar variable "@LO14a40b4a3".

Code

ResultsetFields fields = new ResultsetFields(2); fields.DefineField(new EntityField2("Name", new DbFunctionCall("ISNULL",new object[]{name,name})),0); fields[1] = TeamFields.TeamId; RelationPredicateBucket filter = new RelationPredicateBucket(); filter.PredicateExpression.Add((EntityField2)fields[0] == DBNull.Value); using (DataAccessAdapter adapter = new DataAccessAdapter()) {

            IDataReader reader = adapter.FetchDataReader(fields, filter, CommandBehavior.CloseConnection, 0, true);
            while (reader.Read())
            {
                string str = reader.GetValue(0).ToString();
            }

The query generated by LLBLGEN Query: SELECT ISNULL(@LO14a40b4a1, @LO14a40b4a2) AS [Name], [CBAdmin].[dbo].[Team].[TeamId] WHERE ( ( ISNULL(@LO14a40b4a3, @LO14a40b4a4) IS NULL))

Parameter: @LO14a40b4a1 : String. Length: 9. Precision: 0. Scale: 0. Direction: Input. Value: "test name".

Parameter: @LO14a40b4a2 : String. Length: 9. Precision: 0. Scale: 0. Direction: Input. Value: "test name".

is there any need of using DBFunction for the custom EntityField2 ? Please suggest.

Thanks lbluser

goose avatar
goose
User
Posts: 392
Joined: 06-Aug-2007
# Posted on: 10-Jun-2008 18:22:33   

where is the Name field actually defined? and the from clause?