I've been trying to write a general purpose Case Expression class with little success and I'm hoping for some help.

Here is a snippet from an existing view:

  CASE
    WHEN sc.team_cd = 'EXTERNAL' THEN 'External'
    WHEN sc.bp_owned_ind = 1 THEN 'Owned'
    ELSE 'Controlled'
  END AS ship_status

and here is the code I want to use to get the result:

CaseItem[] caseItems = new CaseItem[]
{
  new CaseItem(ShipControlFields.TeamCode == "EXTERNAL", "'External'"),
  new CaseItem(ShipControlFields.BPOwnedFlag == 1, "'Owned'"),
};

IEntityField2 shipStatusField = new EntityField2("ShipControlStatus", new CaseExpression("'Controlled'", caseItems));

This is the code/constructors for the classes involved...

    public class CaseItem
    {
        IPredicate condition;
        object result;

        public CaseItem(IPredicate condition, object result)
        {
            this.condition = condition;
            this.result = result;
        }

        public IPredicate Condition { get { return condition; } }
        public object Result { get { return result; } }
    }

    public class CaseExpression: IExpression
    {
        public CaseExpression(params CaseItem[] caseItems): this(null, caseItems) {}
        public CaseExpression(object defaultValue, params CaseItem[] caseItems)
        {}
    }

I've sort of got to the point where ToQueryText is called but I can't work out how to get the IPredicate for each CaseItem to produce text for itself. It blows up with a NullReferenceException.

I think my question is how do I get a text fragment from an IPredicate and what pre-requisites do I need to configure first?

Maybe the problem is a missing FieldPersistenceInfo somewhere? I can see an InsertPersistenceInfoObject(IPredicateExpression) in DataAccessAdapterBase but I can't see a way of getting it called. Is it the case that custom DataAccessAdapter code has to be written because this doesn't fall into one of the 'well-known' categories?

Cheers Simon

CASE support is added in v.2.5, using constants for DBFunctionCall parameters.

The following is copied from the manual "Using the generated code -> Calling a database function":

CASE support Although it's directly a function, we could use the function call with constant feature to specify CASE fragments for a select. Below is an example how to do that.

// C# ResultsetFields fields = new ResultsetFields(2); fields.DefineField(SupplierFields.Id, 0); fields.DefineField(SupplierFields.Region, 1);

fields[1].ExpressionToApply = new DbFunctionCall( "CASE {0} WHEN 'MA' THEN 'Massachusetts' WHEN 'OR' THEN 'Oregon' ELSE 'Unknown' END", new object[] { SupplierFields.Region });

Walaa wrote:

CASE support is added in v.2.5, using constants for DBFunctionCall parameters.

The following is copied from the manual "Using the generated code -> Calling a database function":

CASE support Although it's directly a function, we could use the function call with constant feature to specify CASE fragments for a select. Below is an example how to do that.

// C# ResultsetFields fields = new ResultsetFields(2); fields.DefineField(SupplierFields.Id, 0); fields.DefineField(SupplierFields.Region, 1);

fields[1].ExpressionToApply = new DbFunctionCall( "CASE {0} WHEN 'MA' THEN 'Massachusetts' WHEN 'OR' THEN 'Oregon' ELSE 'Unknown' END", new object[] { SupplierFields.Region });

Hi Walaa

That what I've been doing previously but I wanted to teach myself how to write a custom expression/predicate so I've a better idea of how the internals work and it looks like I've failed miserably.

Is there an answer to the question of how to get an IPredicate 'injected' with the necessary information at the correct time? If so, I'll finish off my CaseExpression class, if not, I'll add a couple of methods to my LLCoolJHelper class.

Cheers Simon

I've sort of got to the point where ToQueryText is called but I can't work out how to get the IPredicate for each CaseItem to produce text for itself. It blows up with a NullReferenceException.

I think I'm missing something here, what's IPredicate got to do here?

As far as I know, if you want to have your own CASE implementation, you should implement IExpression and override the ToQueryText to output the needed text.

Expressions are used to replace EntityFields, in which ever context.

Check this old thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3480

Walaa wrote:

I've sort of got to the point where ToQueryText is called but I can't work out how to get the IPredicate for each CaseItem to produce text for itself. It blows up with a NullReferenceException.

I think I'm missing something here, what's IPredicate got to do here?

As far as I know, if you want to have your own CASE implementation, you should implement IExpression and override the ToQueryText to output the needed text.

Expressions are used to replace EntityFields, in which ever context.

Check this old thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3480

Well it may be my misunderstanding of terms but I thought an IPredicate was an expression that results in a boolean value and can be used in a WHERE clause or the like.

So I wanted to write an IExpression that would accept pairs of IPredicates/Values and build a CASE statement. (There are two types of CASE statements in SqlServer and this is the one that doesn't have a field value after the CASE).

The problem I am having is that the IPredicates need to be 'injected' with PersistenceInfo somehow (otherwise it crashes with a NullException) and DataAccessAdapterBase's InsertPersistenceInfoObjects method overloads don't deal with IPredicates (except as part of other constructs). There is an overload that deals with IPredicateExpressions and I can wrap each IPredicate in its own IPredicateExpression but that overload only seems to be called from the other overloads!

ExpressionElementType has Expression; Field; FunctionCall; ScalarQuery; Value as its allowable types and my IPredicate(Expression) is none of those.

I did sort of manage to get it working by implementing IDBFunction (ala DbFunctionCall) and using FunctionCall as the ExpressionElementType but the code in InsertPersistenceInfoObjects(IDbFunctionCall functionCall) only deals with IExpressions and IEntityField2. I overrode this method in DataAccessAdapter and added this code:

else
{
    IPredicate parameterAsPredicate = functionCall.FunctionParameters[i] as IPredicate;
    if (parameterAsPredicate != null)
    {
        InsertPersistenceInfoObjects(new PredicateExpression(parameterAsPredicate));
    }
}

and it behold! it works! But this seems a long-winded way of achieving this and so I was wondering whether I was missing something.

Cheers Simon

simmotech wrote:

Walaa wrote:

I've sort of got to the point where ToQueryText is called but I can't work out how to get the IPredicate for each CaseItem to produce text for itself. It blows up with a NullReferenceException.

I think I'm missing something here, what's IPredicate got to do here?

As far as I know, if you want to have your own CASE implementation, you should implement IExpression and override the ToQueryText to output the needed text.

Expressions are used to replace EntityFields, in which ever context.

Check this old thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3480

Well it may be my misunderstanding of terms but I thought an IPredicate was an expression that results in a boolean value and can be used in a WHERE clause or the like.

So I wanted to write an IExpression that would accept pairs of IPredicates/Values and build a CASE statement. (There are two types of CASE statements in SqlServer and this is the one that doesn't have a field value after the CASE).

Ah, I see. IPredicate result in SQL predicates for a WHERE clause, they're not usable for CASE statements per se, as you still have to provide the THEN part.

The problem I am having is that the IPredicates need to be 'injected' with PersistenceInfo somehow (otherwise it crashes with a NullException) and DataAccessAdapterBase's InsertPersistenceInfoObjects method overloads don't deal with IPredicates (except as part of other constructs). There is an overload that deals with IPredicateExpressions and I can wrap each IPredicate in its own IPredicateExpression but that overload only seems to be called from the other overloads!

ExpressionElementType has Expression; Field; FunctionCall; ScalarQuery; Value as its allowable types and my IPredicate(Expression) is none of those.

I did sort of manage to get it working by implementing IDBFunction (ala DbFunctionCall) and using FunctionCall as the ExpressionElementType but the code in InsertPersistenceInfoObjects(IDbFunctionCall functionCall) only deals with IExpressions and IEntityField2. I overrode this method in DataAccessAdapter and added this code:

else
{
    IPredicate parameterAsPredicate = functionCall.FunctionParameters[i] as IPredicate;
    if (parameterAsPredicate != null)
    {
        InsertPersistenceInfoObjects(new PredicateExpression(parameterAsPredicate));
    }
}

and it behold! it works! But this seems a long-winded way of achieving this and so I was wondering whether I was missing something.

Cheers Simon

No, you're not missing something. It expects IPredicate at given places, so it doesn't expect predicates as db function call parameters.

It's an interesting approach. I can add the few lines to DataAccessAdapterBase.InsertPersistenceInfoObjects to deal with IPredicate parameters in a DBFunctioncall. (though not soon).

The main reason IPredicate elements aren't considered there is that in general booleans aren't really a type in DB systems. So passing a boolean to a function is often not possible, you've to pass 1 or 0 instead, while a predicate results in a boolean.

This for example doesn't work in SqlServer (and most other db's I think) SELECT (field>10) As BigValue FROM Table

Otis wrote:

No, you're not missing something. It expects IPredicate at given places, so it doesn't expect predicates as db function call parameters.

It's an interesting approach. I can add the few lines to DataAccessAdapterBase.InsertPersistenceInfoObjects to deal with IPredicate parameters in a DBFunctioncall. (though not soon).

The main reason IPredicate elements aren't considered there is that in general booleans aren't really a type in DB systems. So passing a boolean to a function is often not possible, you've to pass 1 or 0 instead, while a predicate results in a boolean.

This for example doesn't work in SqlServer (and most other db's I think) SELECT (field>10) As BigValue FROM Table

No biggie but I think it would be useful in a future version. Although I can't think of any other uses for a predicate other than WHERE and CASE off the top of my head, I'm sure other uses will come up.

Cheers Simon