Hi All,

I am having a problem using a DBFunctionCall to insert a CASE Statement into the generated SQL when using adapter.FetchEntityCollection()

My EntityCollection that i am returning is a collection of CaseEntity, structure is as follows

CaseEntity has a property of Type ClientEntity ClientEntity has a property called Anonymous, a property called FirstName and a Property called LastName.

The SQL Field list that i am trying to achieve is

SELECT CASE Anonymous WHEN 1 THEN 'ANON' ELSE FirstName END, 
   CASE Anonymous WHEN 1 THEN 'ANON' ELSE LastName END, ....

The select does have quite a few more fields from multiple tables using the relationships built up in a RelationPredicateBucket and a PrefetchPath.

I am having a problem with the following code

IPredicateExpression pe = new PredicateExpression();

pe.Add( 
    ClientFields.Anonymous.SetExpression(
            new DbFunctionCall(
            "CASE ( {0} ) WHEN 1 THEN {1} ELSE {2} END",
            new object[] { ClientFields.Anonymous, "ANON", ClientFields.FirstName }
        )
    )
);

This complains about not being able to Convert IEntityField2 to IPredicate, this is fair enough as the above code was ripped from a really early post on here and there is no overload that takes IEntityField as a parameter.

The problem i am having is i can't find what object to add the DBFunctionCall to for this to work! i know that using FetchTypedList will accept this as a parameter but I am using FetchEntityCollection.

Any help anyone can give is greatly appreciated

TIA Wes

I'm not sure it's completely clear from your post what you are trying to accomplish.

Do you want your entity to actually return a value other than what is in the database? So when you access myCase.Client.FirstName, the value will be "ANON" if it's anonymous, and the actual first name if it's not?

Or are you trying to filter out case entities that have anonymous clients? I ask that because your code uses a predicate expression, which is used for filtering.

Either way, I don't think you need a DBFunctionCall or CASE statement. Could you post a little more about what you are trying to do?

Elaborating a bit more on what psandler said:

1. You CAN'T use these kind of expression in a SELECT part of a Fetch when the container is an EntityCollection.

2. You CAN use these kind of expression if you are filtering (WHERE part), in EntityCollection. That's what .Net is complaining: you are using it in a predicate expression, but it's just an expression, you are not comparing it.

3. You CAN use these kind of expression (CASE... etc) in DynamicLists, in SELECT part.