I'm trying to get this sql statement to work in llbl gen 2.0. It works in sql server 2000.

CASE WHEN CustomerNameAlias = '' THEN CustomerName ELSE CustomerNameAlias END AS NameAlias

This is what I've tried in llbl gen-

ResultsetFields fields = new ResultsetFields(1); fields.DefineField(new EntityField("SortLink",( if(CustomersFieldIndex.CustomerNameAlias == string.Empty) { CustomersFieldIndex.CustomerName } else { CustomersFieldIndex.CustomerNameAlias } )),0);

Have tried other variations, no luck. Basically I'm asking how to do if..else in Fields Expressions. Have only found basic field expression examples in llbl gen help.

I'm assuming you are using an entity or an entity collection: You may manually add a custom property to your entity class which might be called "NameAlias" And then you may make the if else check in its "get" section

public virtual System.String NameAlias
{
    get
    {
        if(CustomerNameAlias  == '')
        {
            return CustomerName; 
        }
        return CustomerNameAlias;
    }

}

You can also go for a database view, and then map this view to an entity or a TypedView

Adding a custom property to an entity class wouldn't work since the columns I"m switching between span two table. I only showed one table in my example for simplicity.

I need to sort on this column and it would make things easier if I could get it to work in a field expression. I take it Field Expressions don't support if.. else?

I ended up adding a sql server function to perform the if.. else.

The function, named IsEmpty(), accepts the two field names and checks for an empty or null value. The fuction works with the sql statement from within sql server.

When I use it in a fields.. ExpressionToApply it surrounds the function parameters(field names being sent) with single quotes when viewing the sql execution in profiler.

Here is how I'm calling the function fields[2].ExpressionToApply = new DbFunctionCall("LearnAbout", "IsEmpty", new object[] { CategoryRelationFieldIndex.CategoryNameAlias, CategoryFieldIndex.CategoryName });

Is there a way to prevent the function parameters from being surrounded with single quotes?

Please try the following:

fields[2].ExpressionToApply = new DbFunctionCall("LearnAbout", "IsEmpty", new object[] { CategoryRelationFieldsCategoryNameAlias, CategoryFields.CategoryName });

That worked

Thanks for all the help.